Repeat item labels in a PivotTable report

“This Repeat Down Labels is an excellent addition, congrats! The VLOOKUP function is exactly what we often want to use together with pivots but could not in the previous versions.”
~ Fabio F.

In Excel 2010, you can repeat item and field labels in a PivotTable report to make a PivotTable easier to read. You can repeat labels for nested items per field in rows or columns. You can also turn the option to repeat labels on or off for all fields in the PivotTable at the same time.

For example, repeated labels are useful in a PivotTable report that has value fields in columns and grand totals and subtotals turned off for all fields on rows.

What do you want to do?

Repeat item labels in rows

Repeat item labels in columns

Turn repeated item labels on or off for all fields

Repeat item labels in rows

  1. In the PivotTable report, click the row label that you want to repeat.

This displays the PivotTable Tools, adding an Options and a Design tab.

  1. On the Options tab, in the Active Field group, click Field Settings.

Excel Ribbon Image

Tip:  You can also right-click the row label that you want to repeat, and then click Field Settings.

  1. Click the Layout & Print tab, and then select the Repeat item labels check box.

Tip:  To remove the repeated labels, clear the Repeat item labels check box.

  1. To show the repeated labels in the PivotTable, make sure that Show item labels in tabular form is selected.

Note: 

  • When you edit any of the repeated labels, the changes you make are automatically applied to all other cells with the same label.

  • When you change the format of all repeated labels by selecting the item label in the summary row or all repeated labels, the format is automatically applied to all other cells with the same label. However, you can also change the format of individual repeated labels without applying the same formatting to other cells with the same label.

  • Repeated labels are shown in tabular form only. They are not shown when compact form or outline form are applied.

Top of Page

Repeat item labels in columns

  1. In the PivotTable report, click the column label that you want to repeat.

This displays the PivotTable Tools, adding an Options and a Design tab.

  1. On the Options tab, in the Active Field group, click Field Settings.

Excel Ribbon Image

Tip:  You can also right-click the column label that you want to repeat, and then click Field Settings.

  1. Click the Layout & Print tab, and then select the Repeat item labels check box.

Tip:  To remove the repeated labels, clear the Repeat item labels check box.

Note: 

  • When you edit any of the repeated labels, the changes you make are automatically applied to all other cells with the same label.

  • When you change the format of all repeated labels by selecting the item label in the summary row or all repeated labels, the format is automatically applied to all other cells with the same label. However, you can also change the format of individual repeated labels without applying the same formatting to other cells with the same label.

  • Repeated labels are shown in compact form for outer fields only. Repeated labels are not shown when outline form is applied.

Top of Page

Turn repeated item labels on or off for all fields

  1. Click anywhere in the PivotTable for which you want to repeat labels.

This displays the PivotTable Tools, adding an Options and a Design tab.

  1. On the Design tab, in the Layout group, click Report Layout.

PivotTable Tools: The Layout group on the Design tab

  1. Do one of the following:

  2. To display repeated item labels, click Repeat All Item Labels.

  3. To remove repeated item labels, click Do Not Repeat Item Labels.

  4. Click anywhere in the PivotTable for which you want to repeat labels (Test_O15).

  5. This displays the PivotTable Tools, adding an Options and a Design tab(Test_O15)

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×