Layout and values Updated November 2026

Tabular layout

Click in the pivot, open Design, Report Layout and choose Show in Tabular Form. Each row field then has its own column with a proper header, which is what most people want when the pivot feeds other formulas or will be copied as values.

Syntax

Design > Report Layout > Show in Compact Form | Show in Outline Form | Show in Tabular Form

Part	What it is
`Compact`	Default; all row fields share one column with indentation and the header says Row Labels
`Outline`	One column per field, outer label on its own row above the inner items
`Tabular`	One column per field, outer label on the same row as the first inner item, subtotals at the bottom

Examples

Rows: Region, Rep. Values: Sum of Revenue
Design > Report Layout > Show in Tabular Form

Result: Columns Region, Rep, Sum of Revenue with real field names as headers; East appears on the first rep row with East Total under its reps.

Tabular Form + Design > Report Layout > Repeat All Item Labels

Result: Region is filled on every row, so the block can be copied and used like a database table.

File > Options > Data > Edit Default Layout > Report Layout: Show in Tabular Form

Result: Every new pivot opens in tabular layout (Microsoft 365 and Excel 2019).

Common mistakes

The headers still say Row Labels: you are in Compact form; switch to Tabular or Outline to see field names.
Subtotals appear at the top in Outline but at the bottom in Tabular: that is normal; choose the layout you prefer.
The layout resets on a new pivot: set it once in Edit Default Layout.

Practice Tabular layout

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

Region, then repShow total Revenue by Region, and within each region by Rep.
Pay by department and levelShow the average Salary by Department, and within each department by Level.

Questions

What is the difference between compact and tabular form?

Compact squeezes all row fields into one column with indents. Tabular gives each field its own column and places subtotals below each group, like a classic report.

How do I make tabular the default pivot table layout?

In Microsoft 365: File, Options, Data, Edit Default Layout, then pick Show in Tabular Form.

Repeat row labelsFill the blank cells under an outer row label so every row shows its region: Design, Report Layout, Repeat All Item Labels, plus the per-field option.
Two row fieldsPut two or more fields in Rows to nest categories, choose which field is outer and inner, collapse groups and switch to tabular layout for separate columns.
Remove grand totals and subtotalsTurn grand totals off for rows, columns or both and remove subtotals from nested fields on the Design tab. How to bring them back and show subtotals on top.
Copy a pivot table as valuesTurn a pivot table into plain cells with Copy, Paste Special, Values and Number Formats, keep the look with Keep Source Formatting, set tabular layout first.