Pivot Table Issues

How to Show Repeated Values Separately in Excel PivotTables

Amos Gikunda Sep 27, 2026 869 views

Question details

The user wants to display identical items or repeated products on separate rows in a PivotTable instead of having them automatically aggregated or grouped together.

How to Show Repeated Values Separately in an Excel PivotTable

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating or modifying a PivotTable report where individual transaction records or duplicate product entries need to be listed distinctly rather than summed up.
Observed behavior: Excel automatically groups identical row values into a single line, and standard layout adjustments may fail to keep these repeated products separate, especially after filtering or updating source data.

Before you start

Ensure your source data contains a unique identifier column, such as a transaction ID or row number, to prevent the PivotTable from automatically aggregating identical entries.

Solution 1Recommended

Reorder Row Fields and Adjust PivotTable Layout Settings

Moving the identifying field to the last position in the Rows area and enabling tabular layout can force the PivotTable to display items distinctly.

By default, PivotTables group items hierarchically. Changing the report layout to Tabular Form and instructing it to repeat item labels ensures that the outer fields populate on every row.

Open PivotTable Fields

Click anywhere inside your PivotTable to reveal the PivotTable Fields pane on the right side of the screen.

Reorder the Row Fields

In the 'Rows' area of the pane, click and drag the field containing the repeated values (e.g., 'Unit' or 'Product') to the very bottom of the field list.

Change to Tabular Layout

Navigate to the 'Design' tab under PivotTable Tools on the top ribbon. Click 'Report Layout' and select 'Show in Tabular Form'.

Repeat All Item Labels

Click 'Report Layout' again on the Design tab and select 'Repeat All Item Labels'. This ensures that the grouped fields display their labels on every single row.

Reorder Row Fields and Adjust PivotTable Layout Settings

Tip: If you still see items aggregated, verify that there are no blank fields overriding the layout settings and that subtotal calculations are turned off.

Solution 2

Add a Unique Identifier to Source Data

If Excel still aggregates your data despite layout changes, adding a unique row identifier is the most reliable method to force un-grouping.

Seamless Pivot Table Management

Create and Format PivotTables Easily with WPS Office

WPS Spreadsheet offers a highly intuitive and powerful PivotTable feature that perfectly mirrors Microsoft Excel's capabilities. You can easily adjust report layouts, repeat item labels, and manage row fields without struggling with complex settings or compatibility issues.

1. Insert a PivotTable: Open your workbook in WPS Spreadsheet, select your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
2. Arrange Row Fields: In the PivotTable pane, drag your desired fields into the 'Rows' area, ensuring your unique identifier is properly positioned.
3. Adjust the Report Layout: Navigate to the 'PivotTable Tools' tab, click 'Report Layout', select 'Show in Tabular Form', and then click 'Repeat All Item Labels' to separate the values.

Easily format PivotTables to show repeated item labels in Tabular Layout.100% format compatibility with Microsoft Excel (.xlsx) files and PivotTable structures.Free, lightweight, and incredibly fast spreadsheet processor.Intuitive drag-and-drop interface for managing PivotTable fields.

Frequently Asked Questions

Why does my PivotTable automatically group identical text together?

PivotTables are fundamentally designed to summarize, aggregate, and consolidate large datasets. By default, they group identical row labels into a single line item so they can calculate totals, sums, or counts for that specific category.

How do I stop a PivotTable from summing my data?

If you want to list data without calculating it, place your numerical fields in the 'Rows' area of the PivotTable field list rather than the 'Values' area. Alternatively, if you only need to filter and list data without summarizing, consider using a standard Excel Data Table instead of a PivotTable.

What is the Tabular Form layout in a PivotTable?

The Tabular Form layout changes the PivotTable into a traditional grid format where each row field appears in its own distinct column. This makes it read more like a standard data table and unlocks the ability to use the 'Repeat All Item Labels' feature.

Why does 'Repeat All Item Labels' not separate my aggregated totals?

'Repeat All Item Labels' only fills in the blank cells in the outer row groupings; it does not un-aggregate summarized data. To force completely identical items to appear on separate rows, your underlying source data must contain a unique identifier column that you include in the PivotTable's Rows area.

Excel & Data Problems