559 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
|How to create multiple worksheets based on pivot table report filter values|
|Did you know that you can automatically generate multiple versions of a pivot table, with each one configured to filter by a different item? We didn't - this blog is an attempt to apologise for our oversight!|
We've been training in Excel for how long? 20 years? And yet, just occasionally, a delegate on a course asks a question and changes everything ...
Many thanks to Selam Adamu for her question on a recent course, which led us to realise that pivot tables contained a feature which we had never realised was there!
So let's imagine you've created a pivot table on a set of expenses (actually, you don't have to imagine - you could download the workbook):
The expenses incurred by employee name and year.
You've created a report filter:
You have dragged the Category field into the pivot table filters box.
This means that you can choose to show only certain categories:
You can filter the report to show only expenses in one or more selected category.
However, you can also go to the Analyze tab of the Excel ribbon (I'm using Excel 2016, but this feature has been there - to our shame - since at least Excel 2010):
Click in the pivot table, then select this tab.
You can then choose to show report filter pages:
Show one worksheet for each report filter item.
Excel will ask you which report filter you want to use:
Even though there is only one report filter, you still have to choose it.
What happens next is amazing! Excel creates one version of the pivot table for each report filter item:
The worksheets are in alphabetical order, so the first one is Other, showing all of the expenses in the Other category.
It's also possible to create multiple worksheets like this using grouping and page breaks in SQL Server Reporting Services - but the Excel method is much simpler!
This feature is confined to Excel pivot tables and report filters. So it doesn't, for example, work in PowerPivot pivot tables:
Exactly the same pivot table based on a workbook imported into PowerPivot, but here the option is greyed out.
There also doesn't seem to be any way to perform this trick with a slicer:
Slicers may be good, but they don't allow you to create multiple worksheets automatically based on the slicer values.
Still an impressive feature, though!
Some other pages relevant to the above blog include:
25 Aytoun Street