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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Please follow these steps for that.
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.
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.
Please follow these simple steps for that.
Now, blank cells are highlighted in above pivot table.
You can also remove conditional formatting from a pivot table if you don’t want it.
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.
But we have more to go.
I need your help.
If there are some idea you have, about using conditional formatting in your pivot table please share with me in the comment box.
And, Please don’t forget to download my new Excel Productivity Guide which is fully loaded with useful excel tip like using conditional formatting in pivot table.