This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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!