Creating Multiple Pivot Tables All At Once

If you are using Pivot Tables, it is extremely easy to create multiple pivot tables from your original pivot table by using the Report Filter.
The Report Filter which displays at the very top of the pivot table  is the best feature within the pivot table as it controls all the information seen in the pivot table. It also allows you to easily generate multiple pivot reports.



In the example below, Ship Via is showing as the Report Filter. Currently, I am seeing the total orders for all the ships but I can easily generate a separate pivot table for each of the individual carriers.

1. Click on a cell in the pivot table

2. Go the Options contextual ribbon





3. Select the little down arrow next to Options  (in the first group on the ribbon),

4. Select Show Report Filter Pages…..

4. Select the Report Filter that you want to view in detail. We only have one report filter here so you would select
5. Click OK




It may seem as though nothing happened however if you look down at the sheet names in your file, you will see that Excel has created worksheets for all the individual carriers. In my example, I now have a worksheets named UPS, Parcel Post, FedEx etc and when I select the sheet named FedEx I have a pivot table just showing the orders FedEx. If I click on the UPS sheet, then the pivot table displays just the order information for UPS.

Here is another quick example: If the report filter allowed you to select between 4 Salespeople, Smith, Jones, Brown and White and the Report Filter instead of displaying All was displaying Smith, you would see 3 sheets at the bottom that Excel created – Jones, Brown and White. (Jones would not display as a new separate sheet as that is already displayed on the “original worksheet”.

This can be a big timesaver.