We often want to enhance the layout and format of our pivot tables by using Conditional Formatting. This step by step tutorial will assist all levels of Excel users in working with a pivot table that has conditional formatting, and learning to solve some common problems associated with it.
Figure 1. Working with pivot table that has conditional formatting
Setting up Our Data
Figure 2. Sample data: Working with pivot table that has conditional formatting
Our table consists of four columns: Name (column B), Month (column C), Orders (column D) and Sales (column E). First, let us insert a pivot table using our data.
Insert a pivot table
In order to insert a pivot table, we follow these steps:
Step 1. Select the cells of the data we want to use for the pivot table. In this case, select cells B2:E18
Step 2. Click the Insert tab, then select PivotTable
Figure 3. Selecting the data to insert a pivot table
Step 3. In the Create PivotTable dialog box, tick Existing Worksheet. Click the Location bar and then click cell G2.
Figure 4. Inserting a pivot table in existing worksheet
Step 4. In the PivotTable Field List, click Name, Month and Total Sales
Figure 5. Creating a pivot table with chosen fields
We have now successfully created our pivot table. Now we will discuss three common problems associated with pivot table that has conditional formatting:
- Highlighting the wrong cells
- Conditional formatting not applied to new data added
- Error: Cells outside the PivotTable data region
Highlighting the wrong cells
We want to highlight the sum of sales per month, but the highlighted cells are the subtotals per name as shown below in cells H7 and H10.
Figure 6. Wrong cells are highlighted through conditional formatting
Work-around
Change the cell reference in the Apply Rule to: bar from $H$3 to $H$4 then tick All cells showing “Sum of Sales” values for “Month”.
Figure 7. Entering correct formatting range to highlight correctly
This way, the sum of values for each month that are above average are highlighted.
Conditional formatting not applied to new data added
In below table, we have added new data in row 19 for Liz in June. The pivot table has been updated but conditional formatting is not applied to the new data in G24:H24.
Figure 8. Conditional formatting not applied to newly added data
The most probable cause would be the cell reference or range inside the Apply Rule To: bar.
Figure 9. Wrong formatting range entered in formatting rule
Work-around
Change the formatting range from $H$3:$H$23 to $H$3:$H$24 to include the newly added data in conditional formatting.
Figure 10. Entering correct formatting range to include newly added data
Cells outside the PivotTable data region
Sometimes we encounter this error when applying conditional formatting to our pivot table. It says conditional formatting cannot be applied because the range has cells outside of a PivotTable data region.
Figure 11. Error in Conditional Formatting: Cells outside PivotTable data region
In cases like this, the Apply Rule To: contains an invalid range like the example below, where the range is: =$H$3+$H$10.
Figure 12. Invalid formatting range causing error
Work-around:
Make sure that the cell or range in the Apply Rule To: box is inside our pivot table. Let us correct the example and enter the following as range: =$H$3:$H$9
Figure 13. Entering a valid formatting range
The conditional formatting now works perfectly and highlights the sum of sales above average.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment