Conditional formatting basics

CelesteCS Conditions is a Confluence add-on for visually highlighting cells that contain values, meeting conditions you specify, for example:

  • Highlight cells containing specific words
  • Highlight the values based on formulas
  • Replacing empty values with specific text

Highlighting by conditional formatting can be done with font style, font color, or cell background color.

You configure it once and it just works. All you need to do is glance at the results when you need it. A simple instruction of how to start with CelesteCS Conditions add-on may be found here.

Conditional rules

Conditions add-on has a set of simple rules that cover the most frequent criteria for conditional formatting. To apply these built-in rules, add Conditional Rule macro to Conditional Rules Manager macro body and select the rule you need from the list of Rule condition parameter values. These predefined conditional rules work well for simple conditions. For more information you may have a look at the Conditional Rule macro description here.

Specifying multiple conditions

Cells may have multiple conditional formatting rules applied. For this purpose add several Conditional Rule macros in Conditional Rules Manager macro body, so they will be processed in the order of their appearance there.

If the Stop if true parameter value of one of Conditional Rule macros is true, the consequent rules will not be processed for the cell if the condition of this Conditional Rule macro is met. It makes conditional rules processing performance higher, but if you need to apply different formatting rules at once, set this parameter value to false, which is by default.

Specifying cells to apply conditional rules to

You may specify the cells for applying conditional rules to, separately for each rule. Specify the Applies to parameter value appropriately for your purpose. This parameter value is ‘All tables’ by default, which means that formatting will be applied to all the tables inside Conditional Rules Manager macro. You may also specify table column or row range, like Table1.A:B.

Note, that the Conditional Rules Manager’s Skip heading cells parameter is applied to all the conditions. If it is true, the formatting will not be applied to any heading cell of this Conditional Rules Manager macro tables.

Specifying conditional formulas

When you need conditional formatting that has too complex conditions for simple conditional formats, you may create your own formulas.

Formula-based conditional formatting may depend on complex calculations based on cell values inside or outside the range, being formatted. These conditional formulas may contain AND, OR, and NOT logical conditions that are not available in simple conditional formatting rules.

To configure formula-based conditional formatting do the following:

  1. Perform the initial steps from the “How to start” article to add Conditional Rules Manager and Conditional Rule macros to your page
  2. Open Conditional Rule macro configuration dialog
  3. Set the Rule condition parameter value to “Formula result value is true”
  4. Specify the formula as the Value 1 parameter value, for example: “A1<B1” or “A1>$E$2”. The formula must result in TRUE for the formatting to be applied
  5. Click Save to apply macro configuration
  6. Save the page
  7. See the result

Note, that you must enter a formula that returns TRUE when the conditional format should be applied. For example, you might use a formula like this to test whether values in column C of the table are equal to the value in cell $C$7:

$C1=$C$7