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!

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):

Expenses workbook

The expenses incurred by employee name and year.

You've created a report filter:

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:

Choosing a category

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):

Analyze tab

Click in the pivot table, then select this tab.

You can then choose to show report filter pages:

Report filter pages

Show one worksheet for each report filter item.

 

Excel will ask you which report filter you want to use:

Choosing a report filter

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:

One worksheet per 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:

PowerPivot pivot table

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:

Slicer trick

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! 

This blog has 0 threads Add post