Elements, to which conditional formatting is appliedConditional formatting is used only when the specified condition is met Conditional formatting makes data analysis more visual.
Conditional formatting can be applied to several table elements and can consist of several conditions, for example:
Elements, to which conditional formatting is applied
Dimension element (row/column).
Calculated element.
Level.
Dimension.
Entire table (data).
Column totals.
Row totals.
Order of application of conditional formatting
Settings of different conditional formatting types (color schemes, color scales, icons, histograms) overlap each other. If table elements have identical formatting type, settings are applied in the following order:
Entire table (data).
Dimension.
Level.
Dimension element.
Calculated element.
Features of formatting application:
Conditional formatting determined for calculated elements (calculated hierarchy) or alternative hierarchy is more important than conditional formatting determined for dimension elements.
Conditional formatting of row totals (displayed in the column) is more important than column totals (displayed in the row).
If conditional formatting in the row overlaps the conditional formatting in the column, the conditional formatting by column is applied. This rule is applied equally to other table elements.
If several dimensions are located in heading or sidehead, for which conditional formatting is set, the higher importance is given to elements of the last dimension located in the heading or sidehead. This rule is applied equally to other table elements.
Conditional formatting is applied according to the the selected measurement unit.
Conditional formatting can be applied by means of:
The Conditional Formatting side panel tab
The tab can be used to create, edit, set up application order and delete formatting conditions.
To display the tab:
Select a table element.
Select the Table, Format or Totals radio button on the side panel. The radio button name depends on the area selected in the table.
Go to the Conditional Formatting tab.
The tab look depends on the type of formatting condition to set up, for example:
The Conditional Formatting ribbon button
The button allows for a quicker application of conditional formatting, editing and deleting of created conditions.
The Conditional Formatting button is located on the following ribbon tabs:
Home. The Analytical Queries (OLAP), Reports tools.
Designer. The Analytical Queries (OLAP), Dashboards tools.
Format. The Analytical Queries (OLAP), Reports, Dashboards tools.
NOTE. When working with the Reports tool, if conditional formatting is set up using the Home ribbon tab, it is applied to the report sheet; if it is set up using the Format tab, it is applied to the table.
The button is available if some table element is selected, from which conditional formatting is available.
The Conditional Formatting button contains the menu with the following conditional formatting types:
Color scales:
Histograms:
Icons:
The Edit Conditions dialog box
The dialog box allows can be used to create, edit, set up application order and delete formatting conditions.
NOTE. The feature is available only in the desktop application.
To show the dialog box:
Select a table element, for which conditional formatting is available.
Click the Conditional Formatting button on the Home, Designer or Format ribbon tabs.
Select the Edit Conditions item in the menu that opens.
To create a formatting condition, select a table element, for which conditional formatting is available, and then:
Go to the Conditional Formatting side panel tab and click the Add button. The tab displays setup parameters for the created condition.
Select a formatting type in the drop-down menu of the Conditional Formatting ribbon button. The selected formatting type is applied to the selected table area.
Open the Edit Conditions dialog box and click the Add button. The Formatting Condition Parameters dialog box opens. Set up the created formatting condition in it.
NOTE. The Edit Conditions dialog box is available only in the desktop application.
Click the Conditional Formatting button on the ribbon and select the Create Condition item in the menu that opens. The Formatting Condition Parameters dialog box opens. Set up the created formatting condition in it.
NOTE. This feature is available only in the desktop version of the Reports tool.
To edit a formatting condition:
Select the table element, for which conditional formatting is set up.
Go to the Conditional Formatting side panel tab.
Select the required condition.
The tab displays condition parameters.
Methods of editing formatting condition available only in the desktop application:
Select the table element, for which conditional formatting is set up.
Open:
The Conditional Formatting side panel tab.
The Edit Conditions dialog box.
Select the required condition and:
Double-click it with the main mouse button.
Select the Edit item in the condition's context menu.
Click the Edit button. The method is available only in the Edit Conditions dialog box.
The Formatting Condition Parameters dialog box opens. Set up the created formatting condition in it. This dialog box can be used to set up all formatting conditions, except for fill by cell groups based on their values.
TIP. For details about available formatting conditions see the Setting Up Conditional Formatting section.
Change order of formatting conditions
Formatting conditions are applied in the order, in which they are placed on the Conditional Formatting tab on the side panel or in the Edit Conditions dialog box. The order of conditions on the side panel tab matches with that in the dialog box.
To change order of formatting conditions:
Select the table element, for which conditional formatting is set up.
Open:
The Conditional Formatting side panel tab.
The Edit Conditions dialog box. The dialog box is available only in the desktop application.
Select the required condition and use the and buttons.
The desktop application contains additional options:
Select the Up or Down items in the condition's context menu.
Hold down the main mouse button and drag the condition to the required position.
Press ALT + ↓ to move the condition one level up and ALT + ↑ to move the condition one level up.
Change effective range of formatting condition
NOTE. The feature is available only in the desktop version of the Reports tool.
Select the table element, for which conditional formatting is set up.
Open the Edit Conditions dialog box.
Select the Range cell corresponding to the required condition.
Enter a new range by means of the keyboard or select the required range on the report sheet.
The formatting condition will be applied to the changed range.
To delete formatting condition:
Select the table element, for which conditional formatting is set up.
Open:
The Conditional Formatting side panel tab.
The Edit Conditions dialog box. The dialog box is available only in the desktop application.
Click the Delete button.
The desktop application contains additional options:
Select the Delete item in the condition's context menu.
Press the DELETE key.
The selected formatting condition is deleted.
Delete all formatting conditions
To delete all formatting conditions:
In the Analytical Queries (OLAP) and Dashboards tools:
Select the table item, for which all formatting conditions should be deleted.
Select the Clear item in the drop-down menu of the Conditional Formatting ribbon button.
In the Reports tool:
Select the table item, for which all formatting conditions should be deleted.
Select the Delete Conditions item in the drop-down menu of the Conditional Formatting ribbon button.
Select the method of condition deletion:
Clear Conditions from Selected Cells. All formatting conditions are deleted from the selected table range.
Clear Conditions from Entire Sheet. All formatting conditions are deleted from the sheet.
Formatting conditions are deleted.
See also: