Conditional Formatting in a Pivot Table: Bars and Scales

Part of the free Module 10: Pivot Tables · Lesson 20 of 20 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

Conditional formatting in a pivot table colours the value cells by rule: data bars for revenue, a colour scale for growth, icons for targets met or missed. The rule survives refreshes and pivots as long as you apply it to the whole field with the formatting options button (all cells showing Sum of Revenue values) rather than to a fixed cell range. This lesson covers that scoping choice, the four rule types, and the number formats that make a pivot table readable.

Table of Contents

Number formats first: format the field, not the cells

Before adding colour, fix the numbers. Formatting cells in the values area with Home > Number works until you refresh, filter or move a field; then the pivot table rebuilds and the format is lost. Format the field instead.

Click any value cell, for example a Sum of Revenue figure.
Right-click and choose Value Field Settings, or right-click and choose Number Format directly (the faster route).
Click the Number Format button, pick a category or type a custom code, and click OK twice.

The format is stored with the field. It follows the field into any area, survives every refresh and applies to the grand totals and subtotals automatically. Repeat for each value field; Sum of Sales (units) and Sum of Revenue need different formats.

Need	Custom number format code	Example result
Currency, no decimals, thousands separator	`$#,##0`	$1,234,567
Show thousands as K	`#,##0,"K"`	1,235K
Show millions as M with one decimal	`#,##0.0,,"M"`	1.2M
Percentage with one decimal	`0.0%`	12.5%
Negative in red with brackets	`#,##0;[Red](#,##0)`	(4,500)
Hide zeros	`#,##0;-#,##0;""`	blank instead of 0
Positive with plus sign, for change columns	`+#,##0;-#,##0;0`	+2,300

Percentages produced by Show Values As (for example % of Grand Total) already carry a percentage format; change the decimals through Number Format in the same dialog. See Value Field Settings for the calculation side.

How to apply conditional formatting to a pivot table

Click one value cell in the field you want to colour, for example any Sum of Revenue cell that is not a total.
Go to Home > Styles > Conditional Formatting and choose a rule, for example Data Bars > Gradient Fill > Blue Data Bar.
A small Formatting Options button appears next to the cell. Click it and choose All cells showing “Sum of Revenue” values for “Product” (the third option) or, for totals as well, All cells showing “Sum of Revenue” values.
Refresh, sort or filter the pivot table. The colour follows the data.

The scoping step is the whole lesson. The three choices in the Formatting Options button, also available in Manage Rules > Edit Rule, decide whether the rule survives a change to the layout.

Scope option	Applies to	Survives refresh and re-pivot?	Use when
Selected cells	The fixed range you selected	No; it stays on the cells even when other data lands there	Never, on a pivot table
All cells showing “Sum of Revenue” values	Every cell of that value field, including subtotals and grand totals	Yes	You want totals coloured too (rarely; totals distort colour scales)
All cells showing “Sum of Revenue” values for “Product”	Detail cells of that field at that row level, no totals	Yes	Almost always; totals are excluded so the scale is fair

The four rule types that suit pivot tables

Data Bars: a bar inside each cell in proportion to its value. Best for a single value column such as Revenue by Product. Use More Rules and tick Show Bar Only to turn a column into a mini chart. Details in Data Bars.
Colour Scales: a two- or three-colour heat map. Best for a cross-tab, for example Product by Month, where the eye finds the hot and cold cells at once. See Colour Scales.
Icon Sets: arrows, traffic lights or flags. Best for a difference or % change column produced with Show Values As, where up, flat and down carry meaning. See Icon Sets.
Top/Bottom Rules: highlight the top 5 items or values above average. Combined with the “for Product” scope they rank inside the pivot table without a Top 10 filter.

Highlight Cells Rules (greater than, between, text contains) also work and are ideal for a target line: colour every Revenue cell above 50,000 green. For anything the built-in rules cannot express, use a formula rule, covered in Formula-based Conditional Formatting, but note the limitation in the next section.

Formatting the row labels and the whole row

The Formatting Options button only appears on value cells. To colour a row label, or a whole row depending on its value, you need a formula rule applied to a range that comfortably covers the pivot table, for example $A$4:$F$200.

Select the range from the first data row of the pivot table down past its last possible row.
Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter a formula that refers to the value column with a mixed reference, for example =$F4>50000 to colour every cell of a row whose Revenue in column F exceeds 50,000.
Set the fill and click OK.

Because this rule is range-based, it does not move with the field. If you add a column field the values shift right and the rule points at the wrong column. Keep formula rules for reports whose layout is fixed, and add the row filter =AND($F4>50000,$A4<>"Grand Total") to keep totals uncoloured.

Worked example: heat map of revenue by product and month

Open the practice file Pivot-Table.xlsx and build a pivot table with Product in Rows, Date in Columns grouped by Months, and Revenue in Values. Set the field number format to #,##0. The grid looks like this (figures rounded):

