Contextures

Pivot Table Conditional Formatting

How to avoid 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.

pivot table with conditional formatting

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.

pivot table before conditional formatting

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.

above average window

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

pivot table with conditional formatting

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.
    • pivot table with conditional formatting
  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

pivot table with conditional formatting

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. 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
    • pivot table with conditional formatting
  4. Click Edit Rule, to open the Edit Formatting Rule window.
  5. In the Apply Rule To section, there are 3 options, and the Selected cells option is selected.
  6. Click on the 3rd option -- All cells showing "Sum of Sales" values for "Date" and "Territory"
    • pivot table with conditional formatting
  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.

pivot table with conditional formatting

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

year month with sales values

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 Ribbon's Home tab, click Conditional Formatting
  3. Click Data Bars, and click one of the Data Bar styles
    • I selected Solid Fill - Orange

select a data bar style

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

data bars in sales value cells

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.

no data bars in July sales value cell

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.

data bars in July sales value cell

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"

2 copies of Sales field in values area

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.

2 copies of Sales field in values area

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.

data bars and numbers in separate columns

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.

Download 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

Conditional Formatting Examples

Conditional Formatting Data Bars

 


Last updated: February 23, 2021 2:42 PM