BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 08 December 2017
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Creating one worksheet per item in a pivot table report filter
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!
A couple of limitations
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!