How To Apply Conditional Formatting To An Excel Pivot Table

This next tip I know causes a bit of frustration with Excel users, based on the number of times I have been asked about it. It is applying conditional formatting to a Pivot Table. The method of applying conditional formatting to a Pivot Table is not the same as applying it to a regular data set.

In a regular data set, you would probably use the method below to highlight all sales values above the average in the data set.

This is all working well for us, with the conditional formatting applied to cells D3:D8. But, if we were to use the same method in a Pivot Table anytime new data was added to the table then the formatting would not be applied to any new data.

Let’s take a look at applying conditional formatting correctly to a Pivot Table and make it adapt and update automatically as we add new data to it.

Here is my same data but in Pivot Table format.

Making The Conditional Formatting Stick.

So, to make the same conditional formatting adapt and remain on the Pivot Table no matter how many new rows of data we add into it, follow these easy steps using the Conditional Formatting Rules Manager.

Select the data you want to apply conditional formatting to (D11:D16)
Home Tab | Styles | Conditional Formatting
Top/Bottom Rules
Above average
Specify the formatting if the argument is true

So far so good all is the same as if applying formatting to a regular range of cells. The next bit is the smart stuff.

Home Tab | Styles | Conditional Formatting | Manage Rules
Select your rule and hit Edit Rule – this is the clever bit

In the Edit Rule section, you will see 3 options-

Selected Cells
All cells showing “Sum of Sales Value” values
All cells showing “Sum of Sales Value” values for ” Sales Rep”

The first option will only apply the formatting to selected cells. Like in the conditional formatting of a regular data set. The second option will apply formatting to all of the cells that contain Sum of Sales Value. This will also take into consideration the Grand Total values.

The third option is the one I want to use. This choice will apply the conditional formatting to values that are the combination of Sum of Sales Value AND Sales Rep.

You can now add new data to your Pivot Table and the conditional formatting will take into account your new data. However, if you change the Pivot Table row/column fields then the conditional formatting will be lost. These rules are great for ongoing reports where new data is added into the dataset but the structure remains.

That’s it, folks. I hope you enjoyed this instalment of Macro Monday.

What Next? Want More Excel Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

How To Excel At Excel

How To Apply Conditional Formatting To An Excel Pivot Table – Correctly – Excel Tip

Making The Conditional Formatting Stick.

What Next? Want More Excel Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Recent Posts