Excel Tabular Form: Flatten a PivotTable for Sharing

To put each PivotTable row field in its own column, click inside the PivotTable and choose Design → Report Layout → Show in Tabular Form. Before sharing the result as a dataset, also repeat the labels and remove subtotal and grand-total rows.

The target is a summary where every row can stand alone: its category labels are filled in, its measure is clearly named, and sorting will not separate values from their meaning.

Switch the PivotTable to tabular form

In desktop Excel:

1. Click any cell inside the PivotTable. The PivotTable Analyze and Design tabs appear.
2. Open Design → Report Layout.
3. Select Show in Tabular Form.

Microsoft describes tabular form as one column per field, with room for field headers. Unlike compact form, which combines multiple row fields in one indented column, it gives fields such as Region and Product separate columns. Microsoft’s layout guide documents this route.

This changes the report’s layout, not the underlying level of detail. A PivotTable summarizing sales by region and product still contains region–product summaries, not individual transactions.

For the simple extract below, put Region and Product in Rows, Units sold in Values summarized by Sum, and leave Columns empty. If categories currently run across columns, move those fields to Rows for this layout. Tabular form alone does not turn a cross-tab into one record per category combination. Microsoft’s guide also explains how to rearrange fields.

If you meant organizing an ordinary worksheet into rows and columns rather than changing a PivotTable, use the Excel tabular-data preparation guide.

Repeat labels and remove report-only rows

Tabular form alone can leave blank category cells below the first item in a group. Choose Design → Report Layout → Repeat All Item Labels so each row carries its own labels.

For a single field, right-click its label, choose Field Settings → Layout & Print, and select Repeat item labels, with Show item labels in tabular form selected. These controls are covered by Microsoft Support and the Microsoft Press layout walkthrough.

For an export containing only the summary records, use these additional controls on Design:

• Subtotals → Do Not Show Subtotals
• Grand Totals → Off for Rows and Columns
• Blank Rows → Remove Blank Line after Each Item Label

The Microsoft Press walkthrough recommends removing subtotals and grand totals when flattening a PivotTable for subsequent analysis; Microsoft Support documents the blank-row control.

Keep a separate total for validation rather than mixing it into the published records. Otherwise, a reader summing additive measures such as units sold could count both the component rows and their totals.

Expected result

This fictional example represents three region–product summaries:

Region	Product	Units sold
North	Notebook	120
North	Pen	80
South	Notebook	150

North appears twice deliberately. If the rows are sorted by units sold, each value still has its region and product attached. The three numeric values sum to 350; a fourth “Grand Total” row does not belong in this extract.

Create and check a publication copy

Refresh the working PivotTable if its source has changed, and expand any collapsed groups you intend to include. Microsoft documents refreshing the PivotTable, and Microsoft Press explains expanding collapsed fields.

Copy the header row and summary records into a new workbook, using Home → Paste → Values. This creates a static copy rather than a working PivotTable. Microsoft documents that the Values option pastes results without formatting or comments. Reapply appropriate date and number formats in the copy, since Values does not carry them over. See the paste options.

Before saving:

• Use one header row with clear names and units.
• Check that every row has the required category labels. Investigate blanks from the source rather than automatically replacing them.
• For additive measures such as units sold, compare the extract’s sum with the working PivotTable’s total, using the same filters. Do not use this sum check for averages, percentages or distinct counts; check those against the corresponding group results instead.
• Check dates, identifiers and number formats in the copy.
• Document the source, reporting period, filters and aggregation—for example, “sum of units sold by region and product.”

For CSV, follow the export-and-check workflow, then inspect the saved file before publishing.

TablePage accepts CSV, TSV, XLSX and XLS uploads and generates a public dataset page with a shareable link and filterable table. Upload only the reviewed publication file—not a working workbook containing confidential sheets or sensitive records. After publication, compare the headers, rows and several values with your checked copy before distributing the link.