Excel Pivot Table: Show All Row Values

Charts & pivot tables · Updated November 18, 2026

To show a row label on every line in an Excel PivotTable, select the table, choose PivotTable Design > Report Layout > Show in Tabular Form, then choose PivotTable Design > Report Layout > Repeat All Item Labels. This repeats category names that Excel normally leaves blank on subsequent rows; it does not expand summarized totals into every underlying source record.

What “show all row values” can mean

The phrase can describe two different PivotTable tasks. Often, someone sees a category name once, followed by blank cells in that column, and wants the name repeated beside every detail row. For example, a report might show West beside the first product but leave the Region cell blank for the next two products. The rows still belong to West; Excel is using a compact report layout to avoid repeating the parent label.

In other cases, the request is to display every source value or transaction rather than a sum, count, or other summary. A PivotTable is designed to group and summarize records, so repeating labels alone will not expose the individual transactions. The right fix depends on which problem you have: use Repeat All Item Labels for blank-looking labels, and add a unique detail field to Rows or use Show Details when you need the underlying records.

Repeat row labels in Excel for Microsoft 365

These steps apply to Excel for Microsoft 365 on Windows. The command works on a selected PivotTable and is most useful when each row field should occupy its own column.

  1. Click any cell inside the PivotTable to display the contextual PivotTable tabs.
  2. Choose PivotTable Design > Report Layout > Show in Tabular Form. This places each row field in a separate column instead of nesting several fields in one compact column.
  3. Choose PivotTable Design > Report Layout > Repeat All Item Labels.
  4. Check the result at each level of the row hierarchy. Region, product category, and product labels should now appear on the rows where they apply.

If you only want to repeat one field, right-click a label from that field, choose Field Settings, open Layout & Print, and select Repeat item labels if the option is available. This is helpful when a report has several row fields but only one needs a repeated value. The all-labels command is quicker when the goal is a flat table that can be copied or exported.

In Excel for Mac, select the PivotTable and look for the contextual Design tab and its Report Layout controls; ribbon placement can differ slightly by build and window size. Excel for the web has a more limited ribbon, so if a command is not visible, open the workbook in the desktop app to use the full layout controls.

Choose a layout that makes the labels visible

Excel offers compact, outline, and tabular layouts. Compact Form combines row fields into one column, which saves horizontal space but can make a hierarchy look like it has missing values. Outline Form separates row fields into columns, while Tabular Form presents the fields in a more table-like structure. For a report that needs a value beside each detail row, Tabular Form is usually the clearest starting point.

Repeating labels changes how the report is displayed, not the source data or the calculations. It does not insert new source records, change a sum to a list, or make each repeated label a separate category. This distinction matters when you copy the PivotTable into another system: the repeated labels make the layout easier to read and filter, but the numbers remain PivotTable summaries.

Subtotals can also affect what appears to be a row value. To make a detailed layout less cluttered, use PivotTable Design > Subtotals > Do Not Show Subtotals when appropriate. Grand totals can be adjusted separately through PivotTable Design > Grand Totals. These settings control summary rows and columns; they are not substitutes for repeating item labels.

Show every underlying record, not just the summary

Suppose a source table contains several sales transactions for each product and the PivotTable shows one total per product. Repeating the product label will repeat the product name on summary rows, but it will not turn the total into a transaction-by-transaction listing. To make individual records appear in the PivotTable, add a field that identifies each record—such as Order ID or Transaction ID—to the Rows area. Add other fields, such as date or customer, if they help distinguish records.

When a unique identifier is included, each transaction can form its own row. Without one, Excel may group records that share the same combination of row-field values, so repeated-looking entries can still be combined. If you need a complete flat extract rather than a PivotTable summary, use the source table itself or copy the relevant source records to a separate worksheet.

You can also retrieve the records behind a summarized number with Show Details. In a standard PivotTable, double-click a value cell—not a row label or grand total—to have Excel create a new worksheet containing the source rows that contribute to that value. Right-clicking the value and choosing Show Details is another route when that command is available. The resulting sheet is a drill-through extract, not a change to the PivotTable’s layout; if the command is unavailable, the PivotTable’s source or connection may not support it.

