As shown in the Pivot Tables online course, you can put conditional formatting onto a Pivot Table in such a way that it knows it is in a Pivot table and will adjust with the size of the pivot table. The one problem with this is that you will then struggle to use some of the pre defined formats that Excel has (e.g. Light Red fill with Dark Red Text, Yellow Fill with Dark Yellow Text, Green Fill with Dark Green Text). It is difficult to get it exactly the same and so it may differ from the other conditional formatting on your spreadsheet.
A way around this is to use the wizard on a cell, and then tell Excel to use that conditional format within a pivot.
So in the example below we have clicked only on cell B6 but it doesn’t really matter. You can highlight one cell, a few cells or all the cells in the current Pivot Report. Switch on the conditional format you want (in this example we want to have it turn green if it is bigger than zero).
Note that once we set it (using 0 as the trigger and the predefined Green Fill with Dark Green Text), only the one cell will change.
Now, while you are still clicked on that cell, click on the Manage Rules button as shown below.
Click on the rule you want (below we only have 1 but you may have more) and click on the Edit Rule option.
The dialogue box that will appear (shown below) will contain a section which only appears when the cell is within a Pivot Table. You can now click on the ‘All Cells Showing ‘xxx’ values for ‘xxx’ and ‘xxx’, which tells Excel that the conditional formatting must apply to the Pivot Table as a whole and not just the Selected Cells.
When you click through all the OK’s, all the cells that meet the condition in the Pivot Table will get that predefined formatting as shown below. If you wanted the Grand Totals to also get the conditional formatting, you would have chosen the ‘All cells showing ‘xxx’ values.’
Want to learn more about Advanced Microsoft Excel and using it efficiently? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.