Pivot Table Formatting: The Complete Guide
Default pivot tables look like default pivot tables. This guide covers report layouts, subtotals control, number formatting that survives refresh, conditional formatting, custom styles, and the settings that turn a working pivot into a presentation-ready one.
Report layouts
Design tab → Report Layout offers three fundamentally different layouts:
Compact form (default)
All row fields collapsed into one column with indentation. Compact but hard to read for wide reports or when exporting.
Use when: screen space is tight, one or two row fields, mostly exploratory.
Outline form
Each row field gets its own column. Subtotals appear at the top of each group.
Use when: multiple row fields, cleaner hierarchy visibility, printed reports.
Tabular form
Each row field gets its own column. Subtotals appear at the bottom of each group. Most database-like.
Use when: exporting to another tool, database-style reports, when downstream systems expect flat tabular data.
Design tab → Report Layout → Repeat All Item Labels. Now every row has all group labels filled in — no more blanks in outer fields. Essential when exporting to another system.
Subtotals control
Design tab → Subtotals. Three options:
- Do Not Show Subtotals — cleaner look, especially for tabular exports
- Show all Subtotals at Bottom of Group — traditional accounting style
- Show all Subtotals at Top of Group — quicker scan when you care about totals
Per-field subtotal control
Right-click any row field label → Field Settings → Subtotals tab. Turn off subtotals for specific fields while keeping others.
Grand totals
Design tab → Grand Totals:
- Off for Rows and Columns — no totals at all
- On for Rows and Columns — both a bottom row and rightmost column
- On for Rows Only — grand total row at bottom only
- On for Columns Only — grand total column on right only
Number formatting
Format numbers the right way
Right-click any value cell → Number Format (NOT Format Cells). Number Format is pivot-specific — persists through refresh. Format Cells sets a one-time cell format that Excel resets on refresh.
Custom formats worth memorizing
$#,##0— currency, no decimals, thousands separator$#,##0.00— currency with cents#,##0.0,,"M"— millions with one decimal (12,345,678 → 12.3M)#,##0,"K"— thousands (12,345 → 12K)0.0%— percentage with one decimal[Red]-#,##0;#,##0— red for negatives▲ 0.0%;▼ 0.0%;0.0%— arrows for positive/negative variance
Preserve formatting on refresh
PivotTable Analyze → Options → Layout & Format tab:
- Tick "Preserve cell formatting on update" — column widths, number formats stay
- Untick "Autofit column widths on update" — prevents Excel from resizing on refresh
Both together = your formatting survives refresh. Without them, dashboards break every time.
Conditional formatting on pivots
Conditional formatting works on pivot tables but takes care to apply correctly.
Applying data bars, color scales, or icon sets
Select a value in the pivot
Just one cell to start.
Home tab → Conditional Formatting → your rule type
Data Bars, Color Scales, Icon Sets, or a custom rule.
Small dropdown appears
Excel asks how to apply: to just this cell, to all cells showing the value, or to all cells showing the value for the specific row/column combination.
Pick the third option
"All cells showing values for [Field] and [Field]" is what you almost always want.
Heatmap-style pivot
Select values → Conditional Formatting → Color Scales → Red-Yellow-Green (or Green-Yellow-Red). Instant heatmap. Works especially well for cohort analysis and matrix-style pivots.
Highlighting top values
Select values → Conditional Formatting → Top/Bottom Rules → Top 10 Items. Configurable — change to Top 5, or use Above Average.
PivotTable Styles
Design tab → PivotTable Styles gallery. Preset color schemes range from minimal (light) to bold (dark).
Style options
Design tab → PivotTable Style Options section. Toggles:
- Row Headers — bolds/highlights row header labels
- Column Headers — same for columns
- Banded Rows — alternating row colors (zebra stripes)
- Banded Columns — same for columns
Custom pivot styles
Right-click a style in the gallery → Duplicate → modify individual elements (header, subtotal, grand total). Save your custom style; apply it to future pivots.
Hiding and showing details
Collapse and expand groups
Click the +/- buttons next to row labels to expand/collapse groups. Or right-click a group → Expand/Collapse → Collapse Entire Field.
Hide +/- buttons for cleaner look
PivotTable Analyze → +/- Buttons toggle. For read-only reports, hide them. For interactive dashboards, keep them.
Hide field headers
PivotTable Analyze → Field Headers toggle. Removes the "Row Labels" and "Column Labels" text — cleaner for print/export.
Blank cells and errors
PivotTable Analyze → Options → Layout & Format tab:
- For empty cells, show: — enter "0", "-", or "n/a" to replace blanks
- For error values, show: — replace #DIV/0! and similar with a specific string
Design tab: Report Layout → Tabular, Grand Totals → Rows Only, PivotTable Style → any light preset with Banded Rows on. Options: Preserve formatting on, Autofit width off. Number Format on values. Conditional formatting on the main metric. Done — a pivot that survives refresh and looks intentional.
Formatting the way you want, remembered
Formatting a pivot table involves half a dozen settings across two dialogs, then applying number formats and conditional rules. Excel Wizard can apply a saved formatting profile to any pivot ("format this pivot in our standard dashboard style") — replicating your defaults in one prompt.
Install Excel Wizard →Frequently asked questions
Why does my pivot table lose formatting on refresh?
PivotTable Analyze → Options → Layout & Format tab → tick 'Preserve cell formatting on update' and untick 'Autofit column widths on update'. Both are needed.
How do I change number format for the whole pivot?
Right-click any value cell → Number Format (NOT Format Cells). Set the format. It applies to the entire value field and persists through refresh. Format Cells only sets a one-time format that Excel resets.
How do I add data bars to a pivot table?
Select a value cell → Home → Conditional Formatting → Data Bars. In the small popup, choose 'All cells showing values for [Field] and [Field]' — this applies the bars across the pivot correctly, not just to selected cells.
Can I use my own colors for pivot styling?
Yes. Design tab → right-click any style → Duplicate → modify each element (header, first row, subtotal, grand total) with your colors. Save; apply to future pivots.