Contextures

Pivot Table Conditional Formatting

For conditional formatting in a pivot table, settings might need to be adjusted, to ensure that the correct cells are formatted, after the pivot table changes.



Apply Conditional Formatting to Pivot Table

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

Video: Apply Conditional Formatting to Pivot Table

Watch this video to see the steps for applying conditional formatting to pivot tables cells. Then you'll see how to adjust the rule, so new cells will be formatted if the pivot table layout changes.

The written instructions are below the video.

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 Sample File

You can download the Conditional Formatting sample file 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

 

 

 

 

 

 

 

 

 

 

 


Last updated: January 10, 2016 1:07 PM