To apply conditional formatting in a pivot table, select the right settings, to keep the correct cells formatted, after the pivot table changes.
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.
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.
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:
The cells with above average values are highlighted.
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.
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.
To ensure that the correct range is formatted, you can change one of the settings.
The new cells are now formatted correctly, and the formatting range will automatically expand, if more records are added to the source data.
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.
Last updated: September 30, 2016 7:20 PM