Create Multiple Pivot Table Reports with Show Report Filter Pages

Learn how to quickly create multiple pivot table reports with the Show Report Filter Pages feature.

Pivot tables are an amazing tool for quickly summarizing data in Excel. They save us a TON of time with our everyday work. There is one "hidden" feature of pivot tables that can save us even more time.

Sometimes we need to replicate a pivot table for each unique item in a field. This could be a report for:

Each department in organization.
Each salesperson on the sales team.
Each account in the general ledger.
Each customer in the CRM system.
Each stock in the database.
Or, just about any other field (column) in your data set.
We could create one pivot table, filter it for a specific item, then copy the sheet and re-apply a filter for the next item. But this would take a lot of time if we have dozens or hundreds of unique items in the data set.

Fortunately, we don't have to do all this manual work. Pivot tables have a feature called Show Report Filter Pages that automates this entire process.

The Show Report Filter Pages Feature

The Show Report Filter Pages feature:

Creates a copy of an existing pivot table for each unique item in a field.
The new pivot tables are created on individual worksheets.
Each sheet is renamed to match the item name.
A filter is applied to the field in the Filters Area of each pivot table for the item.
All this is done with a click of a button. Your field can have 5 or 500 unique items. Show Report Filter Pages will create a sheet for each item and replicate the pivot table report.

[missywanstall]

I had no idea this functionality existed. This is a HUGE TIME-SAVER for providing multiple pivot tables for reporting.

[CM-qidh]

VERY HELPFUL WITH SO LITTLE TIME IN THE DAY -YOU GO POINT BY POINT TO THE EXACT INFORMATION NEEDED TO CREATE MULTIPLE PIVOT TABLES ESPECIALLY FOR ME AS A ONE TIME USER...BEGINNER AGAIN~

[Serraomomma]

Thank you! I often use this video every time I forget how to do this quick trick! I'm sure you know by now that you don't need "tab hound" to go to a specific worksheet. Right-click on the right arrow (that points to the worksheets) and a list of all the sheets will display.

[spark]

I didn’t know this feature was available. Thanks! It will save me tons of time

[ElTigreFIME]

Nice and concise content to solve this common problem scenario…thanks for sharing your knowledge!

[jeny.]

What a great feature! Learning a lot. Thank you!

[adnanahmed]

Very helpful. I was looking for this feature since long.

[TheYasinsonu]

Thanks for showing this function in Excel.

[yesenia]

This was great, easy to follow and very practical

[hmwzvpl]

Wow I had no idea what an awesome feature

[leapfrogtutoring]

Awesome! Thanks for this invaluable show of humanity.

[felipemendes]

I only wish I had watched it sooner in my life :D Thanks!

[NehaSingh-zdvj]

That's awesome, thanks for shairing

[daedal]

@Jon, you can just right-click the arrows that toggles through the sheets for a list and just select the sheet you want. No plug in needed.

[iudenden]

Thank you so much for this! I am guilty of having done the copy paste of pivot table to several tabs!

[Howtoexcelatexcel]

I love this Ecel trick, always good to show colleagues this one.!

[janhughes]

This was very helpful. Thank you so much for sharing!

[prathameshsakharkar]

Hi, Thanks that is useful. Can you also guide with the process to create a separate file for each sales person so that he will get his individual data

[abhishekpawan]

Wonderful, thanks Man! Also, great add-in

[AltafKhan-hdbs]

Hii
Can i do this in normal excel file to create many worksheets without pivot table