Advanced Conditional Formatting Setup

To set up conditional formatting, use the Conditional Formatting tab on the side panel or the Edit Conditions dialog box.

To open the dialog box

Parameters available for setup depend on formatting condition to be set up, for example:

Creating a Conditional Formatting

Click the Add button to add a conditional formatting:

• On the Conditional Formatting tab. The Three-Color Gradient type is added by default.

• In the Edit Conditions dialog box. The Formatting Condition Parameters dialog box opens.

NOTE. The desktop version of the Reports tool also has the Create Condition command in the drop-down menu of the Conditional Formatting button on the Home ribbon tab.

Editing Formatting Condition

To edit formatting condition, select the required condition:

• Select the Edit context menu item.

• On the Conditional Formatting tab in the Selection Properties area and set available condition parameters. Available parameters depend on the type of configured condition.

• In the Edit Conditions dialog box and click the Edit button or double-click the mouse. 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 types of formatting conditions except for fill by groups of cells based on their values.

NOTE. In the web application the Conditional Formatting tab opens on the side panel.

Changing Formatting Condition Order

To change formatting condition order, select the required condition and:

• Use the and buttons.

• Hold down the main mouse button and drag the condition to the required position.

• Select the Up or Down context menu item.

• Press ALT + DOWN to move the condition one position down or ALT + UP to move the condition one position up.

Deleting Formatting Condition

To delete formatting condition, select it and:

• Click the Delete button.

• Press the DELETE key.

• Select the Delete context menu item.

To delete all formatting conditions, select the Clear item in the drop-down menu of the Conditional Formatting button on the ribbon.

NOTE. In the Reports tool use the menu of the Conditional Formatting button on the Format ribbon tab.

In the Reports tool:

1. Select the Delete Conditions item in the drop-down menu of the Conditional Formatting button on the Home ribbon tab.

2. 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.

Available Formatting Condition Types

• Format cells based on their values.

• Format cells with definite value.

• Format cells with greatest/least values.

• Format cells with values greater/less than average.

• Format cells with unique/duplicate values.

• Format cells satisfying a formula.

NOTE. Take into account the following feature of the scales used for conditional formatting: boundary values are always included into the smaller scale interval. For example, there is a scale with three intervals: less than 25, from 25 to 50, greater than 50. The 25 value will be in the Less Than 25 interval, the 50 value will be in the From 25 To 50 interval.