ExcelInsider Team
When you create a Pivot Table in Excel with multiple fields in the rows area, the default setting is for the outer row labels not to repeat. This can make it difficult to see which group each row belongs to, especially when scrolling through a large dataset. While this compact view saves space, it can hamper readability. Fortunately, Excel provides a simple way to repeat these labels, making your Pivot Table much easier to understand.
In this article, we will cover several methods to repeat row labels in Pivot Table.
To repeat row labels in a Pivot Table, here is one simple solution by using the Report Layout feature.
➤ Go to Design > Layout > Report Layout.
➤ Choose Repeat All Item Labels, and you will get all the row labels repeated in your Pivot Table.
Using Report Layout Feature to Repeat All Row Labels
This is the most efficient method for repeating all row labels in your Pivot Table. It is a quick fix that applies to all fields in the rows area.
Suppose we have some sales data including Year, Country, Region, Product, and Total Sales. We will use this data to create a Pivot Table, convert the layout to Tabular Form, and demonstrate how to repeat the labels.
➤ To create a Pivot Table, select the data and go to Insert > PivotTable.
Now, drag Year, Country, and Region to the Rows area, and Total Sales to the Values area to create the Pivot Table. As you can see, the row labels are not repeated, and the table is in a nested Compact Form.
To repeat all the labels, we first need to change the layout to a Tabular Form. This separates the nested fields into their own columns.
➤ Click anywhere inside your Pivot Table.
➤ Go to the Design tab on the ribbon.
➤ In the Layout group, click Report Layout.
➤ From the dropdown menu, select Show in Tabular Form.
Your Pivot Table will now have the fields in separate columns, but the labels in the first and second columns still have blank cells.
Now, to repeat the labels in those blank cells.
➤ Click on the Design tab again.
➤ In the Layout group, click Report Layout.
➤ This time, select Repeat All Item Labels from the dropdown menu.
Your Pivot Table will now display all the row labels repeated, making it much easier to read.
Applying Field Settings Option to Repeat Single Row Label
While the previous method repeats all row labels at once, you might only want to repeat a single label. The Field Settings option allows you to do just that.
To repeat only the labels in the Year column:
➤ Right-click on any cell in the Year column.
➤ From the context menu, select Field Settings.
A dialog box will appear.
➤ Navigate to the Layout & Print tab.
➤ Checkmark the box for Repeat item labels.
➤ Click OK.
Now, only the labels in the Year column are repeated, leaving the Country label as it was.
Using VBA Code to Repeat Row Labels in Pivot Table
If you frequently work with Pivot Tables and want a more automated solution, you can use a simple VBA macro. This code will automatically apply the “Repeat All Item Labels” setting to all Pivot Tables in your active workbook.
➤ Click on the Developer tab.
➤ In the Code group, click on Visual Basic.
➤ Go to Insert > Module.
➤ Copy and paste the following code into the new module window and hit Run.
Sub RepeatRowLabelsInPivot()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = Worksheets("Pivot3").PivotTables("PivotTable4")
For Each pf In pt.RowFields
pf.RepeatLabels = True
Next pf
MsgBox "Row labels have been repeated for all fields in the pivot table.", vbInformation
End Sub➧ Set pt = Worksheets("Pivot3").PivotTables("PivotTable4"): This line targets a specific Pivot Table named "PivotTable4" on the "Pivot3" worksheet.
➧ For Each pf In pt.RowFields: This loop goes through every field in the "Rows" area.
➧ pf.RepeatLabels = True: This is the key command that tells Excel to repeat the labels for each of those fields.
➧ Next pf: This moves the loop to the next field.
➧ MsgBox "Row labels have been repeated...": This line shows a confirmation message when the macro is done.
A message box will pop up confirming that the row labels have been repeated.
➤ Click OK.
The labels will now be repeated in your Pivot Table. This method is especially useful if you have multiple Pivot Tables and want to apply the change quickly.
Frequently Asked Questions
Can I remove repeated row labels later?
Yes, go to Design > Report Layout > Do Not Repeat Item Labels to remove repeated labels and revert to the default compact view.
Why aren’t my row labels repeating even after enabling the option?
Common reasons include using the Compact Form layout (which does not show repeated labels clearly) or the field being in a column rather than a row. Switching to Tabular Form usually resolves this.
Can repeated row labels be used with grouped data?
Yes, repeating labels works with grouped data. Each group will show the repeated label for all its items, making complex reports easier to read.
Concluding Words
Above, we have explored different ways to repeat row labels in a Pivot Table. This simple adjustment can improve the readability of your data. Whether you use the Report Layout feature, Field Settings, or a VBA macro, you have all the options to customize your Pivot Table row labels. If you have any further questions or need assistance with similar Pivot Table formatting, feel free to share them below.
