Conditional formatting allows you to add visual indicators or markers to your tables which can enhance the tables' legibility. For example, you might want to display negative numbers in red. The conditional formatting options are found in the conditional formatting category of the calculation properties.
The condition determines when the conditional formatting will be applied. For example, if you wanted to highlight values between 1 and 100 in bold text, you would define the condition as between 1 and 100.
You can define as many separate conditions as you wish for a calculation. To add a new condition, at the top of the conditional formatting category, click Add:
To remove a condition, click it to select it, and click Delete:
.png?Policy=eyJTdGF0ZW1lbnQiOlt7IlJlc291cmNlIjoiaHR0cHM6Ly9kemY4dnF2MjRlcWhnLmNsb3VkZnJvbnQubmV0L3VzZXJmaWxlcy8xMTAwLzMzMjkvY2tmaW5kZXIvaW1hZ2VzL2ltcG9ydC10eDVra2Z6YTY4NnEvaW1hZ2VzL2ljbi1yZW1vdmUoNSkucG5nIiwiQ29uZGl0aW9uIjp7IkRhdGVMZXNzVGhhbiI6eyJBV1M6RXBvY2hUaW1lIjoxNzY3MjMzNzIwfX19XX0_&Signature=aku39M-BkeBQYbeeM0WETmjl1Fm79ytt2BgZiEXnQp-nxx~vsLvQ2qIk3z3r6L3eXNTsR7BMWirKYdyRJ~KJagAU1ZfmBVbB7SEpnqNza20AFV~Knd94orba0JA63aVbTMdvlfKpkadCg4mNFvmX4g5xYR0WRreMai1FRsjjoj-JQudI3DZsM~l7Yy7YuVgUMLKjxm~1TYKWBHtGL~G9TWJfi2qaQLEEG4-zQRoRto8Yl9hN824CuMuPPIuIzmOPGxnVT7ab-oWGqtSOTtrIXnhQ6hLFcoS2HXpRzkLdBjYBkISVh3WlP5mLd0KFiqPfe7J9YWkVBBC9qwuye8~iVQ__&Key-Pair-Id=K2TK3EG287XSFC)
You can change the order of the conditions by dragging and dropping them into the desired order. To drag a condition, click the drag handle:
To edit the details of a condition, click it to select it; its properties are shown immediately below. In the drop-down list, you can specify the basic condition type, as follows:
Out of: Use this operator to specify a range; the formatting will be applied if a value falls outside of this range. Enter the lower and upper limits of the range in the adjacent text boxes.
=: Use this operator to specify a single value you want to exactly match. The formatting will be applied if a value is exactly equal to this value. Enter the value in the adjacent text box.
: Use this operator when you want to apply formatting to all values that are not equal to a specific single value (e.g. apply formatting to numbers that are not 100). Enter the value in the adjacent text box.
: Use this operator when you want to apply formatting to all values that are below a specific single value (e.g. apply formatting to numbers that are below 50). Enter the value in the adjacent text box.
>: Use this operator when you want to apply formatting to all values that are above a specific single value (e.g. apply formatting to numbers that are above 25). Enter the value in the adjacent text box.
: Use this operator when you want to apply formatting to all values that are below or equal to a specific single value (e.g. apply formatting to numbers that are 50 or below). Enter the value in the adjacent text box.
>=: Use this operator when you want to apply formatting to all values that are equal to or above a specific single value (e.g. apply formatting to numbers that are 25 or above). Enter the value in the adjacent text box.
The extent of the condition determines which items will be formatted if the condition is true. To set the extent of a condition, select as follows (in the following table, qualifying cell refers to any cell for which the condition evaluates true):
This calculation: The qualifying cell will be formatted.
All calculations: All calculations in the table will be formatted.
Caption in row: The caption for the row containing the qualifying cell will be formatted.
Caption in column: The caption for the column containing the qualifying cell will be formatted.
Caption in row and column: The captions for both the row and column containing the qualifying cell will be formatted.
Entire row: All the cells in the row containing the qualifying cell will be formatted.
Entire column: All the cells in the column containing the qualifying cell will be formatted.
Entire row and column: All the cells in both the row and column containing the qualifying cell will be formatted.
In the Formatting section, you can define how the values or captions will be transformed if the condition evaluates true. To set the formatting for a condition, ensure the condition is selected in the condition list. Then select as follows:
| Command | Description |
|---|---|
|
|
Set the font to bold. |
|
|
Set the font to italic. |
|
|
Set the font to underline. |
|
|
Set the font color. |
|
|
Set the font background color. |
You can also specify whether the formatting will affect flat calculations, totals, or both. By default, all are affected, but you can easily exclude certain items by de-selecting them. The available options are as follows:
In total row: If selected, items in the total row will be affected by the conditional formatting.
In total column: If selected, items in the total column will be affected by the conditional formatting.
In cross-tabs: If selected, flat calculations will be affected by the conditional formatting.
