Final Report
Charts and Visualization Excel Tips and Tricks

Conditional Formatting in a Pivot Table with Example

Conditional formatting is a very useful feature in Microsoft Excel. If you are working on an Excel Report or Excel Dashboard then often require conditional formatting to highlight the values as per our requirements. Today, we will talk about conditional formatting in a Pivot table.

Learn Step by Step Pivot Tables in Excel

Learn Step by Step Conditional formatting in Excel

Example 1:

To apply the conditional formatting in a pivot table, just select a cell of value and go to the Home tab >> Conditional formatting>> New Rule

Conditional formatting with a Pivot Table
Conditional formatting with a Pivot Table

 

A new formatting Rule window will be opened. There are 3 options for “Apply Rule to

Selected Cells:

If you select this option then Conditional formatting will be applied only for selected cells. This means it will not be dynamic, it will be always for the selected cells.

 

All Cells showing Sales values:

This option can be selected when you want to apply conditional formatting on all the cells wherein the Sales amount is available in an entire pivot table. It will consider the Row and Column Grand Total also for the Sale amount.

 

All Cells showing Sale values for Emp Name and Week:

You can select this option when you want to apply the conditional formatting only for Emp Name and Week for the Sale amount. t will not consider the Row and Column Grand total also for Sale amount

 

Color Scale Conditional formatting with a Pivot Table
Color Scale Conditional formatting with a Pivot Table

 

Example 2:

In example 2, we have applied conditional formatting to highlight the Top 5 and Bottom 5 Employee by Call Handled and Sales.

 

Highlight the Top 5 and Bottom 5

 

Example 3:

In this example, we have applied 2 types of conditional formatting.

First, we are showing the status for Target Vs Actual Sales with Icon Set in conditional formatting. We have created a Calculated Field first for Sale Status.

 

To create the Calculated Field, select the pivot table >> go to the Pivot Table Analyze >> Field, Items and Sets >> Calculated Field

 

Calculated Field
Calculated Field

 

After creating the Calculated field, move this to the pivot table after Target Sales and Rename with space only. Now You can apply a new rule as Icon set as given in the below image-

 

Icon Set Conditional Formatting
Icon Set Conditional Formatting

 

In this example, we have applied Data Bar Conditional formatting also for Login Hours. We are considering the Login hours Target as 9 hours per day.

 

Data Bar Conditional formatting
Data Bar Conditional formatting

 

After applying this conditional formatting, our Pivot table will look like this.

 

Final Report
Final Report

Watch the step by step video tutorial:

Click here to download the practice file.

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 12 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com