Monday, 20 April 2015

Excel - Repeat All Item Labels in a pivot table

Introduction
When working with pivot tables, especially OLAP pivot tables, it's often the case that I have needed to flatten the pivot and copy the data to a new sheet to create a new table (a 'flat file'). One important part of this is to fill in the data labels of the rows.

On the Design tab, click Show Report in Tabular Form
Previously to fill in all the labels, on each column I was right clicking, Field Settings | Layout & Print | Repeat Item Labels:


However, this only works for one column at a time.

Repeat All Item Labels
The following is a better way of doing it, below we repeat all the item labels for the whole pivot table in one go with just a couple of clicks:


Click on the pivot table
Click Design
Click Report Layout | Repeat 


Conclusion
It is strange that Microsoft put the two variations of this function in different places. However, it was my mistake not to notice this earlier. Maybe you didn't notice it either? If so I hope this article has helped you.


Excel Version?
Excel 2013 and 2010.

Post a Comment