Conditional Formatting in Excel Pivot Table (Table of Contents)
Conditional Formatting in the Pivot Table
Pivot Table is the best feature of Excel to analyze and summarize data. With the help of this, you can create reports in minimum time. You can arrange the fields as per your convenience and on demand of the management.
Conditional formatting in Excel helps you to visualize the data. We can highlight cells in a different format.
How to Apply Conditional Formatting in Excel Pivot Table?
If you want to highlight particular cell value in the report, use conditional formatting in excel pivot table. Let’s take an example to understand this process.
Example #1
We have given below data of a Retail Store (2 months data).
Follow the below steps to create a Pivot Table:
- Click on any cell in the data. Go to INSERT tab.
- Click on the Pivot Table under the Tables section and create a pivot table. Refer below screenshot.
- A dialog box appears. Click OK.
- We get the below result.
- Drag Product field to Rows section, Sales to value section whereas the Month field to Column section.
Now we have the Pivot report for month wise sale. We want to highlight the products whose sale is less than 1500.
For applying conditional formatting in this pivot table, follow the below steps:
- Select the cells range for which you want to apply conditional formatting in excel. We have selected the range B5:C14 here.
- Go to the HOME tab > Click on Conditional Formatting option under Styles > Click on Highlight Cells Rules option > Click on Less Than option.
- It will open a Less Than dialog box.
- Enter 1500 under Format Cells field and choose a color as “Yellow Fill with Dark Yellow Text”. Refer below screenshot.
- And then click on OK.
- The Pivot Report will look like this.
This will highlight all the Cell values which are less than Rs 1500.
But there is a loophole with the condition formatting here. Whenever we make any changes in the Excel Pivot data, then conditional formatting will not be applied to the correct cells and it might not include the whole new data.
The reason being is when we select a particular cell range for applying conditional formatting in excel. Here we have selected the fixed cell range B5:C14, hence when we update the Pivot table, it would not be applied to the new range.
Solution to overcome the problem:
For overcoming this problem follow below steps, after applying conditional formatting in the Excel pivot table:
- Click on any cell in the pivot table > Go to the HOME tab > Click on Conditional Formatting option under Styles option > Click on Manage Rules option.
- It will open a Rules Manager dialog box. Click on Edit Rule tab as shown in below screenshot.
- It will open the Editing Rule formatting window. Refer below screenshot.
As we can see in the above screenshot, Under Apply Rule To section, there are three options available:
- Selected Cells: This option is not applicable when you make any changes in the Pivot data like add or delete the data.
- All cells showing “Sum of Sale” values: This option might include extra fields like Grand Totals etc. fields which we might not want to include in our reports.
- All cells showing “Sum of Sale” values for “Product” and “Month”: This option restricts with the data and does formatting with cells where our required cells appear. It excludes the extra cells like Grand Totals etc. This option is the best option for formatting.
- Click on the 3rd option All Cells showing “Sum of Sale” values for “product” and “Month” as shown in below screenshot and then click on OK.
- The below screen will appear.
- Click on Apply and then click OK.
It will apply the formatting in all the cells with the updated record in the dataset.
Things to Remember
- If you want to apply new condition or change the formatting color, then you can change these options under Edit Rule Formatting window itself.
- This is the best option to present the data to the management and target the specific data which you want to highlight in your reports.
Recommended Articles
This has been a guide to Conditional Formatting in the Pivot Table. Here we discuss how to apply conditional formatting in excel pivot table along with practical examples and downloadable excel template. You can also go through our other suggested articles –
