Home > Pivot > Format > Conditional
Pivot Table Conditional Formatting
How to avoid Excel pivot table conditional formatting problems. Short video shows easy setup steps. Get the free workbook, and follow along. See how to fix conditional formatting problems after a pivot table refresh

|
Conditional Formatting in Pivot Table
Watch this video to see the steps for applying conditional formatting
to pivot tables cells. Then adjust the rule, so
new cells are formatted if the pivot table layout changes. The written instructions are below.
Pivot Table Conditional Formatting
In Excel, you can use conditional
formatting to highlight cells, based on a set of rules. For example,
highlight the cells that are above average, or lower than a specific
amount.
After applying conditional formatting to a pivot table, adjust the settings, to make sure that the correct cells
are formatted, after the pivot table is refreshed.

Apply Formatting to Pivot Table Cells
In a pivot table with a simple layout, you can select a group of
cells, and apply a conditional formatting rule, just like you would for any cells on a worksheet.
However, unless you do an extra step, you might see conditional formatting problems later, after you refresh the pivot table.
To avoid pivot table conditional formatting:
- First, follow the steps below, to apply the simple formatting
- Next, go to the Fix Conditional Formatting section, to see how to avoid problems
Apply Conditional Formatting to Pivot Table Cells
In this example:
- Date is in the Rows area
- Territory is in the Columns area
- Sales Amount is in the Values area.
We want to highlight sales amounts that are above average. The
Grand Total amounts won't be included, because they would skew the
average.

To apply simple conditional formatting:
- In the pivot table, select the territory sales amounts, in cells B5:C16
- On the Ribbon's Home tab, click Conditional Formatting
- Click Top/Bottom Rules, and click Above Average
- In the Above Average window, select one of the formatting options
from the drop down list.
- I chose Green Fill with Dark Green Text
- Click OK, to close the window.

The cells with above average values are highlighted, with the formatting style that you selected..

Problems After Refreshing Pivot Table
When you apply conditional formatting to a block of cells in the
pivot table, the formatting rule is applied to those cells only.
Later, if you change the pivot table layout, or add new records to the source
data, the rule:
- could be applied to the wrong data
- might not include
all the new data
To avoid those problems:
- Go to the Fix Conditional Formatting section below
- Follow the steps listed there, to make a small change to the conditional formatting rule.
What Happens If You Don't Change the Rule?
To see an example of this pivot table conditional formatting problem, try these steps --before you go to the next section.
- In the source data, add a couple of new records, for the next
month's sales.
- Go back to the pivot table sheet
- Right-click on any pivot table cell, and click Refresh.
In the screen shot below, you can see the conditional formatting problem.
- The new data appears in the pivot table, in row 17
- The new data cells do not have the
conditional formatting rule applied
Why Does This Happen?
- The conditional formatting rule was applied to specific cells only -- down to row 16
- The new data is in row 17, outside of the
original block of cells
To fix the problem, continue to the Fix Conditional Formatting section below

Fix Conditional Formatting Problem
After you apply conditional formatting to pivot table cells, follow these steps to change one
of the conditional formatting settings.
This step will prevent conditional formatting problems after you refresh the pivot table.
- Select any cell in the pivot table
- On the Ribbon's Home tab, click Conditional Formatting, then click
Manage Rules
- The Conditional Formatting Rules Manager opens, where you can create a new rule, edit an existing rule, or delete a rule
- In the list of rules, select the rule that you created -- the Above Average rule in this example
- In the list, you can see
the range of cells in the pivot table

- Click Edit Rule, to open the Edit Formatting Rule window.
- In the Apply Rule To section, there are 3 options, and the Selected
cells option is selected.
- Click on the 3rd option -- All cells showing "Sum of Sales"
values for "Date" and "Territory"
- Click OK twice, to close the windows.
The new cells are now formatted correctly, and the formatting range
will automatically expand, if more records are added to the source
data.

Apply Rule To Options
For pivot table cells, are 3 options for where the conditional formatting rule should be applied. Usually option 3 is the best choice.
1) Selected Cells
- This option might work in a simple pivot table, if it won't change size or layout
- If you
rearrange the pivot table, or add new data, the conditional
formatting might not adjust correctly.
2) All Cells Showing "Sum of Sales" Values
- This option would include subtotals and Grand Totals.
- That could cause problems if the conditional formatting is a colour scale, based on the numbers
2) All Cells Showing "Sum of Sales" Values
for "Date" and"Territory"
- This is the best option in most cases
- This option restricts formatting to cells where Date and
Territory values appear, and excludes subtotals and
Grand Totals.
Pivot Table Data Bars
For pivot tables with a single column of values, conditional formatting data bars can be an effective way to show the results.
This short video shows the steps for setting up a pivot table with data bars, and there are written steps below the video.
Pivot Table with Sales Data
For this example, there is a pivot table with:
- YrMth field in Rows area, filtered to show January to June
- Sales in Values area, formatted as number, with zero decimal places, and a thousand separator

Add Data Bars for Sales
To show data bars for the sales amounts, follow these steps:
- In the pivot table, select the monthly sales amounts, in cells B3:B8 (don't select the Total)
- On the Home tab of the Ribbon, click Conditional Formatting
- Click Data Bars, and click one of the Data Bar styles
- I selected Solid Fill - Orange

The Data Bars appear in the January to June Sales value cells, along with the number

Adjust Conditional Formatting Range
The January to June sales cells have data bars, but if you change the filter, to also show July, its Sales cell does not have a data bar.

We'll adjust the formatting range, to fix that problem.
- Select any cell in the pivot table
- On the Ribbon's Home tab, click Conditional Formatting, then click
Manage Rules
- In the list of rules, select the Data Bar rule, which applies to cells B3:B8
- Click Edit Rule, to open the Edit Formatting Rule window.
- In the Apply Rule To section, select the 3rd option, All Cells Showing "Sum of Sales" Values
for "YrMth"
- Click OK twice, to close the windows.
The July Sales cell now has a Data Bar, and the formatting range
will automatically expand, if more months are shown in the pivot table.

Show Numbers and Data Bars Separately
Data bars give a good picture of the data, but they can make the numbers difficult to read. To avoid that problem, put the numbers and data bars in separate columns.
Add Another Sales Field
- First, in the PivotTable Fields list, drag another copy of the Sales field to the pivot table Values area
- Next, format those values as Number, with zero decimal places, and a thousand separator
- Then, change the Value headings to "Sales " (with a space at the end), and "Sales Amt"

Format the Data Bars
Next, follow these steps to format the data bars, so they don't show numbers.
- Select any cell in the pivot table
- On the Ribbon's Home tab, click Conditional Formatting, then click
Manage Rules
- In the list of rules, select the Data Bar rule, which applies to cells B3:B8
- Click Edit Rule, to open the Edit Formatting Rule window.
- In the Edit the Rule Description section, add a check mark to Show Bar Only
- Optional - Make changes to the Data Bar colour, or other settings
- Click OK twice, to close the windows.

The Sales column now has a Data Bar only, and the Sales Amt column shows the number only.
Note: You can make the Data Bar column wider, if necessary, to make it easier to see small differences in the bar length.

More About Data Bars
To set up the data bars, you'll use conditional formatting. Before we set up data bars in a pivot table, you can watch this short video to see how to set up data bars on a worksheet.
Get the Workbook
To see the pivot tables shown on this page, download the
Conditional Formatting workbook. The zipped Excel file is in xlsx file format, and does not contain any macros..
More Tutorials
Pivot Table Conditional Formatting - Weekends
Conditional Formatting Examples
Conditional Formatting Data Bars