Conditional Formatting Rules
- 4 minutes to read
The conditional formatting functionality is available in Spreadsheet, Data Grid, Tree List, and Vertical Grid controls. You can create ten types of rules inspired by Microsoft Excel® to emphasize specific cell data and trends.
You can apply all conditional formatting rules to Table View cells in Spreadsheet controls. The same rules in Data Grid, Tree List, or Vertical Grid controls can affect only one contiguous cell range (for example, all cells in a column). In both cases, you can use a rule’s Areas property to adjust the affected cell ranges. If a cell meets multiple formatting criteria, the Index property specifies the rule that defines the cell’s formatting.
Style-Based Conditional Formatting Rules
All style-based rules apply spreadsheet-compatible style settings to the cells that meet specific formatting conditions.
- Above or Below Average
Applies custom formatting to all affected cells whose numeric values are above, below, or equal to the calculated average.
The rule’s ComparisonOperator property allows you to select a cell value evaluation mode. Certain modes take the specified standard deviation multiplier into account.
Use the sdxConditionalFormattingRuleEditDialogRuleNameAboveOrBelowAverage resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
- Cell Is
Applies custom formatting to all affected cells whose values meet one of the following conditions:
Values are within or beyond the range between results of two formula expressions assigned to the Expression and Expression2 properties.
Values are above, below, or equal to the result of a formula expression assigned to the Expression property.
You can apply custom formatting to cells whose non-numeric values are equal to the result of a formula expression. The rule’s ComparisonOperator property allows you to select a cell value evaluation mode. Certain modes take the specified standard deviation multiplier into account.
Use the sdxConditionalFormattingRuleEditDialogRuleNameCellIs resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
- Duplicate Values
Applies custom formatting to all affected cells that contain duplicate numeric or non-numeric values.
Note
This conditional formatting rule complements the Unique Values rule.
Use the sdxConditionalFormattingRuleEditDialogRuleNameDuplicateValues resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
- Expression
Applies custom formatting to all affected cells for which the specified formula expression returns True.
The rule’s Expression property specifies the cell formatting criteria.
Use the sdxConditionalFormattingRuleEditDialogRuleNameExpression resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
- Top/Bottom Values
Applies custom formatting to the specified absolute or relative number of affected cells that contain the highest or lowest numeric values.
The rule’s Direction property allows you to switch between Top and Bottom formatting modes.
Use the sdxConditionalFormattingRuleEditDialogRuleNameTopBottomValues resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
- Unique Values
Applies custom formatting to all affected cells that contain unique numeric or non-numeric values.
Note
This conditional formatting rule complements the Duplicate Values rule.
Use the sdxConditionalFormattingRuleEditDialogRuleNameUniqueValues resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
Threshold-Based Conditional Formatting Rules
Threshold-based rules use scales to differentiate all affected cells by their numeric values. The scales consist of simple formatting elements associated with multiple threshold values.
- Data Bar
Visualizes numeric values in the affected cells as horizontal data bars. Use the rule’s Style property to customize data bar appearance settings.
The range of displayed numeric values is limited by the lowest and highest values out-of-the-box. You can use the MinValue and MaxValue properties to adjust this range and prevent the display of data bars in cells whose values are outside the range.
Use the sdxConditionalFormattingRuleDialogRuleNameDataBar resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
- Icon Set
Draws icons within cells whose numeric values are greater than or equal to the specified thresholds. Use the rule’s PresetName property to select a predefined icon preset that associates icons with the thresholds. The threshold count is always equal to the number of icons in the selected preset.
Use the sdxConditionalFormattingRuleDialogRuleNameIconSet resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
- Three Color Scale
Fills backgrounds of affected cells with colors of a calculated gradient. The gradient has three key point colors that correspond to the lowest, intermediate, and highest threshold values.
Use the sdxConditionalFormattingRuleDialogRuleNameThreeColorScale resource string to localize the rule’s name in the Conditional Formatting Rule dialog.
- Two Color Scale
Fills backgrounds of affected cells with colors of a calculated gradient. The gradient has two key point colors that correspond to the lowest and highest threshold values.
Use the sdxConditionalFormattingRuleDialogRuleNameTwoColorScale resource string to localize the rule’s name in the Conditional Formatting Rule dialog.