Pivot Table Conditional Formatting

To apply conditional formatting in a pivot table, select the right settings, to keep the correct cells formatted, after the pivot table changes.

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.

If you are applying conditional formatting to a pivot table, you might need to adjust the settings, to ensure that the correct cells are formatted, after the pivot table changes.

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. In this example, the Date field is in the Rows area, Territory is in the Columns area and Sales Amount is in the Values area.

We want to highlight the 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 conditional formatting:

  1. In the pivot table, select 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.

    above average window

  5. Click OK, to close the window.

The cells with above average values are highlighted.

pivot table with conditional formatting

Problems After Updating the 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.

If you change the pivot table layout, or add new records to the source data, the rule may be applied to the wrong cells, or might not include all the new data.

Follow these steps to add new data, and see what happens to the formatted cells in the pivot table.

  1. In the source data, add a couple of new records, for the next month's sales.

    pivot table with conditional formatting

  2. Then, right-click on a pivot table cell, and click Refresh.

The new data appears in the pivot table, but it does not have the conditional formatting rule applied, because it is outside of the original block of cells.

pivot table with conditional formatting

Change the Formatting Range

To ensure that the correct range is formatted, you can change one of the settings.

  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, find the Above Average rule, which shows 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.
    • The Selected cells option works in many cases, but if you rearrange the pivot table, or add new data, the conditional formatting may not adjust correctly.
    • The second option, All Cells Showing "Sum of Sales" Values, might include too many cells, such as subtotals, and Grand Totals.
    • The third option, All Cells Showing "Sum of Sales" Values for "Date" and"Territory," is the best option for this pivot table. It restricts the formatting to cells where the Date and Territory values appear, and it excludes the subtotals and the Grand Totals.
  6. Click on the option -- All cells showing "Sum of Sales" values for "Date" and "Territory"

    pivot table with conditional formatting

  7. 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

Download the Workbook

You can download the Conditional Formatting workbook to see the pivot table used in this example.

The file is zipped, and is in xlsx file format.

More Pivot Table Resources

30 Excel Functions in 30 Days







pivot power premium




xtreme pivot tables


pivot power premium


Last updated: September 30, 2016 7:20 PM