How to Apply Conditional Formatting to a Pivot Table in Excel

Last Updated: June 22, 2023
puneet-gogia-excel-champs

- Written by Puneet

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

You can create instant reports using pivot tables. And to make it more appealing you can apply conditional formatting to a pivot table. Just look at below where I have applied conditional formatting in a pivot table to highlight cells.

apply conditional formatting in pivot table by using slicer

It will highlight the city which has the highest amount and the city which has the lowest amount when you filter any states with a slicer. And today 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.

Steps to Apply Conditional Formatting to a Pivot Table

Applying conditional formatting in a pivot table seems tricky at first sight but actually, it’s simple and easy. In the below pivot table, you need to apply “3-Color Scales” on “Month” values. Let’s follow these steps.

color-scale-conditional-formatting-in-pivot-table
  1. First of all, select any of the cells which have month value.
    select cell to conditional formatting in pivot table
  2. Then, go to Home Tab → Styles → Conditional Formatting → New Rule
    select new rule to apply conditional formatting in pivot table
  3. Here, you will get a pop-up window to apply conditional formatting to the pivot table.
    pop window to apply conditional formatting in 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.
      three options to apply conditional formatting in pivot table
  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.
    select options to apply conditional formatting in pivot table
  8. In the end, click OK.
    pivot table with conditional formatting in pivot table

On the above pivot table, you have applied 3-color formatting to the cells having a monthly amount. 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 a smart move and you can use it in different ways. Here is a list of some examples which you can learn and apply in your work instantly.

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, it will automatically get updated with that change.

dynamic conditional formatting in pivot table

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 when for 2015, Product-C has the highest sales quantity.

Every time when you filter your pivot table it will automatically consider the current values and apply the formatting rule to it.

Related: Excel Slicer

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

In the below pivot table, you have product-wise and week-wise sales data. And, here you need to highlight the cell which has the highest sales quantity for a particular product.

apply conditional formatting in pivot table in rows
  1. Select any of the cells.
    select cell to apply conditional formatting in pivot table in row
  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“.
    highlight top values from a row by using conditional formatting in pivot table
  6. Apply formatting you want.
  7. Click OK.
    conditional formatting in pivot table for highlighting rows

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.

And, if you want to apply the rule on all cells, just select “all values” from the drop-down list.

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

In the below pivot table, 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 comparing to that target value.

conditional formatting in pivot table using another cell

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.
    apply conditional formatting in pivot table to apply data bars
  5. In the end, specify the formatting for data bars you want and click OK.
    use conditional formatting in pivot with another cell for data bars

4. Apply Conditional Formatting on Pivot Table Subtotals

In the below pivot table, you have sales amount for the different months and quarters and subtotals for each quarter. Here you need to apply conditional formatting (Icon Set) on subtotals instead of month values.

Follow these steps:

apply icons to subtotal to use conditional formatting in pivot table
  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 the rule type.
  3. In the rule description, select “Icon Sets” and select Icon style.
  4. Now in the icon display rule, use percentage to apply conditional formatting on subtotals.
  5. In the end, click OK.

When you apply these icon sets on subtotals, they will consider the highest value as a base for 100%. Cells that have values higher than 67% will get a green icon, cells that have values from 33% to 67% will get a yellow icon, and other cells below that will get a red icon.

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

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

The best way to highlight blank cells is by using conditional formatting and you can use it in a pivot table as well. In the below pivot table, some cells are blank and you need to apply conditional formatting to those cells where there is no sale.

highlight blank cells by using conditional formatting in pivot table

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.
    apply conditional formatting in pivot table for blank cells
  3. Apply cell color for formatting and click OK.
    conditional formatting in pivot table with blank cells highlighted

Sample File

download

3 thoughts on “How to Apply Conditional Formatting to a Pivot Table in Excel”

  1. Is there a way to apply conditional formatting based on the results in the Value fields. For instance, in the last example with the Product A thru J, can you highlight the name of the Product that had no sales in December? In other words, Product-C would be highlighted in red as well as its corresponding Dec cell where it shows that product had no sales.

    Reply
  2. If I have a Pivot Table with columns that will change based on the data input how can I conditional format. It appears that the formatting is linked to certain cells but if I add a column and it shifts the others my formatting will be wrong.

    Is there a way to fix this? have excel look at what the column name is and row name is and format based on that?

    Reply
  3. I don’t get that option to apply to what cells – I always only get the regular formatting text box. Using ecel 2013

    Reply

Leave a Comment