Conditional Formatting
The conditional formatting in spreadsheet documents helps you visualize the data inside in a user-friendly manner, making it easy to analyze data, find critical issues, patterns and trends.
A conditional format changes the appearance of cells based on conditions that you specify. If the conditions are true, the cell range is formatted; if the conditions are false, the cell range is not formatted. There are many built-in conditions, and you can also create your own (including by using a formula that evaluates to True or False).
This article describes the supported formatting rules, how to create and how to use them.
Rules
SpreadProcessing supports many different types of formatting rules. Each rule implements the base ConditionalFormattingRule
class. All of them are listed in Table 1.
Table 1: Supported formatting rules
Rule | Description |
---|---|
ColorScaleRule | Specificies a gradient range of colors that is used to give additional meaning to data by assigning certain values to colors in the gradient spectrum. |
DataBarRule | A graphical representation of the cells’ content. |
IconSetRule | Specifies a collection of icons that can be used to comment and classify data into categories. |
BetweenRule | This conditional formatting rule determines whether a cell value is inside a specified range. |
NotBetweenRule | This conditional formatting rule determines whether a cell value is outside a specified range. |
EqualToRule | Determines whether a cell value matches a specified value. |
NotEqualToRule | Determines whether a cell value doesn't match a specified value. |
GreaterThanOrEqualToRule | Determines whether a cell value matches specified value or it is greater. |
GreaterThanRule | Determines whether a cell value is greater than the specified value. |
LessThanOrEqualToRule | Determines whether a cell value matches specified value or it is smaller. |
LessThanRule | Determines whether a cell value is smaller than the specified value. |
HasErrorRule | Matches cell values that contain errors. |
HasNoErrorRule | Matches cell values that do not contain errors. |
BeginsWithRule | Matches cells whose values begin with a specified string. |
EndsWith | Matches cells whose values end with a specified string. |
ContainsBlanksRule | Matches blank cells. |
NotContainsBlanksRule | Matches non-empty cells. |
ContainsRule | This conditional formatting rule highlights cells containing given text. |
NotContainsRule | This conditional formatting rule highlights cells that do not contain given text. |
TopRule | This conditional formatting rule highlights cells whose values fall in the top N bracket. It can also work for N % instead of N items. It has two Boolean configuration properties: Items and Percent, which act like switches for these behaviors. |
BottomRule | This conditional formatting rule highlights cells whose values fall in the bottom N bracket. It can also work for N % instead of N items. It has two Boolean configuration properties: Items and Percent, which act like switches for these behaviors. |
DuplicateValuesRule | Matches values that are duplicate inside a specified range. |
UniqueValuesRule | Matches values that are unique inside a specified range. |
ValueDistributionRule | This conditional formatting rule highlights cells that are above or below the average for all values in the range. It has four Boolean configuration properties – AboveAverage , AboveOrEqualToAverage , BelowAverage , BelowOrEqualToAverage . |
FormulaRule | This conditional formatting rule allows you to enter any valid formula as a rule argument that returns a number. |
Create Formatting for a Rule
The ConditionalFormattingDxfRule class is a base class for all rules that support formatting. All of its inheritors, expose the Formatting
property. This property is of type DifferentialFormatting
and holds the formatting that will be applied to all cells inside the range that fit the rule requirement. You can set the following properties:
- Font size
- Font family
- Fore color
- Bold
- Italic
- Underline
- Fill
- LeftBorder
- RightBorder
- TopBorder
- BottomBorder
- DiagonalUpBorder
- DiagonalDownBorder
- CellValueFormat: Allows you to set the number format string for the cell value. For more information, check the Number Formatting topic.
The Formatting property can be used for all rules listed in Table 1 except ColorScaleRule, IconSetRule, and DataBarRule. Due to their specificity, these three rules inherit directly from ConditionalFormattingRule
and do not expose the Formatting
property. Their styling options are directly inside the rule class.
Example 1: Create formatting
Create and Apply Conditional Formatting Rule
Each of the classes listed in Table 1 above expose constructors enabling you to instantiate the specific rule. The constructors of these classes take a string
parameter allowing you to specify the values and conditions the rule must work with. You can pass any cell value for the parameter, inlcuding formulas.
Example 2: Create Between rule
Between rule applied on a range of values
Example 3: Create GreaterThanOrEqualTo rule
GreaterThanOrEqualTo rule applied on a range of values
Example 4: Create ColorScale rule
Depending on the exact number of colors you would like to apply for the ColorScaleRule, you can choose between TwoColorScaleValueContext and ThreeColorScaleValueContext classes.
ColorScale rule applied on a range of values
Example 5: Create DataBar rule
DataBar rule applied on a range of values
Example 6: Create IconSet rule
IconSet rule applied on a range of values
The PresetIconSet
enum exposes multiple definitions for predefined sets of icons. You can also use custom icons through the other construcor of IconSetRule. This overload accepts object of type IconSetValueContextBase whose implementations allow you to register three, four or five icons: ThreeIconSetValueContext
, FourIconSetValueContext
, FiveIconSetValueContext
.
Working With IRangeValue
Some of the rules enable you to set values for their ranges. Examples for similar rules are DataBar and ColorScale. Their contexts accept IRangeValue
objects that define the type for the minimum, middle (if present) and maximum values. These values could be numbers, percentages, or automatically calculated values for the specific range.
The following list shows all implementations of IRangeValue
that you can use:
- AutomaticMaximumValue: A value that is automatically determined depending on the current context.
- AutomaticMinimumValue: A value that is automatically determined depending on the current context.
- MaximumValue: The highest value in the applied range.
- MinimumValue: The lowest value in the applied range.
- NumericValue: A simple numeric value.
- FormulaValue: A formula whose result is used as a range value.
- PercentValue: A percentage numeric value.
- PercentileValue: A numeric value that takes values up to a certain percentile of the range.
Get the Conditional Formatting from a CellRange
Any previously applied formatting can be obtained through the GetConditionalFormattings method of CellSelection. This method returns a collection of ConditionalFormattingRange object representing the formattings applied to the selection and the CellRange each formatting is applied on.
Example 7: Get the conditional formatting
Remove Conditional Formatting
Through the CellSelection, you can also remove the formatting from the selected cells.
Example 8: Remove the conditional formatting
Resolve Conditional Formatting
Each of the formatting rule classes gives you the ability to evaluate the rule and obtain its result through the Resolve
method.
For the rules that apply on all the values in the range, the return value is between 0 and 1, depending on where that value is positioned in the range of all values. Such rules are ColorScaleRule
, DataBarRule
and IconSetRule
. For all other rules, the result of Resolve
is 0 or 1, depending on whether the specific cell value meets the rule requirements.
Example 9: Resolve conditional formatting rule
Update the Rule for a Formatting
In case you would like to change the rule used by a ConditionalFormatting object, you can do so using the UpdateRule() method.
Example 10: Change the rule for existing conditional formatting
Update the Cell Range of Existing Formatting
The CellSelection class exposes the UpdateConditionalFormattingCellRanges method to help you change the conditional formatting element's cell range, applying it to the currently selected ranges. When invoked, the UpdateConditionalFormattingCellRanges method removes the conditional formatting from the ranges it is associated with and applies it to the selection.
Control the Priority of Rules
Each ConditionalFormattingRule has a specific priority used to evaluate which formatting should be applied when several rules are used on the same range of cells. If you would like to change that priority, you can use the SwapPriority() method of the rule. It takes a ConditionalFormattingRule object and swaps its priority with the rule the method is invoked for.