Skip to content
Related Articles

Related Articles

Pivot Table Conditional Formatting in Excel

Improve Article
Save Article
  • Last Updated : 27 May, 2021
Improve Article
Save Article

One of the most useful ways to customize the pivot table formatting is using Conditional Formats. Conditional formatting rules can be applied to Pivot tables just like they can be applied to normal data ranges. So by using conditional formatting, we can highlight the cells with a certain color depending on the cell’s value.

HOME -> Conditional formatting

Types of Conditional Format:

There are many conditional formats that we can use on our data. Some of them are given below: 

1. Text or value-based formats: In this particular formatting, we have two options available.

  • Firstly, we can highlight the cells that are greater or less than a particular number, or are between a particular range, or are equal to a particular number.

Conditional formatting -> Highlight cell rules

For example, if we take the data on the average temperatures experienced month-wise (in F) and if we want to highlight the cells having temperature greater than 50 F (basically we want to visualize on the months having average temperature greater than 50 F), then we can use conditional formatting.

All the months having temperature > 50 F

  • Secondly, we can highlight the cells that are the top 10 items or the bottom 10 items or occur in the top 10% or bottom 10% of that particular column.

2. Data bars: It is also a great way to visualize relative values. Given below is the same example of average temperature displayed month-wise having the visualization of data bars. 

We see that August has the maximum average temperature

We can also edit this formatting rule by going to the Manage rules option and then select the option of “Show only data bar” if we want only the data bars to be displayed and not the numbers. 

Here only the data bars are shown and not the numbers. 

3. Color scales: A great way to create some heat maps for our data. So we will take the same example of displaying month-wise average temperatures. In this formatting, we will use a Red – White – Blue color scale in which maximum temperatures will be colored red and minimum will be colored blue. 

So we can see that August was the hottest month and January was the coolest one. 

4. Icon sets: In this type of formatting, we use directional arrows, shapes, and various indicators to visualize our data. 

We use green arrow for values > 67 and red arrow for values < 47.

5. Formula-based rules: We can also create our own rule and apply conditional formatting to them. 

  • These were basically the 5 types of conditional formats that we can use to visualize our data in a more convenient and simpler way.
  • If we want to clear any rule or edit any rule, we can simply go to the option of Clear rules and Manage rules respectively. We can clear any rule that we applied or edit it according to our choice.
My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!