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.

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.

To apply conditional formatting:

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

Click OK, to close the window.

The cells with above average values are highlighted.

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.

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

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.

Change the Formatting Range

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

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, find the Above Average rule, which shows
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.

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.

Click on the 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.