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
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 to your users, 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.
It will highlight city which has the highest amount and a city which has the lowest amount when you filter any states with slicer.
And today in this post, you will learn how to apply conditional formatting to a pivot table and all the possible way to use it.
Applying conditional formatting in a pivot table seems tricky at first sight but actually, it’s simple and easy.
In below pivot table, you need to apply “3-Color Scales” on “Month” values.
Let’s follow these steps.
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 that which month has more revenue than other.
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.
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.
In 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 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.
In below pivot table, you have a product wise and week wise sales data.
And, here you need to highlight cell which has the highest sales quantity for a particular product.
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.
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.
Here are the steps you need to follow.
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:
When you apply these icon sets on subtotals, they will consider the highest value as a base for 100%.
Cells 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 and percentage values as per your need.
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 which are blank and you need to apply conditional formatting to those cells where there is no sale.
Follow these simple steps.
Download this samples file from here to learn more.