[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

cfRule (Conditional Formatting Rule)

This collection represents a description of a conditional formatting rule.

[Example:

This example shows a conditional formatting rule highlighting cells whose values are greater than 0.5. Note that in this case the content of <formula> is a static value, but can also be a formula expression.

<conditionalFormatting sqref="E3:E9">
<cfRule type="cellIs" dxfId="0" priority="1" operator="greaterThan">
<formula>0.5</formula>
</cfRule>
</conditionalFormatting>

end example]

Only rules with a type value of expression support formula syntax.

Parent Elements

conditionalFormatting3.3.1.17)

 

Child Elements

Subclause

colorScale (Color Scale)

§3.3.1.15

dataBar (Data Bar)

§3.3.1.26

extLst (Future Feature Data Storage Area)

§3.2.10

formula (Formula)

§3.3.1.40

iconSet (Icon Set)

§3.3.1.46

 

Attributes

Description

aboveAverage (Above Or Below Average)

Indicates whether the rule is an "above average" rule. '1' indicates 'above average'. Valid only for type = aboveAverage.

 

The possible values for this attribute are defined by the XML Schema boolean datatype.

bottom (Bottom N)

Indicates whether a "top/bottom n" rule is a "bottom n" rule. '1' indicates 'bottom'. Valid only for type = top10.

 

The possible values for this attribute are defined by the XML Schema boolean datatype.

dxfId (Differential Formatting Id)

This is an index to a dxf element in the Styles Part indicating which cell formatting to apply when the conditional formatting rule criteria is met.

 

The possible values for this attribute are defined by the ST_DxfId simple type (§3.18.26).

equalAverage (Equal Average)

Flag indicating whether the 'aboveAverage' and 'belowAverage' criteria is inclusive of the average itself, or exclusive of that value. '1' indicates to include the average value in the criteria. Valid only for type = aboveAverage.

 

The possible values for this attribute are defined by the XML Schema boolean datatype.

operator (Operator)

The operator in a "cell value is" conditional formatting rule. Valid only when type = cellIs

 

The possible values for this attribute are defined by the ST_ConditionalFormattingOperator simple type (§3.18.16).

percent (Top 10 Percent)

Indicates whether a "top/bottom n" rule is a "top/bottom n percent" rule. Valid only for type = top10.

 

The possible values for this attribute are defined by the XML Schema boolean datatype.

priority (Priority)

The priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where '1' is the highest priority.

 

 

 

The possible values for this attribute are defined by the XML Schema int datatype.

rank (Rank)

The value of "n" in a "top/bottom n" conditional formatting rule. Valid only for type = top10.

 

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

stdDev (StdDev)

The number of standard deviations to include above or below the average in the conditional formatting rule. Valid only for type = aboveAverage. If a value is present for stdDev and the rule type = aboveAverage, then this rule is automatically an "above or below N standard deviations" rule.

 

The possible values for this attribute are defined by the XML Schema int datatype.

stopIfTrue (Stop If True)

If this flag is '1', no rules with lower priority may be applied over this rule, when this rule evaluates to true.

 

The possible values for this attribute are defined by the XML Schema boolean datatype.

text (Text)

The text value in a "text contains" conditional formatting rule. Valid only for type = containsText.

 

The possible values for this attribute are defined by the XML Schema string datatype.

timePeriod (Time Period)

The applicable time period in a "date occurring…" conditional formatting rule. Valid only for type = timePeriod.

 

The possible values for this attribute are defined by the ST_TimePeriod simple type (§3.18.82).

type (Type)

Type of conditional formatting rule.

 

The possible values for this attribute are defined by the ST_CfType simple type (§3.18.13).

The following XML Schema fragment defines the contents of this element:

<complexType name="CT_CfRule">

   <sequence>

   <element name="formula" type="ST_Formula" minOccurs="0" maxOccurs="3"/>

   <element name="colorScale" type="CT_ColorScale" minOccurs="0" maxOccurs="1"/>

   <element name="dataBar" type="CT_DataBar" minOccurs="0" maxOccurs="1"/>

   <element name="iconSet" type="CT_IconSet" minOccurs="0" maxOccurs="1"/>

   <element name="extLst" minOccurs="0" type="CT_ExtensionList"/>

   </sequence>

   <attribute name="type" type="ST_CfType"/>

   <attribute name="dxfId" type="ST_DxfId" use="optional"/>

   <attribute name="priority" type="xsd:int" use="required"/>

   <attribute name="stopIfTrue" type="xsd:boolean" use="optional" default="false"/>

   <attribute name="aboveAverage" type="xsd:boolean" use="optional" default="true"/>

   <attribute name="percent" type="xsd:boolean" use="optional" default="false"/>

   <attribute name="bottom" type="xsd:boolean" use="optional" default="false"/>

   <attribute name="operator" type="ST_ConditionalFormattingOperator" use="optional"/>

   <attribute name="text" type="xsd:string" use="optional"/>

   <attribute name="timePeriod" type="ST_TimePeriod" use="optional"/>

   <attribute name="rank" type="xsd:unsignedInt" use="optional"/>

   <attribute name="stdDev" type="xsd:int" use="optional"/>

   <attribute name="equalAverage" type="xsd:boolean" use="optional" default="false"/>

</complexType>