Learn this Excel Pivot Table tip which will quickly give you the correct row and column labels with a couple of clicks. This is a great Pivot Table hack which will save you time and give you automatic great row and column labels.

I cannot find the option to repeat items labels on a pivot table. This is why I need to converting the output the pivot table into tabular table. In excel, there is a option that permits you display the items labels in the same pivot table. Comment 1 raal 2015-01-04 11:29:07 UTC. In Excel 2010 for Windows (and later) the Pivot Table Design ribbon contains a drop-down called 'Report Layout'. Selecting 'Tabular Layout' and 'Repeat all item labels' will show a tabular layout with no blank cells. Unfortunately, this setting is not available in Excel 2011 for Mac.

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

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:

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.

Excel For Mac Pivot Table Repeat Item Labels

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

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:

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!):

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

Pivot Table Repeat Data

The row labels are now repeated, as required!

What Is A Pivot Table

And that's it!

Excel For Mac Pivot Table Repeat Item Labels