Create Multiple Pivot Table Reports with Show Report Filter Pages - Excel Campus
29

Create Multiple Pivot Table Reports with Show Report Filter Pages

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 29 comments
Roy - July 7, 2017

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. 😉

Reply
Wenning - June 13, 2017

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

Reply
Cheryle - April 23, 2017

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?

Thanks

Cheryle

Reply
    Jon Acampora - May 25, 2017

    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.

    Reply
Cheryle - April 23, 2017

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?

Thanks

Cheryle

Reply
Chaminda Basnayake - April 22, 2017

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

Reply
Rhonda - April 21, 2017

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

Reply
Aly - April 21, 2017

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!

Reply
    Jon Acampora - April 24, 2017

    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.

    Thanks!

    Reply
Javier - April 21, 2017

Tips that I’ve never heard about it. Awesome. Thank you

Reply
Ahad - April 21, 2017

Just awesome……….

Reply
Ruby - April 21, 2017

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.

Reply
Dawn - April 21, 2017

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!

Dawn

Reply
    Jon Acampora - April 24, 2017

    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! 🙂

    Reply
RODOLFO LANDIN - April 21, 2017

Is the Analysis Tab only available in Excel 2016. Or could we download an add on for 2010 also?

Reply
    Jon Acampora - April 21, 2017

    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!

    Reply
Gayle Snedecor - April 21, 2017

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!

Gayle

Reply
    Jon Acampora - April 21, 2017

    Hi Gayle,
    Thank you for the comment! I forgot about that. I will add this limitation the notes section in the article.

    Reply
Heather - April 21, 2017

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.

Reply
Daryl - April 21, 2017

Awesome !

Reply
Lisa - April 21, 2017

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.

Reply
Bryan - April 21, 2017

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.

Reply
    Jon Acampora - April 21, 2017

    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! 🙂

    Reply
Muhammad Zubair - April 21, 2017

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

Reply
Svetlana - April 21, 2017

Your effort is invaluable. I used the Pivot Table to evaluate the publication activity of my research Institute.

Reply
Kelly - April 21, 2017

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.

Reply
    Jon Acampora - April 21, 2017

    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.

    Reply
Ruud Weber - April 21, 2017

Wow that is great I did not know.

Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x