Excel like a finance pro.
Pivot tables · Layout
Preserving formatting on refresh
Keeps column widths and cell formats from resetting every time you refresh.
When to use it
By default a refresh resets column widths and can drop formatting. Two options keep the layout stable.
The shape of it
- How
PivotTable Options, Layout and Format: uncheck Autofit column widths, keep Preserve cell formatting.
Worked examples
Keep widths
PivotTable Analyze, Options, Layout and Format, uncheck Autofit column widths on update → Column widths stay as you set them
No more jumping columns.
Keep formats
Keep Preserve cell formatting on update checked → Cell formats survive refresh
Stable styling.
Default
Set the default under File, Options, Data, Edit Default Layout → Every new pivot behaves this way
Set once.
Worth knowing
- Format value fields through Value Field Settings rather than cells anyway.
- Pivot styles (Design tab) are the reliable way to color a pivot.
- Conditional formatting applied to a field survives too.
Where it goes wrong
- Formats on cells outside the pivot's current shape are lost when it grows.
- Autofit off means new long labels get cut off.
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.