Excel Pivot Table Formatting Guide (2026)
Home›Excel›Pivot Tables›Formatting
ExcelSub-Guide⏱ 12 min read

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.

Tabular form + repeat item labels

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

  1. Select a value in the pivot

    Just one cell to start.

  2. Home tab → Conditional Formatting → your rule type

    Data Bars, Color Scales, Icon Sets, or a custom rule.

  3. 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.

  4. 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
The presentation-ready pivot

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.

Excel Wizard

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.