Shares

Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth \$20, Absolutely Free

# How To Use Conditional Formatting In Pivot Table [Examples]

You know that Pivot Table is one the most important tools to analyze data. And, Conditional Formatting is useful to deliver a strong message to the user along with data. In this post, you will learn about how you can combine these two awesome tools. You will learn to apply conditional formatting in pivot table.

Below, I have used conditional formatting in pivot table to present a dynamic formatting. It will highlight city which has highest amount & a city which has lowest amount every time I filter states with slicer.

I will show you how to can use conditional formatting within pivot table. Along with it, I have listed some inspiring examples you can learn and apply in your work.

## How to Apply Conditional Formatting in Pivot Table?

Applying conditional formatting to a pivot table is as simple as we do for normal cells. In below pivot table, I want to apply “3-Color Scales” on “Month” values.  Please follow these simple steps for that.

• Select any of the cells which have month value.

• Go to Home Tab → Styles → Conditional Formatting → New Rule

• Now, you will get a pop-up window to apply conditional formatting to the pivot table.

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

• Select All Cells Showing “Amount” values for “Month”.
• In “Edit the Rule Description” select 3-Color Scale.
• Change type of minimum, midpoint, and maximum to percent. After that choose a color for all three.

• Click Okay.

By using conditional formatting within a pivot table I have applied  3-color to the cells having a monthly amount. Using this formatting I can easily analyze that which month has more revenue than other.

## Examples to Understand

Using conditional formatting in a pivot table is a smart move. You can use it in different ways. Here I have listed some examples which you can learn and apply in your work. Just go ahead.

### Dynamic Conditional Formatting With Filters

I have already mentioned that when you use conditional formatting in your pivot it will work as dynamic formatting. Whenever you filter values or change data, it will automatically get updated with that change.

In above pivot table, I have applied conditional formatting to highlight the cell with the highest value. When I am selecting 2014, Product-B  has highest sales quantity. And when I am selecting 2015, Product-C has highest sales quantity.

Every time when you filter your pivot table it will automatically consider the current values. And, apply the formatting rule on it.

### Apply Conditional Formatting on a Single Row In Pivot Table

In below pivot table, I have product wise and week wise sales data. And, I want to highlight cell which has the highest sales quantity for a particular product. Please follow these simple steps to do that.

• Select any of the cells with data.

• Go to Home Tab → Styles → Conditional Formatting → New Rule.
• From rule to, select the third option.
• And, from “select a rule” type select “Format only top or bottom” ranked values.
• In edit rule description, enter 1 in the input box and from the drop-down menu select “each Column Group“.

• Apply formatting you want.
• Click Okay.

In above pivot, I have highlighted the cells which have the highest value in its row. 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 Rows group” from rule description. And, if you want to apply the rule on all cells, just select “all values” from the drop down list.

### Conditional Formatting In Pivot Table Based on Another Cell

In below pivot table, I want to apply data bars. But here is a twist that I have target value which is in another cell. And, I want to add data bars on pivot table values by comparing with that value.

So here are the steps to do this. Please follow.

• Select any of the cells which have data in it.
• Go to Home Tab → Styles → Conditional Formatting → New Rule.
• Select the third option from “Apply Rule To”
• Select “Format all cells based on their values” from rule type.
• In rule description, select “Data Bar”.
• In type drop-down menu, select the number for both max and min.
• In min value box, enter 0 and in max input box enter the cell reference E2. (E2 is the cell in which I have the target value).

•  Specify formatting for data bars you want.
• Click OK.

In above pivot table, I have applied conditional formatting using another cell. Now these data bars are linked with a cell and when I will change the values from that cell, data bars will automatically change.

### Apply Conditional Formatting on Subtotals In Pivot Table

In below pivot table, I have sales amount for the different month and I have subtotals for each quarter. Here I want to apply conditional formatting on subtotal instead of month values. I want to apply Icon Sets only on subtotals.

• Select any cell from subtotals.
• Go to Home Tab → Styles → Conditional Formatting → New Rule.
• Select the third option from “Apply Rule To”
• Select “Format all cells based on their values” from rule type.
• In rule description, select “Icon Sets”.
• Select icon style.
• Now, in icon display rule I am using percentage for apply conditional formatting on subtotals. you can also use.
• Number
• Formula
• Percentile

• Click Ok.

How does It work?

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

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

### Apply Conditional Formatting on Blank Cells In Pivot table

In this example, I will show you how to use conditional formatting to highlight blank cells in pivot table.

In above pivot table, I have some cells which are blank. I want to apply conditional formatting on those cells to highlight months where there was no sale for a particulate product.

• Click on the new rule in conditional formatting.
• Select third option in “Apply To Rule”.
• Select rule type “Format only cells that contain.
• Select “Blank” from rule description.

• Apply cell color you want to apply.
• Click OK.

Now, blank cells are highlighted in above pivot table.

## Remove Conditional Formatting from Pivot Table

You can also remove conditional formatting from a pivot table if you don’t want it.

• Select any cell from your pivot table.
• Go to Home Tab → Styles → Conditional Formatting → Clear Rules → Clear Rules from Pivot Table.

Important Note: If you apply conditional formatting to some selected cells in pivot table and after that when you update your pivot conditional formatting will not apply on new cells.

## Conclusion

I hope this post has inspired you to use conditional formatting in pivot table. As when we use conditional formatting in pivot table it will work dynamically. And, you don’t have to update it again & again. You just have to apply it once and it will automatically update when you update your pivot table.

You can also use VBA to apply conditional formatting in your pivot table. Jon from Peltier Tech wrote a nice post about using VBA for applying conditional formatting in pivot table.

But we have more to go.