Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to get row labels for a pivot table group to repeat |
---|
A short blog showing how to stop Excel hiding duplicate values for pivot table groups. |
Someone on a course this week asked me how you could do the following:
Without repeated labels | With repeated row 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!
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!):
Tell Excel to repeat the item labels!
You should now see your pivot table row labels repeating for each group:
The row labels are now repeated, as required!
And that's it!
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.