How to get row labels for a pivot table group to repeat
A short blog showing how to stop Excel hiding duplicate values for pivot table groups.

Posted by Andy Brown on 01 November 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Repeating row labels for groups in a pivot table

Someone on a course this week asked me how you could do the following:

Pivot table without repeated row labels With repeated row labels
Without repeated labels With repeated row labels

That is, whether you can get Excel to show every row label, even when there are duplicates.  The answer, I'm pleased to say, is yes!

Repeating row labels

The file for this blog (should you want to try things out yourself) can be downloaded here.  The first thing to do is to right-click on the group whose rows you want to repeat, and choose to change its field settings:

Field settings menu

Right-click on the group (here it's the travel method we want to repeat, so we right-click on Coach), and choose the Field Settings... option shown.


Next, prevent subtotals appearing for this group (otherwise they will come between the repeated row labels, which looks strange):

Turning subtotals off

Select None as shown here to avoid subtotalling the travel methods.

Now go to the Layout & Print tab on the same dialog box, and tell Excel to display the pivot tables labels in separate columns, without grouping:

Items in tabular form

You need to display your row labels in tabular form.


Finally (and still in the same dialog box) tick the box to repeat item labels (at last!):

Repeat item labels option

Tell Excel to repeat the item labels!

You should now see your pivot table row labels repeating for each group:

Repeated row labels

The row labels are now repeated, as required!


And that's it!

This blog has 0 threads Add post