How To Use Show Report Filter Pages Option For PivotTables In Excel

Here’s a great feature in Excel that automatically generates individual worksheets for each filter option you select:

You can download the file here to follow along. If you see a preview, look for the download arrow in the upper right hand corner.

Here is a table of data from which I want to create a PivotTable:

pto1

Clicking on the table, selecting PivotTable from the Insert tab, and opting for it to be on a new sheet generates this:

pto2

After dragging the fields to the areas I want, my PivotTable has taken shape:

pto3

Notice that chose to have both Region and New/Used as Filters for my table. Under Regions I have East, West, North, and South:

pto4

The challenge is this: what if I want to create a report for each of the 4 regions, have them on separate sheets, and be able to print them out? That could take a significant amount of time, especially if there were a dozen or more items in a Filter category.

The answer? It’s simple in Excel! Just choose the “Show Report Filter Pages…” option from the Options drop down in the PivotTable group of the Analyze tab:

pto5

Once chosen, a dialog box will appear asking you which filter you want to select. You can only choose one:

pto6

As soon as I select “Region” and click OK, four new tabs appear in my workbook:

pto7

One for each of the items in the Region filter! Now I have a separate PivotTable for each of the Regions of my data on a separate worksheet (here’s the East, for example):

pto8

Now that’s a time-saver!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

12 Responses to How To Use Show Report Filter Pages Option For PivotTables In Excel

  1. Natalie June 12, 2015 at 9:21 am #

    Hi,
    Any idea why this feature would be grayed out in the Options menu, and how to get it back? This is a function I used a lot in my last version of Excel, it’s a true time saver for me!

    • Michael Rempel June 12, 2015 at 1:53 pm #

      Natalie, I’ve only seen two reasons why it would be grayed out. One is that you haven’t dragged any categories into the Filters quadrant. The other is you are using Power Pivot. I can’t confirm the latter as a valid reason, but saw it on some blogs, but the first reason certainly would cause this issue. Let me know your response.

  2. James July 31, 2015 at 8:43 pm #

    Thats definitely a time saver, but is there a way to use this same idea but make tables instead?

    • Michael Rempel July 31, 2015 at 9:19 pm #

      James, yes, it’s definitely a time saver, but I know of no way to make them tables automatically. There may be a way with VBA, but that’s not my expertise.

  3. Kishan Fafadia June 20, 2016 at 7:04 am #

    Hello,

    Can i get it extracted in separate workbook, there are like 100 worksheets.

    Thanks

    • Michael Rempel June 20, 2016 at 6:31 pm #

      There is no option as to where the “Show Report Filter Pages” lands, it will only produce them in the current workbook. After they are generated, you can easily move them in bulk to a new workbook.

  4. Rajesh October 5, 2016 at 9:44 am #

    Hi Michael, Is there any option to select two fields at a time? In the above example, Can we select the combination of “West”- All categories of “New/Used” and “East” – All categories of “New/Used” and so on and like wise?

    • Michael Rempel October 5, 2016 at 11:11 am #

      Sorry, Rajesh. As far as I know, there is no way to select more than one filter option at a time when using Show Report Filter Pages

  5. Varun October 12, 2016 at 9:58 am #

    Hi Michael,

    Thanks for your great post!

    I have just the opposite scenario, I have multiple sheets of pivot table region just like yours, Now I have to combine them and make it look like parent sheet (i.e from where we have created our pivot table and separated it in multiple sheets).

    Thanks

    • Michael Rempel October 12, 2016 at 7:25 pm #

      I would be happy to look at what you are trying to do if you can send me a sample of your file or something detailing it a bit more. You can e-mail me at mrempel@excel-bytes.com

  6. Sam Levite December 1, 2016 at 2:33 pm #

    There is a danger to using this technique
    Do not think that just because the “filtered” data is in a new sheet, ONLY that data is in that sheet
    ALL the data is actually copied from the original pivot table. In other words, look at the filter in one of the new sheets. Notice the filter icon? Click on it. You can still select from the entire list and run the danger of sending ALL the data to people that don’t need to see everything.
    This technique DOES NOT remove everything else from the pivot. It only HIDES it

    • Michael Rempel December 1, 2016 at 7:13 pm #

      Sam, you are correct. As I demonstrated in my video, you can click on the drop down in each of the filtered pages and select any or all of the options available. It is still a quick and convenient way to create a filtered pivot table for each of the selections for your filter, but you are correct, it does contain all the data from the pivot table.

Leave a Reply