Pivot Table Conditional Formatting in Excel
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.
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.
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.
- 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 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.
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.
4. Icon sets: In this type of formatting, we use directional arrows, shapes, and various indicators to visualize our data.
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.