Excel Pivot Table Tabular View: Setup and Formatting

Charts & pivot tables · Updated November 18, 2026

To display an Excel PivotTable in tabular view, click inside the PivotTable and choose PivotTable Design > Report Layout > Show in Tabular Form. Each row field then appears in its own column instead of being nested together in one compact column.

What tabular view changes

Tabular Form is a PivotTable report layout. It changes how row fields are arranged on the worksheet; it does not change the source data, the calculations, or the fields included in the report. In Microsoft Excel for Microsoft 365 on Windows, the setting is on the contextual PivotTable Design tab, which appears when a PivotTable is selected.

Consider a PivotTable with Region, Salesperson, and Product in the Rows area, and Sum of Sales in the Values area. In Compact Form, the row-field names are grouped into a single column. Tabular Form gives Region, Salesperson, and Product separate columns, followed by the sales values. The fields still describe a hierarchy: a salesperson belongs to a region, and a product may be listed beneath that salesperson.

This layout is useful when a report needs clearly separated fields, when readers want to scan or filter individual columns, or when you plan to copy results into another worksheet. It is still a PivotTable, however—not a normal Excel table or a fully flattened export of every source record.

How to switch a PivotTable to tabular form

First create or select the PivotTable you want to format. If you are starting from a data range, select a cell in the range and use Insert > PivotTable, then choose where Excel should place the report. After arranging fields in the PivotTable Fields pane, use these steps in Excel for Microsoft 365 on Windows:

  1. Click any cell inside the PivotTable. The contextual PivotTable tabs appear on the ribbon.
  2. Open PivotTable Design > Report Layout.
  3. Select Show in Tabular Form.
  4. If you want parent labels repeated on every row, return to PivotTable Design > Report Layout and choose Repeat All Item Labels.
  5. Review subtotals and grand totals, and adjust them if the report is intended to be a flat-looking list.

The change applies to the selected PivotTable. It does not automatically change the layout of other PivotTables, even if they use the same source data. If the design tab is missing, click inside the PivotTable rather than in a nearby cell. In Excel for Mac, select the PivotTable and look for the contextual PivotTable Design tab; ribbon labels and placement can vary somewhat by release. Excel for the web has a more limited set of PivotTable controls in some situations, so open the workbook in the desktop app if the layout command is unavailable.

Repeat labels for a more readable report

Tabular Form separates row fields into columns, but it may leave repeated parent labels blank to avoid displaying the same value on every line. For example, a region might appear on the first salesperson row and be blank beside the following salespeople until the next region begins. That is normal PivotTable behavior, not missing source data.

To show the region and salesperson values on every detail line, choose PivotTable Design > Report Layout > Repeat All Item Labels. This is especially helpful when readers will sort, filter, copy, or export the visible report. A repeated label makes each row easier to understand on its own. It does not add records to the source or alter the underlying grouping.

Repeat labels can make a report longer and visually busier. If the audience reads the report as a grouped summary, blank parent labels may make the hierarchy easier to see. If the audience treats it like a list, repeated labels are usually clearer. You can switch between these choices without rebuilding the PivotTable.

Manage subtotals and grand totals

Tabular Form does not automatically remove subtotals. Excel may show a subtotal for each outer row field, such as total sales by region, as well as a grand total for the entire report. Those summary rows are useful for analysis, but they can interrupt a list intended for export or further processing.

To change subtotal display, select the PivotTable and use PivotTable Design > Subtotals. Choose Do Not Show Subtotals to remove them, or select one of the other available placements if you want them above or below each group. For grand totals, use PivotTable Design > Grand Totals and choose the display that suits the report, such as turning them off for rows and columns.

Before removing totals, consider what the report is for. A manager reviewing sales by region may need regional subtotals. A report being pasted into a system that expects one record per line may be easier to use without subtotal and grand-total rows. Even after those rows are hidden, a PivotTable remains a summary of source data, not a replacement for a properly structured source table.

Tabular, compact, and outline layouts compared

