And give it a thumbs up. Download the File Download the example Excel file to follow along.
(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. 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.
How To Create A Pivot Table In Excel For Mac
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. This allows us to filter the entire pivot table for a single sales person. Check out my for more on how to use pivot tables. I also have a 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. Select any cell inside the pivot table. Select the Options/Analyze contextual ribbon tab.
Click the drop-down arrow to the right of the Options button on the left side. Click the Show Report Filter Pages button. 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.
Then press OK. 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. 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. Fortunately, this is pretty easy to do.
We can select multiple sheets by holding the Shift key, then right-clickDelete to delete all the sheets at one time. Check out my 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. 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. 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 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 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. 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.
What will you use this technique for? Please leave a comment below with any suggestions or questions. Joyceline - November 30, 2018 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.
How To Build A Pivot Table In Excel For Mac
Please do you know a way to achieve this with the show report filter pages function? Karim Masarweh - May 17, 2018 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. Rafie - February 5, 2018 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? Update: I am using Excel 2013. I just saw your response to Dawn above.
I tried the steps and it still doesn’t work. Rafie - February 5, 2018 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? 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. April 24, 2017 Hi Dawn, Great question! Yes, there are 2 steps we need to take. 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. 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. 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! 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!
How To Create A Pivot Table In Excel For Mac 2016
Thanks again. 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 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!