Bottom line: Learn how to quickly create multiple pivot table reports with the Show Report Filter Pages feature.  Video tutorial below.

Skill level: Beginner

Report Filter Pages - Pivot Table for Each Item in Filter List Excel

Video: How to Create Multiple Pivot Table Reports Based on a List

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the File

Download the example Excel file to follow along.

Show Report Filter Pages.xlsx (97.5 KB)

Create a Pivot Table for Each Item in a List

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.
  • Salesperson on the sales team.
  • Account in the general ledger.
  • Customer in the CRM system.
  • Stock in the portfolio.
  • 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.  This would take A LOT of time if we have dozens or hundreds of unique items in the data set.

How to Replicate Pivot Table Report for All Items in a List

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:

  1. Creates a copy of an existing pivot table for each unique item in a field.
  2. The new pivot tables are created on individual worksheets.
  3. Each sheet is renamed to match the item name.
  4. 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.

Here are the steps to use Show Report Filter Pages:

Step 1 – Add the field to the Filters Area

The first step is to create a pivot table.  Then add the field that contains the list of items to the Filters Area of the pivot table.

In this example we want to create one pivot table for each salesperson in the organization.  So, we are going to add the Salesperson field to the Filters Area of the pivot table.

Add Field for Report Pages to Filters Area of Pivot Table

This allows us to filter the entire pivot table for a single sales person.

Check out my videos series on pivot tables and dashboards for more on how to use pivot tables.  I also have a article on how pivot tables work that explains the Filters Area in detail.

Step 2 – Run the Show Report Filter Pages

Once we have the pivot table all setup with the report we want to replicate, we just have to click the Show Report Filter Pages button.

Show Report Filter Pages Step-by-Step Guide

  1. Select any cell inside the pivot table.
  2. Select the Options/Analyze contextual ribbon tab.
  3. Click the drop-down arrow to the right of the Options button on the left side.
  4. Click the Show Report Filter Pages… button.
  5. The Show Report Filter Pages window will appear with a list of all the fields in the Filter Area of the pivot table.  Select the field to create the reports on
  6. Then press OK.
  7. Result: A worksheet will be created for each item in the list with a pivot table that is filtered for that item.  The worksheet will also be renamed to match the item name.

Results of Show Report Filter Pages - New Sheet with Filtered Pivot Table

With just a few clicks we have a report for each item in the list.  This can be a huge time saver!

Important Things to Note

There are a few important things to know about Show Report Filter Pages.

Pivot Table Changes are NOT Linked

When we make changes to the original pivot table, those changes will NOT be reflected in the new filter pages pivot tables.  If we wanted to change the layout or formatting of all the reports, it would be best to delete all the filter pages (new worksheets) and generate the reports again.

Changes to the Original Pivot Table will NOT Change Show Report Filter Pages

Fortunately, this is pretty easy to do.  We can select multiple sheets by holding the Shift key, then right-click>Delete to delete all the sheets at one time.

Check out my video series on productivity tips for working with sheets for more tips & tricks.

Existing Filters are Applied When Creating Filter Pages

Any filters that are applied to other fields in the pivot table will be applied to all of the new pivot tables.  So, it's best to clear the filters from other fields in the Filters, Rows, or Columns area BEFORE running Show Report Filter Pages.

Clear Other Filters Before Running Show Report Filter Pages

We can also use this to our advantage if we do want to apply filters to all of the reports.  Either way, it's just good to know how this works.  If you generate reports and some of them are blank, it might be due to a filter that is applied somewhere.

Only the Pivot Table is Replicated

Show Report Filter Pages only replicates the pivot table that we run it on.  It is NOT creating a copy of the existing sheet.  It is creating a new sheet and creating the pivot table on a new blank sheet.

Other Objects are NOT copied to Report Filter Pages

If you have any other formulas, slicers, or charts on the original pivot table sheet, those objects will NOT be copied to the new sheets.  We would need to use a macro to do that type of replication.

Navigate Sheets with The Tab Hound Add-in

At the end of the video I showed how we can use the Tab Hound Add-in to quickly search for any sheet in the workbook, and navigate right to it.  I developed the Tab Hound Add-in specifically for this task of navigating large workbooks that contain a lot of sheets.

Tab Hound Quickly Search and Navigate to any sheet in the workbook

Tab Hound eliminates the horizontal scrolling we typically do to find a sheet in a large workbook.  Instead, you can simply type a search for a sheet name in the Tab Hound window, and jump right to it.

Tab of Contents with Tab Hound

