Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Collapse]Data Presentation
     Sorting
     Filtering
     Conditional Formatting
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Conditional Formatting

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.

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.

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.

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.

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.

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.

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 (=, ≠, <, >, ≤, ≥).

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.

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.

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.

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.

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.

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.

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.

How would you rate this topic?​​​​​​​