What’s the issue with Conditional Formatting and Pivot Tables?
Creating Pivot Table Conditional Formatting is a little different to how you would usually Conditionally Format a spreadsheet.
If you have no idea what Conditional Formatting is then think of it as a warning system. When the engine light comes on in your car you know there could be a problem. Now, imagine that you had a similar system for your figures. Say, for example, if a number in a Pivot Table is below £10 then a light comes on. No not really, but it could change to a red colour. In that way alerting you to the fact that there could be a problem.
Now we enter the world of Pivot Tables. You see, if you try to use Conditional Formatting with Pivot Table the same way as you would a range, you’ll start to see strange things happening. No, no, no. It’s not spinning lights, beings from Mars or the lost city of Atlantis rising from the depths. But you will quickly lose all confidence the accuracy of the colours on your Pivot Tables.
So how to add Conditional Formatting to a Pivot Table?
In order to combat this infuriating problem you’ll need to know how to add conditional formatting to a Pivot Table. Ok, so let’s do that.
To start off with you will need to click in the Pivot Table you wish to add Conditional Formatting to. (It’s important to understand that you need to select the grouping level of the values you want to format.)
After you’re sure about the level you want the conditional formatting to added do the following.
From the Home tab select Conditional Formatting – Highlight Cell Rules – Less than.
After that, type in the less than value of box the number 10, should you want to look for values of £10 or less.
Now it’s time to select the colour of any cells in the Pivot Table that have a value of less than £10. Fortunately, the box to the right has already selected the red colour.
Then click OK, you will then see the formatting applied to a single cell.
Now, using the Formatting Rules button in the bottom right of the cell you have just applied the Conditional Formatting rule to, select “All cells showing % Profit Values”
As a result, you should now see a Conditionally Formatted Pivot Table, well done. Now it’s time to pat yourself on the back, go get a cup of tea or run round the block a few times shouting I’m a Pivot Table genius.
If, however, it’s still not working as you might expect check out this really cool Pivot Table Conditional Formatting video. In fact the first screenshot in this post is taken from that video.