Formatting and refresh Updated November 2026

Conditional formatting in a pivot table

Select a value cell, open Home, Conditional Formatting, pick a rule such as Color Scales, then click the small formatting options icon that appears and choose All cells showing Sum of Revenue values for Region. That scope keeps the rule attached to the field so it survives sorting, filtering and refresh.

Syntax

Home > Conditional Formatting > rule > Formatting Options icon > All cells showing "Sum of Revenue" values for "Region"

Part	What it is
`Selected cells`	Only the cells you highlighted; breaks when the pivot grows
`All cells showing Sum of X values`	Every cell of that value field, including totals
`All cells showing Sum of X values for Region`	The field's cells at that row level, excluding subtotals and grand totals; usually the right choice

Examples

Rows: Region. Values: Sum of Revenue
Color Scales > Green-Yellow-Red > scope: values for Region

Result: Regions shaded from green (highest) to red (lowest) without coloring the Grand Total.

Data Bars > Blue > scope: values for Product

Result: In-cell bars proportional to each product's total.

Highlight Cells Rules > Less Than > 0 > red text, scope: Difference From values for Months

Result: Negative month-on-month changes stand out in red.

Common mistakes

The Grand Total is dark green and flattens the scale: the scope includes totals; pick the for Region option.
Formatting vanished after adding a field: the rule used Selected cells; recreate it with a field scope.
Rules pile up: open Manage Rules, This PivotTable, and delete the old ones.

Practice Conditional formatting in a pivot table

These drills check your answer on live data, free, no account needed.

Best region firstShow total Revenue by Region, sorted so the region with the highest revenue is at the top.
Year over year by departmentShow total Amount with Department down the side and each year across the top.

Questions

Why does conditional formatting disappear from my pivot table?

The rule was applied to a fixed cell range. When the pivot resizes, those cells no longer line up. Use the All cells showing values option.

Can I use a formula rule in a pivot table?

Yes, with Use a formula to determine which cells to format, but formula rules can only use the Selected cells scope, so they are less robust.

Number format in a pivot tableFormat pivot table values so the format survives a refresh: use Value Field Settings, Number Format rather than the Home ribbon. Currency and percent.
Top 10 filterUse Value Filters, Top 10 to keep only the top 5, the bottom 3 or the items that make up 80 percent of a total. Steps, the Items, Percent and Sum modes.
Difference from previousCompare periods inside a pivot table with Show Values As, Difference From and % Difference From. Pick the base field and (previous); why period one is blank.