How to Apply Conditional Formatting to a Pivot Table in Excel

You know that the pivot table is one of the most important tools to analyze and summarize data.

Pivot tables can be used to create instant reports. To make them more appealing, conditional formatting can be applied to them.

Look below, where I applied conditional formatting in a pivot table to highlight cells.

Filming any state with a slicer will highlight the city with the highest and lowest amount.

In this post, you will learn how to apply conditional formatting to a pivot table and all the possible ways to use it.

Note: Pivot tables are one of the INTERMEDIATE EXCEL SKILLS.

Access the Sample File

Steps to Apply Conditional Formatting to a Pivot Table

Applying conditional formatting in a pivot table seems tricky initially, but it’s simple and easy.

In the pivot table below, you must apply “3-Color Scales” to “Month” values. Let’s follow these steps.

1. First of all, select any of the cells which have month value.
   
2. Then, go to Home Tab → Styles → Conditional Formatting → New Rule
   
3. Here, you will get a pop-up window to apply conditional formatting to the pivot table.
   
4. In this pop-up window, you have three different options to apply conditional formatting in pivot table.
   • Selected Cell: Use this option to apply conditional formatting only to the selected cell.
   • All Cells Showing “Amount” Values: All the cells which have amount values.
   • All Cells Showing “Amount” Values For “Month”: All cells which have amount values but only for months.
     
5. Select All Cells Showing “Amount” values for “Month”.
6. In “Edit the Rule Description” select 3-Color Scale.
7. Change type of minimum, midpoint, and maximum to percent. After that choose a color for all three.
   
8. In the end, click OK.
   

On the above pivot table, you have applied 3-color formatting to the cells with monthly amounts. Using this formatting, you can easily analyze which month has more revenue than others.

Related: The Complete Guide to Conditional Formatting in Excel

Examples: Apply Conditional Formatting in a Pivot Table

Using conditional formatting in a pivot table is smart; you can use it differently. Here is a list of examples you can learn and apply instantly to your work.

1. Dynamic Conditional Formatting with Filters

When you use conditional formatting in your pivot, it works as dynamic formatting. Whenever you filter values or change data, the formatting automatically updates with that change.

In the above pivot table, you have applied conditional formatting to highlight the cell with the highest value.

When you click on 2014, Product B has the highest sales quantity, and for 2015, Product C has the highest sales quantity.

When you filter your pivot table, it automatically considers the current values and applies the formatting rule.

Related: Excel Slicer

2. Apply Conditional Formatting on a Single Row in a Pivot Table

You will find product and week-wise sales data in the pivot table below. Here, you can highlight the cell with the highest sales quantity for a particular product.

1. Select any of the cells.
   
2. Go to Home Tab → Styles → Conditional Formatting → New Rule.
3. From rule to, select the third option.
4. And, from “select a rule” type select “Format only top or bottom” ranked values.
5. In edit rule description, enter 1 in the input box and from the drop-down menu select “each Column Group“.
   
6. Apply formatting you want.
7. Click OK.
   

When you select “each Column group” (Rows), conditional formatting will compare values only from a row.

If you want to apply this rule to columns instead of rows you can select “each row group” from the rule description.

If you want the rule to apply to all cells, select “all values” from the drop-down list.

3. Conditional Formatting in a Pivot Table Based on Another Cell

In the pivot table below, you need to apply data bars. But here is a twist: You have a target value in a different cell, and you need to apply data bars to compare it to that target value.

Here are the steps you need to follow.

1. First of all, select a cell and go to Home Tab → Styles → Conditional Formatting → New Rule.
2. Then, select the third option from “Apply Rule To” and select “Format all cells based on their values” from rule type.
3. Now, in the rule description, select “Data Bar” and in type drop-down menu, select the number for both max and min.
4. From here, in min value box, enter 0 and in the max input box, enter the cell reference E2.
   
5. In the end, specify the formatting for data bars you want and click OK.
   

4. Apply Conditional Formatting on Pivot Table Subtotals

The pivot table below shows sales amounts for the different months and quarters and subtotals for each quarter.

Here, you must apply conditional formatting (Icon Set) to subtotals instead of month values.

Follow these steps:

1. First, select a cell and go to Home Tab → Styles → Conditional Formatting → New Rule.
2. Then, select the third option from “Apply Rule To” and select “Format all cells based on their values” from the rule type.
3. In the rule description, select “Icon Sets” and select Icon style.
4. Use percentage to apply conditional formatting on subtotals in the icon display rule.
5. In the end, click OK.

When you apply these icon sets to subtotals, they will consider the highest value as a base of 100%.

Cells with values higher than 67% will get a green icon, cells with values between 33% and 67% will get a yellow icon, and cells below that will get a red icon.

You can change icons and percentage values as per your needs.

5. Apply Conditional Formatting on Blank Cells in a Pivot table

The best way to highlight blank cells is by using conditional formatting, which you can also use in a pivot table.

In the pivot table below, some cells are blank, and you need to apply conditional formatting to those cells where there is no sale.

Follow these simple steps.

1. Click on the new rule in conditional formatting and select the third option in “Apply To Rule”.
2. Select rule type “Format only cells that contain” and select “Blank” from rule description.
   
3. Apply cell color for formatting and click OK.
   

Points to Take Care of While Applying Conditional Formatting

Get the Basics Right: Understand how conditional formatting works before applying it to a pivot table. Conditional formatting allows you to automatically apply colors, bold, and italics to specific cells based on their values.
Apply Formatting to Values: Conditional formatting can be applied to the values area of a pivot table to highlight specific results. It can help identify trends or outliers in your data.
Caution with Grand Totals: Be cautious when applying conditional formatting to grand totals or subtotals, as the conditions may not make sense when applied to these totals.