<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>

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

Parent Elements

<conditionalFormatting> (§3.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>