When working with Pivot Tables, it is sometimes useful to repeat the item labels to avoid blank cells in the table. This might be because you want to copy/ paste the data as a flat file, or simply to be able to see what fields are in play in a very long Pivot Table. The newer versions of Excel have this useful tool but you may find that the ‘repeat item labels’ is greyed out.
Table of contents
YouTube Repeat item labels greyed out
Impact of Pivot Report format on ‘Repeat Item Labels’
The most likely reason for repeat items not working is the format of your Pivot Report.
By default the latest versions of Excel use ‘Compact’ format where a single column e.g. Column A below contains multiple fields. In this case, practically there is not enough space to have the item name shown on every row.
As a result you need to change your Pivot Report format to ‘Tabular’ in order to be able to use the Repeat Item labels.
As shown below, click anywhere in the Pivot Table, choose the Design ribbon tab, go to the Report Layout Ribbon and make it Tabular format. Immediately you will see that there is now space for the item labels to be repeated.
Switch on the Repeat Item Labels
Now you will be able to use the ‘Repeat Item labels’ tick box. Right click in the relevant column and click on ‘Field Settings’ as shown below.
Choose the Layout & Print tab and tick the ‘Repeat item labels’ as below. Now as shown in cell A5 down, all the blank cells are filled with the item above.