Product	Jan	Feb	Mar	Grand Total
Product A	42,000	38,500	51,200	131,700
Product B	27,300	29,900	24,100	81,300
Product C	55,800	61,200	58,400	175,400
Grand Total	125,100	129,600	133,700	388,400

Click the Product A January cell (42,000).
Choose Home > Conditional Formatting > Colour Scales > Green – Yellow – Red.
Click the Formatting Options button and pick All cells showing “Sum of Revenue” values for “Product” and “Date”.

Result: the nine detail cells are shaded from green (61,200) to red (24,100); the Grand Total row and column stay white, so the scale is not stretched by the totals. Drag Supervisor Name above Product in Rows and the colour scale re-applies to the new detail cells without any edit. Add a second rule, Top/Bottom Rules > Top 10 Items set to 3, and the three best cells also get a bold border.

Errors and how to fix them

Problem	Cause	Fix
Colour disappears after refresh	Rule was applied to Selected cells or by Format Painter	Delete the rule and re-apply with the Formatting Options button scope
Grand Total is always the darkest cell	Rule scope includes totals	Choose the scope that ends with for “Product” (detail cells only)
Number format lost after refresh	Cells formatted with Home > Number instead of the field	Right-click a value > Number Format, or tick Preserve cell formatting on update in PivotTable Options
Rule shows in Manage Rules but nothing is coloured	The rule applies to a value field that is no longer in the report	Delete the stale rule; add a new one on the current field
Icons appear on the labels	Rule range covers columns A and B	Edit the rule and restrict it to the value field scope
Formula rule colours the wrong column	Layout changed and shifted the values	Edit the formula reference, or switch to a field-scoped rule

Tips and common mistakes

One rule per value field. Data bars on Revenue and a colour scale on Sales in the same report is fine; two colour rules on the same field is unreadable.
Exclude totals from colour scales. The total is always the extreme and turns everything else the same shade.
Prefer field scope over ranges. A rule scoped with the Formatting Options button is the only kind that survives re-pivoting.
Do not use Format Painter on a pivot table; it copies a cell-range rule that will drift.
Use Show Bar Only for dashboards. A data-bar column with hidden numbers behaves like an in-cell bar chart and needs no chart object.
Set Preserve cell formatting on update in PivotTable Options (it is on by default) and turn off Autofit column widths so bars keep their length after refresh.
Check Manage Rules after big layout changes. Choose This PivotTable in the Show formatting rules for box to see every rule attached to the report.

Practice exercise

Build Revenue by Product with the field number format #,##0,"K" and add blue data bars scoped to the detail cells only.
Add Date grouped by Months to Columns and apply a Green – Yellow – Red colour scale to the cross-tab. Confirm the Grand Totals stay uncoloured.
Add Revenue to Values a second time, set it to Show Values As > Difference From > Date, previous, and apply a three-arrow icon set.
Write a formula rule that colours the whole row of any product whose grand total exceeds 150,000, excluding the Grand Total row.
Refresh, then swap Product and Supervisor Name in Rows, and note which of your four rules survived.

Key takeaways

Set number formats through Value Field Settings or the right-click Number Format command so they survive refreshes.
Apply conditional formatting to one value cell, then use the Formatting Options button to scope the rule to the field.
Choose the scope that ends with the row field name to keep subtotals and grand totals out of the colour scale.
Data bars suit a single column, colour scales suit a cross-tab, icon sets suit change columns.
Formula rules on a range work for row labels but do not move with the layout; keep them for fixed reports.
Manage Rules with This PivotTable selected shows every rule attached to the report.

Related lessons

Frequently asked questions

Why does conditional formatting disappear from my pivot table after refresh?

The rule was applied to a fixed cell range, usually by selecting cells first or by using Format Painter. When the pivot table rebuilds, the values move and the rule stays behind. Delete the rule, click one value cell, apply the format again and use the Formatting Options button to scope it to all cells showing that value field.

How do I apply conditional formatting to a pivot table without the grand total?

After applying the rule to one value cell, click the Formatting Options button and choose the third option, All cells showing values for the row field, for example for Product. That scope covers the detail cells only. The second option, All cells showing values, includes subtotals and grand totals.

How do I change the number format of a pivot table permanently?

Right-click any cell of the value field and choose Number Format, or open Value Field Settings and click Number Format. Pick the format and click OK. The format is stored with the field, so it survives refreshes and applies to subtotals and grand totals as well as when the field moves.

Can I use a formula in conditional formatting for a pivot table?

Yes, through New Rule and Use a formula to determine which cells to format on a range that covers the report. It is the only way to colour row labels or whole rows. The rule is range-based, so it does not follow the field when the layout changes; keep it for reports with a stable layout.

Can I add data bars to a pivot table?

Yes. Click one value cell, choose Home, Conditional Formatting, Data Bars, and pick a style. Then click the Formatting Options button and scope the rule to all cells showing that value field for the row field. Open More Rules and tick Show Bar Only if you want the bars without numbers.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.