Conditional formatting allows you to visualize and analyze your data by highlighting relevant cells. It functions the same as other commonly used spreadsheet programs.
To use conditional formatting in the Report Designer, open the spreadsheet you want to apply formatting and go to Design.
The following options are available:
- New Rule - allows you to define a new conditional rule
- Clear Rules - allows you to clear all the rules from the entire sheet or only from the selected cells
- Manage Rules - allows you to make changes to the rules already defined
Note that selecting the font size of a cell with a conditional formatting rule is not possible.
Using conditional formatting with DynaRanges
If you define a conditional formatting rule on a cell inside a DynaRange, the rule is also adapted when the DynaRange expands. The rule is handled based on the Applies to range defined for the rule. If the Applies to range covers all rows of a vertically defined DynaRange or all the columns of a horizontally defined DynaRange, then only the Applies to range is modified. The rule formula itself, including the cell references in it, is not changed.
If the rule uses cell references that should adapt automatically to the expanding DynaRange, then the references must be defined as fully or partially relative.
The example below shows a rule defined with the Applies to range =$D$5:$E$5. The rule is triggered if the left-most cell of the DynaRange contains the value "2014":=$B2="2014".
If the cell reference were a fully absolute, such as $C$5, all the cells in the DR would have checked the value of C5 and thus, the conditional formatting would not have been triggered on any cell.
Notes:
-
The relativeness of the cell reference is relevant in this scenario regardless of whether the referenced cell is located inside or outside the DynaRange.
-
When you use a custom number format in conditional formatting, the Alias in a DynaRange is not shown.
Cloning Conditional Formatting Rules
If the conditional formatting rule inside of the DynaRange does not cover all rows and columns, the Applies to modification is not applied. Instead, the rule is cloned for each copied instance of the first row. The following example, the DynaRange covers two rows:
If the DynaRange is activated, copies of the conditional formatting rule are created and the cell references inside the rule are automatically adjusted, similar to references in cells.
During the cloning, the cell references are adjusted regardless of whether they are fully or partially relative. In this scenario, however, it is still recommended to define relativeness in a proper way. If the cell references pointed to a cell outside the DynaRange, it would not have been adjusted during the cloning process, regardless of whether they are relative or absolute. This behavior is similar to references in cells.
Updated August 29, 2024