How to repeat row labels for group in pivot table?

When working with PivotTables in Excel, data is typically presented using the compact layout by default. In this format, row labels are grouped together in a single column, which can help save space but may compromise clarity, especially when analyzing large datasets with multiple categorical fields. In many cases, it becomes necessary to switch from the compact layout to the outline form, where each field is displayed in its own column. While the outline layout can present data more clearly, by default it will only display the row labels at the beginning of each group, leaving the subsequent rows empty. This can make the data harder to read or interpret, especially if you need to filter, copy, or export your pivot table data for further processing. And how could you repeat the row labels for group in pivot table?

Repeat row labels for all fields group in Pivot table

Repeat row labels for single field group in pivot table

VBA Code - Repeat row labels for multiple pivot tables or bulk worksheets

Repeat row labels for all fields group in Pivot table

Suppose you have a pivot table and want to repeat the row labels for all field groups, as illustrated in the screenshots below. This approach is commonly needed when the pivot table summarizes hierarchical data, such as categories and subcategories, and you want each row to be completely labeled for ease of understanding or for further extraction.

Follow these steps to repeat row labels for all field groups:

1. Click any cell inside your pivot table to activate the PivotTable Tools. Then, under the Design tab. Click Report Layout and choose Show in Outline Form. This action will format your pivot table in an outline layout, making each field display in a separate column. This is necessary because repeated labels cannot be set when your pivot table is still in compact form.

2. Next, in the same Design tab, select Report Layout again, and then click Repeat All Item Labels. This function repeats the available labels in every field for every record, resulting in a more readable flat format:

3. Your pivot table should now display repeated row labels for all field groups, as shown below. Each row will have complete group information, which is especially useful if you want to filter, copy the data elsewhere, or export it for reporting:

If you need to clear the repeated labels and restore the original blank style, follow the same steps and select "Do Not Repeat Item Labels".

This built-in option is quick and reliable, but be aware that it only applies to the pivot table where you make the changes. If you have multiple pivot tables in your workbook or need to perform this operation on several reports, you will have to repeat the steps individually or consider automated methods described later.

Repeat row labels for single field group in pivot table

You may not always want to repeat row labels for all fields—for example, in situations where only the highest-level group needs repeating and lower-level groups do not. Excel allows you to repeat item labels for a specific field only, giving you greater customization, especially when you want to highlight just a key category or minimize visual redundancy.

1. As before, set your pivot table to Outline Form: click any cell inside the pivot table, go to Design > Report Layout > Show in Outline Form.

2. Click directly on the row label for the field you wish to repeat in your pivot table. This will select that field's label in the pivot table.

3. Right-click the selected row label, and then choose Field Settings from the context menu that appears.

4. In the Field Settings dialog box, switch to the Layout & Print tab. Check the box labeled Repeat item labels. See screenshot:

5. Click OK to apply the changes. Now, only the specified field’s labels will repeat.

This method provides field-level flexibility while keeping the rest of your data compact. Remember: this setting will apply only to the selected field, and you can use it in combination with or instead of the full-table method above.

Be aware that if you rearrange your pivot table fields or refresh the pivot table, the repeated label settings might reset, requiring you to reapply the option if necessary.

VBA Code - Repeat row labels for multiple pivot tables or bulk worksheets

If you need to apply the repeated row labels setting to multiple pivot tables—or even to the same field across many worksheets—doing it manually can be time-consuming, especially when managing complex reports or dashboards. In these scenarios, using VBA (Visual Basic for Applications) can automate and streamline the process.

1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications editor. In the VBA window, click Insert > Module and copy-paste the following code into the module:

Sub RepeatRowLabelsAllPivots()
    ' This macro applies repeated row labels for all PivotTables in the current workbook
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RowAxisLayout xlOutlineRow
            For Each pf In pt.RowFields
                pf.RepeatLabels = True
            Next pf
        Next pt
    Next ws
End Sub

2. After entering the code, click Run button or press F5 key. All pivot tables in all worksheets will be switched to outline layout, and all row labels will be repeated for each group and field.

Related articles:

How to create clickable hyperlinks in pivot table?

How to display grand total at top in pivot table?

How to filter top 10 items in pivot table?