Skip to main content

Conditional Formatting in Spreadsheet Documents

  • 5 minutes to read

Conditional formatting is used to highlight important information and make data interpretation easier. Conditional formats such as cell background color, border line style or font color can be applied to cells whose values meet a certain condition specified by a conditional formatting rule.

Warning

Transparency is not supported in conditional formatting.

All conditional formatting rules specified in a worksheet are stored in the ConditionalFormattingCollection collection, accessible from the Worksheet.ConditionalFormattings property. The table below lists the available types of conditional formatting rules.

To create this rule

Do the following

Example

AverageConditionalFormatting - formats cells whose values are above or below the average or standard deviation in a range of cells.

AverageConditionalFormattingExample2

This image shows price data with conditional formatting, highlighting values that are above the average in the range of cells.

Call the ConditionalFormattingCollection.AddAverageConditionalFormatting method. To specify a format applied if the condition is true, use the ISupportsFormatting.Formatting property of the AverageConditionalFormatting object.

How to: Format Cell Values that are Above or Below the Average

ColorScale2ConditionalFormatting - formats cells by using a two-color scale.

ColorScale2ConditionalFormattingExample2

This image shows price distribution using a gradation of two colors. Blue represents the lower values and yellow represents the higher values.

Use the ConditionalFormattingCollection.AddColorScale2ConditionalFormatting method, which also specifies conditional formatting options such as color for the maximum and minimum thresholds of a range.

How to: Format Cells Using a Two-Color Scale

ColorScale3ConditionalFormatting - formats cells by using a three-color scale.

ColorScale3ConditionalFormatting2

This image shows price distribution using a gradation of three colors. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.

Use the ConditionalFormattingCollection.AddColorScale3ConditionalFormatting method, which also specifies conditional formatting options such as color for the minimum, midpoint and maximum thresholds of a range.

How to: Format Cells Using a Three-Color Scale

DataBarConditionalFormatting - formats cells by using data bars.

DataBarConditionalFormattingExample2

This image shows markup magnitude using data bars.

Use the ConditionalFormattingCollection.AddDataBarConditionalFormatting method, which also specifies the data bars to be displayed.

How to: Format Cells Using Data Bars

IconSetConditionalFormatting - formats cells by using icon sets.

IconSetConditionalFormattingExample2

This image shows upward and downward cost trends.

Use the ConditionalFormattingCollection.AddIconSetConditionalFormatting method, which also specifies an icon set to be displayed.

How to: Format Cells Using Icon Sets

ExpressionConditionalFormatting - formats cells whose values meet the criterion represented by a relational operator (=, ≠, <, >, ≤, ≥).

ExpressionConditonalFormattingExample2

This image shows cost data with conditional formatting, highlighting values that are greater than $18.

Call the ConditionalFormattingCollection.AddExpressionConditionalFormatting method. To specify a format applied if the condition is true, use the ISupportsFormatting.Formatting property of the ExpressionConditionalFormatting object.

How to: Format Cells that are Less than, Greater than or Equal to a Value

FormulaExpressionConditionalFormatting - uses a formula to determine which cells to format.

FormulaExpressionConditionalFormatting2

This image shows a data table with conditional formatting that uses the MOD(ROW(),2)=1 formula to shade alternate rows in light blue.

Call the ConditionalFormattingCollection.AddFormulaExpressionConditionalFormatting method. To specify a format applied if the condition is true, use the ISupportsFormatting.Formatting property of the FormulaExpressionConditionalFormatting object.

How to: Use a Formula to Determine which Cells to Format

RangeConditionalFormatting - formats cells that are between or not between two specified values.

RangeConditionalFormattingExample2

This image shows price data with conditional formatting, highlighting values that are less than $10 and greater than $19.

Call the ConditionalFormattingCollection.AddRangeConditionalFormatting method. To specify a format applied if the condition is true, use the ISupportsFormatting.Formatting property of the RangeConditionalFormatting object.

How to: Format Cells that are Between or Not Between Two Values

RankConditionalFormatting - formats top/bottom ranked values.

RankConditionalFormattingExample2

This image shows price data with conditional formatting, highlighting the top three values.

Call the ConditionalFormattingCollection.AddRankConditionalFormatting method. To specify a format applied if the condition is true, use the ISupportsFormatting.Formatting property of the RankConditionalFormatting object.

How to: Format Top or Bottom Ranked Values

TextConditionalFormatting - formats cells that contain the given text.

TextConditionalFormattingExample2

This image shows a list of authors in which the name Ray Bradbury is highlighted.

Call the ConditionalFormattingCollection.AddTextConditionalFormatting method. To specify a format applied if the condition is true, use the ISupportsFormatting.Formatting property of the TextConditionalFormatting object.

How to: Format Cells based on the Text in the Cell

SpecialConditionalFormatting - formats empty cells, unique or duplicate values, formula errors, etc.

SpecialConditionalFormattingExample2

This image shows a list of authors in which the unique values are highlighted.

Call the ConditionalFormattingCollection.AddSpecialConditionalFormatting method. To specify a format applied if the condition is true, use the ISupportsFormatting.Formatting property of the SpecialConditionalFormatting object.

How to: Format Unique or Duplicate Values, Blank Cells and Formula Errors

TimePeriodConditionalFormatting - formats cells containing dates that fall within a specified time period.

TimePeriodConditionalFormattingExample2

This image shows a table with conditional formatting that highlights today’s date.

Call the ConditionalFormattingCollection.AddTimePeriodConditionalFormatting method. To specify a format applied if the condition is true, use the ISupportsFormatting.Formatting property of the TimePeriodConditionalFormatting object.

How to: Format Cells that Contain Dates

All of these objects inherit from the ConditionalFormatting base interface, which provides basic properties to specify a conditional formatting rule:

To remove the conditional formatting rule from the collection, use the ConditionalFormattingCollection.Remove or ConditionalFormattingCollection.RemoveAt method. To remove all conditional formatting rules from a worksheet, use the ConditionalFormattingCollection.Clear method.