12

How to Apply Conditional Formatting to Pivot Tables

Bottom Line: Learn how to apply conditional formatting to pivot tables so that the formats are dynamically reapplied as the pivot table is changed, filtered, or updated.

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the Excel File

Here's the file that I use in the video.  You can use it to practice adding, deleting, and changing conditional formatting on a variety of pivot table examples.

Pivot Table Conditional Formatting.xlsx (63.1 KB)

What is Conditional Formatting?

Formatting cells to be different from surrounding data helps it to stand out and get noticed.  Conditional formatting allows you to setup rules to format cells based on criteria, so that the  cell formatting is applied automatically.

Conditional Formatting Pivot Tables Faster and Easier to Read

This allows us to easily spot trends and anomalies in our reports.  A few examples are, but the possibilities are endless:

  • Revenue that exceeds a certain dollar amount
  • Products that are performing poorly
  • Time series trends
  • and more.

Conditional Formatting can change the font, fill, and border colors of cells.  It can also add icons and data bars to the cells.

The formatting will also be applied when the values of cells change.  This is great for interactive pivot tables where the values might change based on a filter or slicer.

How to Setup Conditional Formatting for Pivot Tables

Setting up conditional formatting for pivot tables is a little different than it is for regular cells/ranges.  So in this post I explain how to apply conditional formatting for pivot tables.

1. Select a cell in the Values area

The first step is to select a cell in the Values area of the pivot table.  If your pivot table has multiple fields in the Values area, select a cell for the field you want to apply the formatting to.

Select cell inside pivot table before applying conditional formatting

2. Apply Conditional Formatting

You can find the Conditional Formatting menu on the Home tab of the Ribbon. It opens up a lot of great options when it comes to setting rules or conditions.  In my example, I use the Top 10 Items rule.

Conditional Formatting Menu..

In the example I've changed the 10 to 3 so that only three cells with the highest values are selected. Then I chose a green fill option for the format.

Top 10 Items Window

This new formatting rule will find the highest three values in the range I've selected. Then it will change those cells to have a green fill with dark green text.  However, since I only started with one cell selected instead of a range, only that one cell has changed (it is the highest value by default).

That's fine because what we are interested in is the Formatting Options menu. This menu appears next to the newly formatted cell. This menu allows you to expand the formatting rule to similar surrounding data.

Formatting Options menu appears by the conditional formatting cell

3. Using the Formatting Options Menu

You can use the Formatting Options menu to apply the formatting to the field in the Values area.

Choosing the second option in the menu (see image above) applies the formatting rule to any of the values under the “Sum of Revenue” heading, including the Grand Total. In the image below, you can see that the highest three values are green.

Top 3 Items in Sum of Revenue

But it's pretty obvious that the Grand Total is always going to be the biggest value. So quite often you are going to want to exclude the Grand Total from your formatting. That way you are comparing similar data (in this case, Products 1 through 7) when you call out your top three. If you want to exclude the Grand Total, you can choose the third option in the Formatting Options menu: All cells showing “Sum of Revenue” values for “Product.”

Top 3 Items in Sum of Revenue, excluding grand Total

4. Accessing the Formatting Options After You've Made Changes

The Formatting Options button Formatting options button appears when you first apply the conditional formatting.  It disappears after you make changes to the sheet.  If you want to see those options again, you can find the rules in the Edit Formatting Rule window. You can access this by clicking on the Conditional Formatting button, selecting Manage Rules, then selecting the rule and clicking Edit Rule.

Conditional Formatting Manage Rules..

Conditional Formatting Rules Manager

This brings up the Edit Formatting Rule window. The top of the window lists the same options that appeared in our menu.

Conditional Formatting for Pivot Tables Edit Formatting Rules Options

Conditional Formatting with Multiple Fields

If your pivot table has multiple fields in the Rows or Columns areas, then it will also likely have subtotal rows & columns.  In this case we have to make sure we are applying the formatting to the right fields.

The easiest way is to first select a cell at this intersection BEFORE applying the conditional formatting.  You can also change the cell later.

By changing the cell that the rule focuses on, you'll see that your options for applying formatting change as well.  For example, when I begin the process with the cell B6 selected, my options include All cells showing “Revenue” values as well as All cells showing “Revenue” values for “Product” and “Qtr”:

Formatting options including subtotals

