Conditional Formatting in a Pivot Table That Survives Refresh

Conditional formatting in a pivot table survives a refresh when the rule is tied to the value field, not to a block of fixed cells. Excel asks how to scope the rule each time you add one, and the choice decides whether new rows get coloured and whether the grand total spoils the result. This guide is for anyone who builds pivot reports that are refreshed every week and wants the highlights to keep working.

Why does pivot table conditional formatting break after a refresh?#

It breaks because the rule points at fixed cells while the pivot keeps changing shape. A normal conditional formatting rule applies to a range such as B5:B9. A pivot table can grow, shrink or move its values every time you refresh, filter or drag a field.

Say a rule covers B5:B9 for five regions. Next month a sixth region appears and the pivot fills B5:B10. B10 has no rule, so it stays plain. If you move Region to Columns, the values move to a row and the old range now highlights the wrong cells, or empty ones.

Pivot tables solve this with field-based scopes. A rule scoped to "Sum of Sales" follows those values wherever they appear. If refreshing itself is the problem, see why a pivot table doesn't update and how to refresh it.

How to apply conditional formatting to a pivot table#

Apply the rule as usual, then change its scope:

1. Click one value cell in the pivot, for example the Sum of Sales for Central.
2. Go to Home > Conditional Formatting and pick a rule, such as Top/Bottom Rules > Above Average or Data Bars.
3. A small Formatting Options button appears next to the cell. Click it.
4. Choose All cells showing "Sum of Sales" values for "Region".

The rule now covers every region value, today and after any refresh. If you create the rule from Conditional Formatting > New Rule instead, the same three choices appear at the top of the dialog under Apply Rule To. The basics of each rule type are in the conditional formatting guide.

The three "Apply rule to" options explained#

Excel gives you three scopes. The names use your own field names, so they change with your data:

The third option is the right default for most reports. Use the second only when you really want totals compared with items, which is rare.

If your pivot has two row fields, such as Region and Month, the third option is tied to the level of the cell you clicked. Click a month value first and the rule covers the month rows, not the region subtotals.

Why you should leave out the grand total#

Rules such as Above Average, Top 10 and colour scales compare every cell in their range, and a grand total is far bigger than any single item. Here is a pivot of sales by region:

With the third option, the average of the five regions is 20,600, so Above Average highlights two regions: East and South. You can check that count with a formula on the values:

=COUNTIF(B2:B6,">"&AVERAGE(B2:B6))

The result is 2. With the second option, the grand total joins the range. The average of all six cells becomes 34,333, and the only cell above it is the grand total itself. The rule "works", but it tells you nothing. The same happens with a colour scale: the total takes the darkest colour and every region looks pale.

How to fix a rule that stopped working#

You can rescope an existing rule without starting again:

1. Click any cell inside the pivot.
2. Go to Home > Conditional Formatting > Manage Rules.
3. In Show formatting rules for, choose This PivotTable.
4. Select the rule and click Edit Rule.
5. Under Apply Rule To, pick the field-based option and click OK.

While you are in Manage Rules, delete duplicates. Applying a rule to "Selected cells" several times over the months often leaves a stack of near-identical rules. The rules list also shows the order Excel applies them in, which matters when two rules colour the same cell.

Which conditional formatting rules work best in a pivot?#

Most rule types work in a pivot. These are the ones that read best in a report:

• Data bars show size at a glance. Leave totals out or every bar looks short.
• Top 10 Items or Top 10% pick out the leaders. You can change 10 to 3 for a top-three highlight.
• Above or below average split items into two groups.
• Greater than a value flags anything over a target, such as sales above 20,000.
• Icon sets work well with a "% of total" value field, where every item is on the same scale. The guide to showing percent of total in a pivot table sets that up.

Formula rules work too. Choose Use a formula to determine which cells to format, write the formula for the cell you clicked, such as =B5>20000, and then pick the field-based scope. Rules can also target a calculated field; the guide to pivot table calculated fields shows how to add one.

Common problems with pivot conditional formatting#

If the colours still look wrong, check these:

1. The value field was removed and added back. Removing a field from the pivot removes its rules. Add the rule again after the layout is final.
2. The rule colours dates or labels. Rules on row labels need the "Selected cells" scope or a formula rule. For grouped dates, see how to group dates in a pivot table.
3. The rule depends on a filter. A Top 3 rule ranks only the items currently visible, so filtering the pivot changes which items are highlighted. That is usually what you want.
4. Manual colours fight the rule. "Preserve cell formatting on update" in PivotTable Options keeps manual fills, not conditional formatting. Clear manual fills so the rule is the only source of colour.

Practise it#

The skills behind this are conditional formatting rules and confident pivot work. Practise conditional formatting in a live spreadsheet, then build and rearrange summaries in the pivot tables lesson. Each lesson checks your work instantly. The exercise on this page shows how to confirm what an Above Average rule will highlight.