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

15 Responses to "Conditional formatting in Excel Pivot Tables"

  1. Doug in York PA says:

    Is it possible to apply conditional formatting to a pivot chart?

    (I had wanted to de-emphasize outliers by formatting their bars with "pattern-fill" of faint dots. I had to do this manually for each individual bar in a whole series of charts derived from one pivot table. It was laborious).

  2. Paula says:

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

    How do you get the Formatting Options button to appear? The directions don't detail if after choosing the format you click OK and OK to save the formatting. Either way,
    I don't get a formatting options button.

  3. Cdodds says:

    Where do we download the chart?

  4. chenthorn says:

    Is there a way to modify the formulas so that changes to slicer settings which add/remove rows do not change the conditional formatting rules?

  5. Robert Tencza says:

    Question? How can you apply the conditional formatting to the pivot table in order to automatically apply it to new rows. For example, say the conditional formatting needed to be a heat map or reach row label. So under Browning there is Farm and Surgery. If the Conditional Formatting was a color scale and it was only applied to each row label. So Browning, Davidson, Peters etc. had there own CF. The color scale was unique to each row label. Browning would color scale Farm and Surgery, Davidson would color scale Farm and Surgery. Say Browning now has data for Farm, Surgery and Inventory. Inventory would not be apart of the color scale for Browning because it is not within the range of B6:E7. It would be C8:E8. All I have found is VBA code to solve this issue. Using the "Now, from the formatting options I'll choose the second option: "Apply formatting rule to .. All cells showing "Average of Clients" values" does not work because then it uses the color scale for all data and not unique to that row label group.

  6. Max says:

    Is there a way to apply conditional formatting at the top of the drilable pivot table?
    Example: employee sales rolling up to a regional total and I would like to highlight regions not meeting the quota in a given month.

  7. khalil Najjar says:

    I have a Pivot Table with MONTH as the column heading.
    Each Month (column) has a unique Conditional Format applied based on a unique Formula for each month. The formula calculates the "aging" of the result (ie: Under 12 months old = Blue, Over 12 Moths old = Orange). This works perfectly until you add a unique record to the Raw Data sheet.. Then the Pivot Table formatting is not applied to the new data.
    In order to get the CF to work, I've had to Manually select the Range the CF applies to = in each of the resulting Month columns (example: Jan = $D$12:$D$4157, Feb = $E$12:$E$4157..
    So naturally, when NEW data is entered in the Raw Data sheet, the CF does not apply to it.
    I've tried selecting "All Cells Showing..." but this applies the Formula for Jan to all the other months..
    Is there a way to get around this?
    Is the some VBA code that I can use to reapply the CF rules to the new data in the Pivot Table?
    Please help..

  8. John D says:

    I have a pivot table which tabulates a resources hours by month and provides a total for each month

    I want to provide conditionals formatting on the sum total row for each resource
    1. if the total is > X - show red( Number is in cell at the top of each month column
    2. if the total is < X - show Green( same cell as 1)
    3. Formatting should extend for each resource total based on the selection criteria
    4. each months total hrs can be different based on holidays etc... 1

  9. Jeremy says:

    Is it possible to apply conditional formatting to only what is selected in the reporting filter? I'm experiencing a scenario where it is only applied to the "ALL" selection in the reporting filter. I would like it to be more dynamic as I'm using it for the above and below average of the group I'm selecting in the reporting filter, but the result is that of "All," instead.

  10. guy from that thing says:

    Hi - conditional formatting was working great. I pulled in data via a CSV feed, data types / conditional formatting rules all worked perfectly. Then the publisher changed the format of the CSV URL -- two notable changes I have to reverse the row order and then take the first row as the header row (by default the feed now puts the header row at the bottom). The columns are still the same, but with the rows reversed the conditional formatting will not take. I can apply it to a column but it still will not show. I believe it's having a problem recognizing the type of data in the column--so when I format the column in Excel as a number, and then reapply the conditional formatting still no luck. Any ideas?

  11. Charlie says:

    I've run a macro to create a pivot table. There is CF on 4 of the columns imported into the pivot table, 3 colour scale based on their values. This does not some through on the pivot table, CF is lost. IS there anyway to keep this?

  12. binod kumar gupta says:

    Is it possible to maintain the size of any data as per according like as 0,00,000,0000 and 1 number in pivot table as cirrculum of stucture of working.please help me to sort the way of doing this process.

  13. binod kumar gupta says:

    Is it possible to maintain the size of any data as per according like as 0,00,000,0000 and 1 number in pivot table as cirrculum of structure of working.please help me to sort the way of doing this process.

  14. Aaron says:

    Hello,

    I am using a pivottable to organise data for colleagues to view data without editing the actual contents of information.

    I have a range of data within one field that I have put into 3 groups. I am trying to use conditional formatting to find the average (and then highlight above average and below average) within that group only.

    I work in a warehouse where we have stock located in aisles, but I need to find the average within the different departments of the warehouse.
    So Ambient, Chilled and Frozen. Therefore the field information has the numbers 1 through to 14 for ambient, 20, 21 and 22 for chilled and 30 through to 55 for frozen.

    When i apply the conditional formatting to the "Aisle" range it finds the average across all 3 groups, which isn't useful for me.

    I've tried creating a custom formula to find the range that has a value 1-14 (for example) but it cannot apply to a pivot table.

    Is there a way i can use this formatting to only apply to a group?

    Thanks in advance,
    Aaron.

  15. Kelly says:

    Is it possible to format the grand total differently from the underlying data. Example I want to show the grand total as a number (the calculated FTE) but want to those the underlying data as the % (% of time the EE entered)

    Thanks!
    Kelly

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite 2018.4 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard