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:

1. First, follow the steps below, to apply the simple formatting
2. 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:

1. In the pivot table, select the territory sales amounts, in cells B5:C16
2. On the Ribbon's Home tab, click Conditional Formatting
3. Click Top/Bottom Rules, and click Above Average
4. In the Above Average window, select one of the formatting options from the drop down list.
• I chose Green Fill with Dark Green Text
5. 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.

1. In the source data, add a couple of new records, for the next month's sales.
• 
2. Go back to the pivot table sheet
3. 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.

1. Select any cell in the pivot table
2. On the Ribbon's Home tab, click Conditional Formatting, then click Manage Rules
3. The Conditional Formatting Rules Manager opens, where you can create a new rule, edit an existing rule, or delete a rule
4. 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
• 
5. Click Edit Rule, to open the Edit Formatting Rule window.
6. In the Apply Rule To section, there are 3 options, and the Selected cells option is selected.
• NOTE: See details on all 3 options below
7. Click on the 3rd option -- All cells showing "Sum of Sales" values for "Date" and "Territory"

• 
1. 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:

1. In the pivot table, select the monthly sales amounts, in cells B3:B8 (don't select the Total)
2. On the Home tab of the Ribbon, click Conditional Formatting
3. 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.

1. Select any cell in the pivot table
2. On the Ribbon's Home tab, click Conditional Formatting, then click Manage Rules
3. In the list of rules, select the Data Bar rule, which applies to cells B3:B8
4. Click Edit Rule, to open the Edit Formatting Rule window.
5. In the Apply Rule To section, select the 3rd option, All Cells Showing "Sum of Sales" Values for "YrMth"
6. 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.

1. Select any cell in the pivot table
2. On the Ribbon's Home tab, click Conditional Formatting, then click Manage Rules
3. In the list of rules, select the Data Bar rule, which applies to cells B3:B8
4. Click Edit Rule, to open the Edit Formatting Rule window.
5. In the Edit the Rule Description section, add a check mark to Show Bar Only
6. Optional - Make changes to the Data Bar colour, or other settings
7. 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