Display items that have no data

Another common meaning of “show all” is to include categories that currently have no matching records. For example, a monthly report may need to list every product even when one product had no sales during the selected month. This is different from repeating labels: a category with no data is absent, rather than present with a blank parent label.

For a regular PivotTable, right-click a field label, choose Field Settings, and check the Layout & Print options for Show items with no data, if available. PivotTable-wide display options may also include separate choices for showing items with no data on rows or columns; open PivotTable Analyze > Options and check the Display tab. The precise availability depends on the PivotTable’s source and Excel build.

Showing empty items does not create missing source records or prove that a category had a zero result. It displays an available category even when the current filter context has no matching data. If you need explicit zeros rather than blank cells, review the PivotTable’s layout and value display settings, and confirm that the category exists in the source or model. PivotTables based on the Data Model or certain external connections can behave differently from a simple range-based PivotTable.

Why labels or rows may still be missing

If the command appears to have no effect, first check whether you selected a cell inside the PivotTable. The contextual tabs only appear when the PivotTable is active. Then confirm that the blank cell is actually a repeated parent label, not a genuinely empty cell in the source data. A blank source value is not the same thing as a label Excel suppresses for layout.

  • A filter is hiding items: Check the field’s filter menu, report filters, slicers, and timelines. Clear selections temporarily to see whether the missing categories return.
  • The hierarchy is collapsed: Expand the relevant field using its expand controls, or right-click an item and look for the expand option. A collapsed parent intentionally hides its lower-level members.
  • The source has no matching category: Refreshing cannot display a category that is not present in the source or otherwise available to the PivotTable.
  • The PivotTable has not been refreshed: Choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections. If rows were added outside the PivotTable’s source range, update the source range or use an Excel Table as the source before refreshing.
  • The report uses Compact Form: Switch to Tabular Form first, then apply Repeat All Item Labels so each row field is easier to inspect.

Keep the source table’s field names in a single header row and avoid blank headers or merged cells. A clean source structure makes fields easier to place and reduces confusion about whether a missing label comes from the report layout or the underlying data.

When to use a PivotTable, a flat table, or a formula

A PivotTable is a good choice when you want to group records, compare totals, rearrange dimensions, or filter a report interactively. Repeated labels are particularly useful when readers need to scan across columns, sort, or export the report while retaining the parent category on every row. Tabular Form with repeated labels can also be easier to use as a starting point for a secondary analysis, although a PivotTable remains a summarized report rather than a general-purpose data table.

Use the original Excel Table when the main need is to see or edit every transaction. A normal filtered table preserves each source row and can show all fields without grouping them into summaries. Use Show Details for a quick investigation of one PivotTable total. If you need a reshaped, refreshable dataset for repeated reporting, Power Query may be a better fit than manually copying drill-through sheets; it can transform source data while leaving the original records intact.

Before distributing a report, decide what “all” means for its audience: every label repeated, every category including empty ones, or every transaction listed. Those are separate requirements and can call for different layouts or data sources. A useful final check is to compare a few PivotTable totals with the source records and verify that filters have not excluded items the reader expects to see.

Frequently Asked Questions

Why does Excel show a row label only once?
Compact or hierarchical PivotTable layouts suppress repeated parent labels to make the hierarchy more compact. Choose Tabular Form and Repeat All Item Labels to display the applicable label on each row.

Does Repeat All Item Labels show every transaction?
No. It repeats labels in the report but keeps the PivotTable’s existing aggregation. Add a unique transaction field to Rows, inspect a value with Show Details, or use the source table to view individual records.

Can I repeat just one row field?
Yes, where the field settings allow it. Right-click that field’s label, choose Field Settings, and look under Layout & Print for Repeat item labels. Otherwise, use Repeat All Item Labels for every row field.

How do I show categories with no sales?
Use the field’s Show items with no data option, or check the PivotTable Options display settings for items with no data on rows. Availability depends on the source and connection, and the setting does not add missing records.

Will new source rows appear after I repeat labels?
Not automatically just because labels repeat. Refresh the PivotTable, and make sure its source range includes the new rows. Using an Excel Table as the source can help the source expand as records are added.

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.