When using Excel you may need to/ want to repeat pivot table row labels. As shown below, the Pivot Table creates a report and you can assume that the blank cells (e.g. A5 to A7) relates to the header in cell A4. But perhaps you would prefer to repeat these headings so that you see ‘Cell C’ in the cells underneath.
Some of the reasons you may want to do this are:
- you plan to copy and paste the pivot table into another sheet to sort or filter and you need the blanks filled in (see here how to manually fill in these gaps), or
- The sheet is very long and as you scroll down you can’t remember what section you are looking at
Since Excel 2010 this has become very easy to do. There is actually a feature built into the Field Settings.
So to repeat pivot table row labels, you can right click in the column where you want the row labels repeated and click on Field Settings as shown below.
In the Field Settings box you need to click on the Layout & Print tab and choose the ‘Repeat items labels’.
Like magic you will now see the row labels repeated on every line.
This way you will always be able to see on each row exactly what makes up that row
Why is the repeat pivot table row labels not working
If you go through these steps and it doesn’t seem to work it is in all likelihood as a result of your report layout. As shown below, when you are in compact format (note below that the red line shows that column A contains 2 fields) the repeat items button doesn’t work. You can click on it but you will still see only a single row label.
It is only when you change to Tabular (our preference) or Outline form that the repeat row labels will be shown.
To learn more about Pivot Tables have a look at the online Pivot Tables training course.
Want to learn more about Microsoft Excel and using it when budgeting or forecasting? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.