Excel like a finance pro.
Pivot tables · Layout
Number formatting a value field
Formats every cell of a value field at once, and the format survives refresh.
When to use it
Format a value field through its settings and every cell of that field is formatted, including new ones after a refresh. Formatting the cells directly does not survive.
The shape of it
- How
Value Field Settings, Number Format. Formatting the cells directly does not stick after a refresh.
Worked examples
Currency
Value Field Settings, Number Format, Currency, 0 decimals → All Sum of Sales cells formatted as currency
Survives refresh.
Thousands
Number Format, Custom, #,##0,"K" → Thousands shown as 12K
Compact numbers.
Percent
Number Format, Percentage → Share-of-total fields as percentages
Pairs with Show Values As.
Worth knowing
- Right-click a value cell, Number Format, is the shortcut to the same dialog.
- Format the source column too so the pivot picks up a sensible default.
- Conditional formatting on value fields also survives refresh when applied to the field.
Where it goes wrong
- Formatting selected cells with Ctrl + 1 looks right until the next refresh.
- Text fields in Values cannot be number formatted.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.