Conditional format a pivot table with the wizards

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).

conditional format a pivot table

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.

conditional format a pivot table

Now, while you are still clicked on that cell, click on the Manage Rules button as shown below.

conditional format a pivot table

Click on the rule you want (below we only have 1 but you may have more) and click on the Edit Rule option.

conditional format a pivot table

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.

conditional format a pivot table

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.’

conditional format a pivot table