I also shared the Table of Contents feature that allows you to create a Table of Contents with clickable links to each sheet in the workbook.  This will make it easy for your users to navigate these large workbooks that contain a lot of sheets.

Tab Hound Add-in for Excel Box

Click here to learn more about Tab Hound

What will you use this technique for?  Please leave a comment below with any suggestions or questions.  Thank you! 🙂


Your email address will not be published. Required fields are marked *

  • Hi Jon,

    I believe i’m in excel 2011 on my mac. My options does not have a drop down arrow and I can’t find anywhere in my excel how to show report filters. IDK what to do and its extremely bizzare.

  • Hi

    Great Feature : However Please seek your assistance asap

    I am trying create Multiple excel worksheets/File for my sales person by creating filter pages, it should get auto saved to a given Directory path or by default at Desktop directory, with usual report item field as sheet name

    Current feature create sheets at same workbook, But saves the source data at each Page field/sheet, which may leak the data to other Sales person, if I send the Excel sheet copy through email attachment, I am seeking a solution it,

    The page field should not have others Report item as selection, all the new generated pivot should be get pasted as values or by removing source data !

    I tried the feature by removing “Existing Filters are Applied When Creating Filter Pages” but new sheet allow user to regenerate new filter and view others data !

    Hope I explained it properly

    Thank you for your immediate response

    Have a nice day
    Best Regards,

    • Hi Rajendra

      The problem for source data being copied in to the Filtered Worksheets can be resolved.

      Before processing with the “Show Report Filter Pages”;
      In the same drop down, go to “Options”; a New Window shall open.
      In this window, go to the “Data” tab.
      In the Data Tab, Uncheck “Save Source Data with File”.

      After doing this,
      Save the file again.
      Now, proceed with “Show Filter Report Pages”.

      It will create multiple worksheets in the same file.

      Go to each worksheet and Move/Copy it to a new file and save it.
      Once you close and reopen the file, it will not have the source data in it and thus cannot be re-filtered nor can any other data been seen.
      This file is safe to email/share with your sales person.

      I am figuring out as to how can I directly generate new files instead of new worksheets as that would save some time for sure.

  • If using the show filter pages to sort suppliers, can a new suppliers be added to the pivot table and show that filter page without duplicating all the previous pages so that you have duplicates plus the new one?

  • Hi Jon,

    In one of the steps, you mention that if you have a table on the page, it will not copy when you separate sheets unless you use a macro for that. Do you have a tutorial about how to create that macro? Also, would run the show report filter and then do the macro?

    Thank you!

  • i have used this for years, but now on excel 365 when i generate additional pages the names do not appear. is there a place to set that?

  • Hi Jon,

    I found your video very useful. I want to know if there is a way I can filter out which all items I want to be created in the report. For eg – out of the 50 salesmen if i want reports only for a “particular” 10 guys or the 10 guys with maximum revenue (assuming revenue is also a column in the DATA source table)

  • I use show report filter pages regularly, but I have a bit of a challenge as I want to be able to show report filter pages for two pivot tables (slide to slide) on each sheet.
    The first step I linked the two pivot tables using a slicer with the hope that I could achieve this but I don’t see how to use a slicer to show report filter pages. Basically, I want two separate pivot tables for the same customer displaying different information on each sheet.
    Please do you know a way to achieve this with the show report filter pages function?

  • The given instruction to Create the Multiple Pivot Table Reports with Show Report Filter Pages is very nicely explained in this blog which is very useful for the viewers especially for those users whose use MS Excel but has anyone faced the error code 0xc004f074 while operate the MS office?

  • Good Morning Jon,

    I am new to your blog and watched your great video on pivot table. Thanks it helped me alot.
    Now my question is how to delete these filtered report from the worksheet. By mistake I have clicked report filtered pages and within a few second there are more than 50 sheet popped up. I could not easily find my original source work sheet and the pivot table.
    Currently i donot need those reports. how to delete them or any tips ..
    Kind regards,

    • Hi Sapsmart1,
      A nice way to browse the different sheet names is to right click one of the 2 left/right arrows at the bottom left side (on the side of the sheets list =).
      This will display the list of sheets in a pop up vertically making it easier to browse.
      Once you find the relevant sheet, you can switch to it, and move it to the leftmost side so that it will be the first.
      Then you can delete any unwanted sheets by multi-selecting them using Ctrl or Shift buttons.
      Good luck

    • Yes, this can be a challenge in Excel. You can select the first sheet that was created by Show Report Filter pages, then scroll to find the last sheet, hold the Shift key and select the last sheet. That will select all the sheets between the first and last. Then right-click a tab and select Delete.

      My Tab Hound Add-in (mentioned in the article above) also makes it easy to find and delete sheets.

  • Hi! I’ve searched and searched but couldn’t find the answer to my problem anywhere. I landed on this page and decided to ask you.
    I’ve done Show Report Filter Pages many times before but every single time, I encounter this issue:
    Say I have data of employees in 10 departments. I pivot the data and add Department to the Filter area. I then create the 10 Department tabs automatically by selecting the Show Report Filter Pages. So now I send the tabs to 10 different department heads. The problem is that when they drop down the Department filter, they see the other 9 departments AND if they choose another department, they get to see the drill down data for that department as well! I would like to restrict each tab’s pivot data to just that department that has its name on the tab. Any quick, elegant, solid way to do this?
    Thank you!
    Update: I am using Excel 2013. I just saw your response to Dawn above. I tried the steps and it still doesn’t work. Thanks…..

  • Hi! I’ve searched and searched but couldn’t find the answer to my problem anywhere. I landed on this page and decided to ask you.
    I’ve done Show Report Filter Pages many times before but every single time, I encounter this issue:
    Say I have data of employees in 10 departments. I pivot the data and add Department to the Filter area. I then create the 10 Department tabs automatically by selecting the Show Report Filter Pages. So now I send the tabs to 10 different department heads. The problem is that when they drop down the Department filter, they see the other 9 departments AND if they choose another department, they get to see the drill down data for that department as well! I would like to restrict each tab’s pivot data to just that department that has its name on the tab. Any quick, elegant, solid way to do this?
    Thank you!

    • Before you split the pivot table, You can right click on the pivot table, Pivot table option -> Data Tab -> Retain Items deleted from the data source -> Change the option to “None”

      Though, I dont agree with the website Jon’s idea to split the pivot table in tabs.

      Excel Tab is one of the lowest efficient way to split and manage data. Try to use Pivot Table, Macro slicer or any other ways to manage the segregation of data rather than split into tabs.

    • Great question! You could use the Format Painter to apply the conditional formatting, then go Edit the rule to apply the formatting to the field in the pivot table. However, that is going to be a manual approach. This is probably best solved with a macro. I’ll add it to my list for future posts.

  • Hi Jon,

    It would be nice if the respective demo files are shown and can be downloaded.
    This tool is useful, thanks for creating it.

  • Hi Jon,

    Trying to incorporate Analysis/Options/Show_Report_Filter_Page feature in a macro. However, I had no luck using the macro recorder to get basic VBA code/syntax to get starter.

    Any suggestion on how to obtain this information.


  • Hi,I log on to your blog named “Create Multiple Pivot Table Reports with Show Report Filter Pages – Excel Campus” like every week.Your writing style is awesome, keep up the good work! And you can look our website about proxy free list.

  • I viewed this post several months ago and today it made me a rock star. A report of 75 subcontractors (1 worksheet per subcontractor), now only takes 5 minutes! After I am done taking credit for your awesome “time Saver” I will direct my client’s staff to your blog. 😉

  • This is very useful for me.
    one more question:
    how to create files for every single Sheet not using copy and paste?

  • Great feature which I already have a use for. I am anaylsing activities by term across the school here and being able to throw the data by term makes it easier to see. I did have a problem on one pivot table where the feature was not active, it was actve on my two pivot tables though. I tried re-arranging the data but the feature did not activiate on that one sheet. Is there a criteria to get the feature active or how the columns are arranged to make it active?



    • Hi Cheryle,
      The main criteria is that there must be a field in the Filters area of the pivot table. The Show Report Filter Pages button will only be enabled when there is a field in the Filters area.

  • Great feature which I found useful. I had three pivot tables from the same data source and found the ‘Show Report Filter Pages’ was not active in all the pivot tables. My data was looking at activities by term and by school year.
    Is there any additional criteria to ensure the feature is active?



  • Hi John,
    As always you sheared Amazing feature in Excel. I am sure it will help many.
    Thank you.

  • As always, you’ve introduced me to another great built in feature in Excel. This is great information. Thank you!

  • In regards to the question in the email: I hope to use this as well as incorporate some pivot table protection to send out usable data reports to my clients without having to strip* the data behind the original pivot. *Keeping only specific client’s which is very time consuming, because I’m still learning macros/vba. I work in research administration and your blog has been a great help! Thank you!

    • Hi Aly,
      Thank you for the comment. I just replied to Dawn in the comments above with the same question. I will try to do a follow-up post on this. There are a few steps you have to take to ensure the source data does not travel with the file.


  • This is handy as I work with students on a class list. We need tabs that have the classes on them. Then, the data on the tabs are the students. For basic data, not in a table, I can create the tabs, but still have to copy and paste the student information into the worksheets so it is formatted to meet the needs of the staff. This is a helpful tool to eliminate a repetitive step. There is also a macro to create worksheet names from a list.

  • Hi, Jon,

    Is there a way to remove the filter so that the individual sheets can be emailed to that person and not allow them to see data for other individuals?

    Thank you!


    • Hi Dawn,

      Great question! Yes, there are 2 steps we need to take.

      1. BEFORE running the Show Report Filter Pages, we need to disable the “Save source data with file” option on the Data tab of the Pivot Table Options menu. You can get to this menu by clicking Options in the same drop-down as the Show Report Filter Pages. It is the split button at the top of the menu. You can also right-click the pivot table and choose PivotTable Options…

      Here is the screenshot of the box we need to uncheck on that menu.

      Save Source Data with File - Pivot Table Options

      This will not include the pivot cache, which stores all the underlying source data in the file. When we copy the sheet out of the workbook, the source data will NOT travel with it in the background. Again, do this BEFORE running the Show Report Filter Pages.

      2. The next step is to copy the worksheet out of the workbook and save it as a new file. You can then send this file to the individual. You will just want to make sure the individual does NOT have access to the original file that contains the source data. If the original is on a shared server that the recipient has access to, then they will still be able to access the source data. Otherwise the pivot table’s source data range will not be accessible, and they will not be able to see the underlying data.

      If you want to be super secure and make sure you don’t miss a step, then I recommend copying the pivot table and pasting the values and formatting to a new workbook. This will ensure that no underlying data is moved with the pivot table because you will only be pasting the values of the cells in the new workbook. You will not be pasting the actual pivot table.

      I hope that helps. I will try to do a follow-up post on this. It’s a great question! Thanks! 🙂

    • Hi Rodolfo,
      The tab is named Options in Excel 2010 and earlier. It contains the same basic buttons including the Options button and the Show Report Filter Pages Button. The tab was renamed to Analyze in Excel 2013. Please let me know if you have any questions. Thanks!

  • Jon,

    This does not work on PowerPivot pivot tables. The option is greyed out. I have Googled it, and others confirm this behavior.

    Thanks for all your tips and tricks!


  • Holy cow! Thank you for providing this tip!! One instance I can immediately think of that I will use this is when I provide (annual) historical cost data to my internal customers for assisting them in preparing their budgets. I provide them separate pivot tables for each expense code. Each year I find something that I want to improve on and in the past, without this tip, I’ve had to recreate each of the pivot tables to implement my improvements. Not any more! Thanks again.

  • Again a great tip for getting around Excel. I already know how to use this, as I currently filter for each individual provider, print the copies I need, and then filter for next, while adjusting the columns for each provider. This will help a lot, I can just create the individual pages, and select and print all.

  • Jon, great tip. I’m guessing that because the pivot table sheets aren’t linked that if the original pivot table is refreshed to update the data that the new sheets aren’t updated. You would have to delete and recreate them. Is that true? Thanks.

    • Hi Bryan,
      Great question!
      All of the pivot tables will share the same source data range, and the same pivot cache. This means that when you refresh one pivot table, all of the pivot tables will be refreshed.

      Now there is a big caveat to this if you are adding new rows or columns of data to the source data range. If the source data range is an Excel Table, then you can add rows and columns to Table, and all of the pivot tables will include the new data on refresh.

      If the source data is a regular range, then the pivot tables will NOT include the new data. You would have to change the source data range for each pivot table. In this case it would be easier to delete the sheets and recreate the pivot tables.

      So, I highly recommend using Tables for the source data of your pivot table. There are some other benefits as well. Here is a video on a beginner’s guide to Tables if you are not familiar with them yet.

      I’m planning another post that explains all these benefits of using Tables with pivot tables. It’s a great question! Thanks again Bryan! 🙂

  • Dear Jon,

    I really appreciate your efforts in this regard, as I am also a very regular and proficient user of MS Excel, your Add-ins helps me every time whenever i am in need of support.
    Keep it up my dear virtual friend (i must say).
    Thumbs up and hats off to you…

    Thanks and Regards,
    Muhammad Zubair

  • Hi Jon

    Thanks for this sharing. It’s really a powerful function. I am just wondering how can I use it in the power pivot ? Seems this function is blocked.

    • Hi Kelly,
      Thanks for the nice feedback. You are correct. This feature is not available with PowerPivot tables. I will add a note in the article. I forgot about this limitation.

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...