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

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.

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.

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.

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.

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.

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.

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.

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

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

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

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

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

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

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.

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.

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:

• How to Filter for Duplicates with Conditional Formatting
• Create a Progress Doughnut Chart with Conditional Formatting
• 2 Ways to Apply Conditional Formatting to Shapes

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! 🙂