But when I change the focus cell to B7, the subtotal fields (Product) are no longer an option.  Instead my choices are All cells showing “Revenue” values and All cells showing “Revenue” values for “Region” and “Qtr”:

Formatting options excluding subtotals

The difference is essentially including or excluding the subtotal fields from the range of cells that you want to be formatted.

You can always change the fields the rule is applied to in the Edit Formatting Rule window.

Apply Rule To Cell Conditional Formatting Pivot Table

The Apply Rule To box displays the cell that intersects the fields.  You can select a different field in the pivot table with this window open to change the fields.  See the video above for details.

What Happens When the Pivot Table Changes?

The great thing is that when the pivot table changes, Excel remembers what rules you've applied. It therefore maintains the appropriate formatting despite the rearranged data.

The point of a pivot table is that you can quickly and easily change your perspective of the data. For example, with the click of a button you can go from looking at sales by region to sales per rep, to reps per region.

Conditional formatting rules remain intact regardless of how the data changes.  This includes applying filters with slicers, modifying the layout and options, or moving the included fields in the Rows or Columns area.

For example, here is a portion of a pivot table that has products listed under each region.

And here is the same pivot table that instead breaks out the regions under each product.

The green-fill formatting for the top ten entries remains intact even though I've rearranged the data.

An Exception

Conditional formatting will be deleted if any of the fields that are included in the formatting are removed from the pivot table.  This is simply because Excel cannot apply rules to fields that no longer exist.

When you access the Conditional Formatting Rules Manager after removing one of the fields, you'll notice that the rule is completely gone.

Conditional formatting rule deleted

More Information About Pivot Tables and Conditional Formatting

Need a refresher on pivot tables?  Check out this post on Pivot Tables & Dashboards to watch the first of three videos in a series that talks about pivot tables, pivot charts, PivotPal, and more.

Here are some others posts about conditional formatting:

What type of Conditional Formats do you want to learn?

This post and video explains how to apply conditional formatting to pivot tables.  In the video and companion Excel file I show a few examples.

What other types of conditional formatting do you want to learn?  Please leave a comment below with the type of formatting or type of report, and I will create some follow-up tutorials.

Thank you! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 12 comments
stella - December 20, 2018

really useful and easy to learn

Reply
Stella - December 20, 2018

this is wonderful

Reply
Rachel Rigby - December 17, 2018

Helpful – thank you

Reply
    Jon Acampora - December 17, 2018

    Awesome! Thanks Rachel! 🙂

    Reply
Jackline Gerges - December 16, 2018

Thank you so much

Reply
Mark Conrot - December 14, 2018

Excellent and well explained, thank you very much, this wil be of great help to me.
Mark

Reply
Dale A Warner - December 13, 2018

This is an excellent video. Although I had used the technique before, I will definitely be forwarding the edition of the newsletter to others on my team. I have added your site and articles to our corporate “Excel Power Site”. Thanks again, Jon!

Reply
    Jon Acampora - December 14, 2018

    Thank you Dale! You sharing the newsletter really helps me out, and I appreciate your support! 🙂

    Reply
Paul - December 13, 2018

Hi John,

Thanks for these tutorials. Is there a way to apply the conditional formatting to each individual column in the values area of a pivot table, rather than to the entire values area? For instance, using your pivot table with the 4 quarters as an example, if I wanted to see the Top 3 products within in each quarter, how would I do that?

It seems to me that the conditional formatting always gets applied to the entire values space, and thus compares the quarters against one another, when what I actually want to do is to restrict the conditional formatting to only comparing the values within the same column against one another. I’ve searched online but I’ve been unable to find a solution to this, which surprises me because it seems like a feature that most analysts would find enormously useful. I know I would!

Thanks!

Paul

Reply
    Jon Acampora - December 17, 2018

    Hey Paul,
    Great question! I don’t believe there is a direct way to do this with the conditional formatting setting for the pivot table. Those settings are applied at the pivot field level, and not the pivot item level. In the example of Quarters, each quarter (Q1, Q2, Q3, Q4) would be a pivot item. The conditional formatting is applied at the field level.

    I’ll give it some thought, but you would probably have to create conditional formatting rules for each column in the pivot table. You could then create Dynamic named ranges for each column so you don’t have to update the ranges as the pivot table changes size. It’s not an ideal solution that would work depending on the scenario.

    I hope that helps.

    Reply

Leave a Reply: