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.
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.
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
- Home Tab | Styles | Conditional Formatting | Manage Rules
- Select your rule and hit Edit Rule – this is the clever bit
- Selected Cells
- All cells showing “Sum of Sales Value” values
- All cells showing “Sum of Sales Value” values for ” Sales Rep”
That’s it, folks. I hope you enjoyed this instalment of Macro Monday.