Excel offers three report layouts under PivotTable Design > Report Layout. They arrange the row fields differently, but do not change the underlying PivotTable calculation:

  • Compact Form places multiple row fields in one column, using indentation to show levels. It is space-efficient and is Excel’s familiar default layout.
  • Outline Form gives each row field its own column, like Tabular Form, but presents group headings and subtotals in an outline-style arrangement.
  • Tabular Form gives each row field its own column and is often the clearest choice when readers need to identify field values across a row.

For a short report with a simple hierarchy, Compact Form may be easier to scan because it uses less horizontal space. Tabular Form is often preferable when the field names matter, when you have several row fields, or when you want a more conventional column layout. Outline Form can suit reports where group structure and subtotal placement are central. Try the layouts on the same PivotTable and choose based on how people will read the result.

What tabular view does not do

A common misconception is that tabular view converts a PivotTable into a flat table of source records. It does not. A PivotTable aggregates data according to the fields and value calculations you selected. If the source contains one row per transaction, a PivotTable grouped by region and salesperson may show summarized sales—not every transaction.

Nor does Tabular Form necessarily produce a clean, database-ready dataset. The report can still contain subtotals, grand totals, blank repeated labels, filters, and grouped items. PivotTable cells are also controlled by the PivotTable layout; editing a value directly is not the same as changing a source record. To change the source information, update the underlying data and refresh the PivotTable.

If you need a conventional Excel table for formulas, sorting, or row-by-row editing, use the original source data or make a separate copy of the displayed results. Copying and pasting values can create a static snapshot, but it will no longer update automatically when the PivotTable refreshes. Keep the PivotTable if you need its interactive summaries.

Formatting and practical reporting tips

Once the layout is set, make the report easy to read without confusing presentation with data. Give value fields meaningful names, apply appropriate number formats, and use a PivotTable style with enough contrast to distinguish headings and totals. A currency or date format can make values clearer, but it does not change their underlying numbers.

For the Region, Salesperson, Product, and Sum of Sales example, a useful setup might have one column for each row field, a currency format for sales, repeated labels if the report will be filtered, and subtotals by region if managers need regional comparisons. If the report is meant to resemble a simple list, turn off subtotals and grand totals and check that each row is understandable on its own.

Keep the source data organized as well. Use one header row with a distinct heading for each column, avoid merged cells in the source, and keep each record on its own row. A PivotTable’s layout options cannot repair inconsistent source data, such as numbers stored as text or multiple spellings of the same category.

Common problems and fixes

  • The layout option is missing: Select a cell inside the PivotTable so the contextual PivotTable Design tab appears. If you are using Excel for the web, try the desktop app when a command is not available there.
  • Row labels still look blank: Choose Repeat All Item Labels. Tabular Form separates fields but does not always repeat each parent value automatically.
  • Extra rows appear between groups: Check the Subtotals menu. Turn subtotals off if the report should read as a continuous list.
  • The report changes after refresh: Refreshing can update the PivotTable from its source and may change displayed items or totals. Confirm that the source range includes the intended records and that the layout settings still suit the report.
  • There is only one row-field column: Check the field arrangement in the PivotTable Fields pane. Tabular Form creates a separate column for each field placed in Rows; it does not move fields out of the Rows area or create columns for fields placed elsewhere.

Frequently Asked Questions

How do I make every PivotTable row label repeat?
Click inside the PivotTable, then choose PivotTable Design > Report Layout > Repeat All Item Labels. This displays parent field values on each applicable row.

Is tabular form the same as a flat file?
No. Tabular Form separates row fields into columns, but the PivotTable still summarizes data and may include totals, filters, or grouped categories. Use source data or a carefully prepared values-only copy when you need a flat list.

Can I remove the blank cells in a tabular PivotTable?
Use Repeat All Item Labels if the blanks are parent labels omitted for display. If cells remain blank because the source data itself is blank, changing the layout will not supply missing source values.

Does tabular view change the PivotTable results?
No. It changes the report’s arrangement, not the source records or the value calculation. Changing a field, filter, or calculation can change results; changing the report layout alone does not.

Can I switch back to compact view?
Yes. Select the PivotTable and choose PivotTable Design > Report Layout > Show in Compact Form. You can also select Outline Form from the same menu.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.