Formatting and refresh Updated November 2026

Number format in a pivot table

Right-click a value, choose Value Field Settings, click Number Format and pick Currency, Number with a thousands separator, or Percentage. Formatting applied this way belongs to the field and stays after refresh; formats painted onto cells with the Home ribbon can be lost when the pivot changes shape.

Syntax

Right-click a value > Value Field Settings > Number Format > Number, 0 decimals, Use 1000 Separator > OK

Part	What it is
`Number Format button`	Bottom left of Value Field Settings; opens the Format Cells number tab
`Custom format`	For example #,##0;(#,##0);"-" for thousands, parentheses for negatives, dash for zero
`Preserve cell formatting on update`	PivotTable Options setting that keeps direct cell formats

Examples

Sum of Revenue > Value Field Settings > Number Format > Currency, 0 decimals

Result: $12,000 instead of 12000, on every revenue cell including totals.

Sum of Revenue (% of Grand Total) > Number Format > Percentage, 1 decimal

Result: 30.0% instead of 0.30 or 30.00%.

Custom: #,##0,"K"

Result: 12,000 displays as 12K.

Common mistakes

Formatting disappears on refresh: it was applied to cells; reapply through Value Field Settings.
Only the first column is formatted: each value field has its own format; set them one by one.
Row labels show dates as 45017: the date field in Rows uses the General format; right-click, Field Settings, Number Format, Date.

Practice Number format in a pivot table

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

Revenue by regionBuild a pivot that shows total Revenue for each Region.
Share of revenueShow each Region's Revenue as a percentage of total revenue.

Questions

How do I add a thousands separator in a pivot table?

Value Field Settings, Number Format, Number, tick Use 1000 Separator, set decimals to 0.

How do I format all value fields at once?

Select the whole values area and use the Home ribbon, then keep Preserve cell formatting on update ticked. Per-field settings are safer.

Conditional formatting in a pivot tableApply color scales, data bars and highlight rules to pivot values so they stay attached to the field after a refresh. The three scope options explained.
Percent of grand totalShow each row as a share of the overall total with Show Values As, % of Grand Total. Add it next to the raw numbers, format it, compare with parent totals.
Refresh a pivot tableRefresh a pivot table after the source changes with right-click Refresh or Alt+F5, refresh every pivot with Ctrl+Alt+F5, and set the file to refresh on open.