Applying Conditional Styles

Last modified: June 26, 2023

Conditional styles give you freedom to change a pivot table appearance to make the data more readable. For example, you can highlight values of the field that are greater than 100 with the red color, and values of another field that meet some other condition with the blue color, the difference between data in a pivot table will be clear at a glance.

Besides setting a background color for some cells, you can select an image as a background, change a font and its color, specify a condition to apply only for some values in a required field, etc.

Let’s examine conditional styles benefits by the following example:

There is a list of films and their rental payments grouped by month and year. We need to quickly identify which films bring the expected revenue, the sum of their rental payments meet the planned monthly range of 20-40 and which films are in less demand, the sum of their rental payments are less than 10.

To use a conditional style

1. Open the Conditional Styles dialog box by either of these ways:

• On the Pivot Table document view toolbar, click Conditional Styles.

• Right-click the pivot table header and select Conditional Styles on the menu.

2. In the Conditional Styles dialog that opens, click Add Condition to create a new condition.

3. In the Field drop-down list, select the payment amount field and go to the Condition field to select the Between value.

4. In the Value1 and Value2 fields, enter 20 and 40 values respectively to specify the required range of values.

5. In the Apply To section, clear all the check boxes except the Cell one, as there is no need to apply this condition for all the cell types in the pivot table.

6. In the Appearance section, click the BackColor field and select LightBlue. Now, the first condition is ready.

7. Click Add Condition to create another condition for the films which have the sum of payment amounts less than 10.

8. In the Field drop-down list, select the payment amount field and go to the Condition field to select the Less value.

9. In the Value1 field, enter 10 and leave only the Cell check box selected in the Apply To section.

10. In the Appearance section, select the red color from the ForeColor field to change the font color of the payment amount values into red.

11. Click OK to apply the conditional styles to the pivot table. The data will look like the following: