Conditional formatting in Excel Pivot Tables

Helen Bradley explains some of the differences in PivotTable formatting in Excel.

With the release of Excel 2007, Microsoft provided some additional conditional formatting options such as data bars and icon sets which makes it easy to apply formats to cells to show visually the relative values in those cells. Microsoft also changed how conditional formatting is applied to PivotTables which gives you a greater flexibility when using conditional formats with data summarized in a PivotTable. In this article, I'll show you how to apply conditional formats to PivotTables and how to harness the power of the new behaviors.

How conditional formatting works

Conditional formatting when applied to PivotTables in Excel 2007 - 2016 is applied to the underlying structure of the PivotTable rather than to the cells themselves. So, when you interact with a PivotTable such as moving fields around and viewing your data in different ways, the formatting is updated as you work. This, combined with the new formats, makes Conditional Formatting a handy tool to use when using PivotTables.

How to apply conditional formatting to a PivotTable

We'll look at conditional formatting in relation to this PivotTable which shows the number of clients each of four vets saw in a year broken up by quarter and by the Office - farm or surgery where they worked. To format the data more visually I'll select the Farm and Surgery values for the first vet Browning - ie from cell B6 to cell E7. With this range selected, from the Home tab on the Ribbon I'll select Conditional Formatting > Data Bars and choose a color to use. This formats the first range of data so each formatted cell contains a bar which indicates the relative number of clients seen in each quarter for each office.

Here I've applied a Data Bar format to the first range of data - excluding any totals.
Data Bar format applied to the first range of data - excluding any totals

To extend this formatting to the same data for the other vets, with the first range of cells still selected, I'll click the Formatting Options button which appears at the bottom right of the range and from it choose the third option - in this case it reads choose "Apply formatting rule to ... All cells showing "Sum of Clients" values for "Office" and "Quarter". This applies to the formatting to the same data for every other vet in the Excel PivotTable without the necessity of applying this to each range individually. These same options are also available if you choose to create a new rule using the New Formatting Rule dialog.

It is a simple matter to extend the conditional format to every other similar piece of data in the PivotTable.
Extending the conditional format to other similar pieces of data in the PivotTable

Now, as you interact with the PivotTable and show and hide data, the size of the data bars in the cells changes to reflect the relative size of the value in each cell in relation to every other visible cell with this format applied.

The length of a data bar changes when the data in the PivotTable changes - the length of the bar is relative to the data in all visible cells.
The length of a data bar changes when the data in the PivotTable changes

I can go one step further and compare the total values using a second conditional format. In this case I'll want to compare each vet's totals against each other vet's totals so I'll select cells B5 to E5 - the total of all clients for the vet Browning. By applying a different color data bar to that range, I can then compare the total number of clients Browning has seen over the four quarters.

A Formatting Options button appears here too allowing me to extend this to the total number of clients for each of the vets.

Here a different color data bar has been applied to the totals allowing them to be compared visually with each other.
A different color data bar has been applied to the totals allowing them to be compared visually with each other.

Other options

In some cases limiting the conditional formatting as I have done to only cells which contain data on the same level rather than to the Totals and Grand Totals makes sense - but that won't always be the case.

Here the data in this second PivotTable shows the average number of clients rather than the sum of the number of clients so you could apply a single conditional format to all the table cells.

To do this, I'll select the range B6:E6 and this time I'll choose Conditional Formatting > Data Bars and choose a green color data bar format.

Now, from the formatting options I'll choose the second option: "Apply formatting rule to… All cells showing "Average of Clients" values". This format is now extended to the Grand Total values which, like the rest of the data in the PivotTable is all average values, so it can be legitimately compared this way.

Here all the cells contain average values so a single format is appropriate.
A single format is appropriate because all the cells contain average values.

Moving data around

We'll go back to our original Excel PivotTable and start moving data around. As you do you so you will find that the formatting stays in place.

Here we've moved Office to the Report Filter area and moved Quarter to be a row label and the purple data bar format is still in place.

Even when the table is rearranged and fields moved, the conditional formatting stays in place.
When the table is rearranged, the conditional formatting stays in place.

Limiting conditional formats

Sometimes, of course, you won't want to extend a conditional format to multiple ranges and, instead, you will want to compare data at a more local level. In our example, we might want to see in which quarter each of our vets performed best when assessed on their total results over both offices. In this case I would apply a separate format to the quarter's results for each vet so I'll need to select and format cells D7:D10, then D12:D15 and then D17:D19 and so on.

The format I used was to select Conditional Formatting > Top 10 Items > set it to 1 item and select the default format. This format can be copied from one range to the next if desired or built up for each range individually. To copy the format, select one or more cells with that format and click Copy. Select the range to copy the format to and from the Paste options on the Home tab, select Paste Special > Formats.

In some cases you'll want to compare data within a small area and you won't want the conditional format extended across a series of discontiguous ranges.
The conditional format does not extend across a series of discontiguous ranges.

Excel's conditional formatting feature when combined with the power of a PivotTable lets you be very specific about how you format your data and what data is being compared when you do format it. You can compare like with like across the entire PivotTable or like with like within a single field that you are interested in. Being aware of your options and how to apply them will help you draw the visual comparisons you want to see in your PivotTable data.

See also