Excel MS Office

Conditional Formatting in Pivot Table

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.

Apply Conditional Formatting in Pivot Table By Using 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.

Color Scale Conditional Formatting In Pivot Table

  • Select any of the cells which have month value.

Select Cell To Conditional Formatting In Pivot Table

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

Select New Rule To Apply Conditional Formatting In Pivot Table

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

Pop Window To Apply Conditional Formatting In 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.

Three Options To Apply Conditional Formatting In Pivot Table

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

Select Options To Apply Conditional Formatting In Pivot Table

  • Click Okay.

Pivot Table With Conditional Formatting In Pivot table

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 Why Using Conditional Formatting in Pivot Table is Helpful 

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.

Dynamic Conditional Formatting In Pivot Table

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.

Apply Conditional Formatting In Pivot Table In Rows

  • Select any of the cells with data.

Select Cell To Apply Conditional Formatting In Pivot Table In Row

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

Highlght Top Values From A Row By Using Conditional Formatting In Pivot Table

  • Apply formatting you want.
  • Click Okay.

Conditional Formatting In Pivot Table For Highlighting Rows

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.

Conditional Formatting In Pivot Table Using Another Cell

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

Apply Conditional Formatting In Pivot Table To Apply Data Bars

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

Use Conditional Formatting In Pivot With Another Cell For Data Bars

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.

Use Conditional Formatting In Pivot Table On Subtotals

Please follow these steps for that.

  • 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

Apply Icons ToSubtotal To Use Conditional Formatting In Pivot Table

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

Highlight-Blank-Cells-By-Using-Conditional-Formatting-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.

  • 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 Conditional Formatting in Pivot Table For Blank Cells

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

Conditional Formatting In Pivot Table With Blank Cells Highlighted

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.

Remove Conditional Formatting In 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.

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.

Please share this tip with your buddies on Facebook | Twitter | Google+ | LinkedIn

Mahtab Khan - MultiWave

Founder and CEO of MultiWave Computer, I happily share my experiences regarding Computer, Mobile, Social Media, Internet and New Gadget technologies. If you're interested for latest updates, Get in touch with me and follow on facebook, twitter and Subscribe Multiwave Newsletter.

More Posts - Website

Follow Me:
TwitterFacebook

Leave a Reply

Your email address will not be published. Required fields are marked *

five × three =