Show a Pivot Table in Tabular Form and Repeat Labels

Pivot table tabular form shows each row field in its own column, with a proper header, instead of squeezing everything into one indented "Row Labels" column. Add Repeat All Item Labels and turn off subtotals, and the pivot becomes a flat table you can read, filter, copy and use in formulas. This guide is for anyone who builds pivot tables for reports or needs to paste pivot results into another sheet or system.

Compact, outline and tabular form compared#

Every pivot table uses one of three report layouts. New pivots start in Compact form.

Here is the same pivot (sales by Region and Product) in compact form:

The products are indented under each region, and both fields share the header "Row Labels". That reads well on screen, but you can't filter by Product alone or copy it as a clean list. If you're new to pivots, the beginner's guide to pivot tables shows how to build this one.

How to show a pivot table in tabular form#

Changing the layout takes three clicks and doesn't change any numbers:

1. Click any cell inside the pivot table.
2. Go to the PivotTable Design tab (called Design under PivotTable Tools in older versions; the same tab on a Mac).
3. Click Report Layout and choose Show in Tabular Form.

The pivot now looks like this:

Region and Product each have their own column and their own header. Region is shown only once per group, and each group ends with a subtotal row.

How to repeat item labels on every row#

Repeat All Item Labels fills those blank cells under North and South, so every row is complete. Go to PivotTable Design > Report Layout > Repeat All Item Labels. It was added in Excel 2010 and works in Tabular and Outline form, not in Compact form.

To repeat labels for one field only, right-click that field in the pivot, choose Field Settings, open the Layout & Print tab and tick Repeat item labels.

Repeated labels are just a display setting. The pivot still groups the data the same way, and the labels stay repeated when you add new items or refresh.

How to remove subtotals and grand totals#

For a fully flat table, take out the total rows as well:

• Subtotals: PivotTable Design > Subtotals > Do Not Show Subtotals.
• Grand totals: PivotTable Design > Grand Totals > Off for Rows and Columns.

With repeated labels and no totals, the pivot has exactly six rows of data:

That is the shape most other tools expect: one row per record, one value per cell.

How to copy a pivot table as a flat table#

Once the pivot is in tabular form with repeated labels, you can copy it as plain values:

1. Select the whole pivot table.
2. Copy it (Windows: Ctrl+C; Mac: ⌘C).
3. Click the target cell, then use Home > Paste > Paste Values (Windows: Ctrl+Alt+V, then V; Mac: ⌘+Control+V, then choose Values).

The pasted cells are ordinary data, so normal formulas work on them. For example, if the pasted Region is in A2:A7 and Sales in C2:C7:

=SUMIF(A2:A7,"North",C2:C7)

The result is 14,100. Without repeated labels, rows 3 and 4 would have a blank Region and SUMIF would count only 8,400. If you already pasted a pivot with blanks, the guide on filling blank cells with the value above fixes it in seconds.

How to make tabular form the default layout#

If you always switch to tabular form, set it once. In Microsoft 365 on Windows, go to File > Options > Data > Edit Default Layout. Set Report Layout to Show in Tabular Form, tick Repeat All Item Labels if you want it, and choose your subtotal and grand total settings. Every new pivot table then starts with that layout. Pivots you already built keep their own layout.

Common problems with tabular form#

A few things can still surprise you after switching layouts:

1. Empty cells instead of zeros. A product with no sales in a region shows a blank. See how to show zero in empty pivot cells.
2. Items in the wrong order. Tabular form keeps the pivot's sort order. The guide to sorting a pivot table shows how to sort by value or by a custom list.
3. Column widths jump on refresh. Right-click the pivot, choose PivotTable Options, and untick Autofit column widths on update.
4. Numbers don't update. The layout survives a refresh, but the pivot doesn't refresh by itself. See how to refresh a pivot table.

Practise it#

Layouts are easy to remember once you have built a few pivots yourself. Practise pivot tables in a live spreadsheet: you place fields in rows and values and check the totals as you go. Then try the exercise on this page to see why repeated labels make pivot output usable in formulas.