Excel For Mac Pivot Table Repeat Item Labels

Use an External Data Source: Displays the Mac OS X ODBC dialog. Choose where to put the PivotTable: New Worksheet: If selected, adds a new sheet to the workbook and places your PivotTable in Cell A1 of the new worksheet. Existing Worksheet: Choose a cell on your worksheet. The cell will be the upper-leftmost corner of your PivotTable. Make sure there’s enough room so your PivotTable doesn. Repeating item and field labels in a PivotTable visually groups rows or columns together to make the data easier to scan. For example, use repeating labels when subtotals are turned off or there are multiple fields for items. In the example shown below, the regions are repeated for each row and the product is repeated for each column.

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

Excel Pivot Table 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

Table

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.

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

Excel For Mac Pivot Table Repeat Item Labels

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

Excel For Mac Pivot Table Repeat Item Labels Greyed Out

The row labels are now repeated, as required!

Excel For Mac Pivot Table Repeat Item Labels Printable

And that's it!