Best Microsoft Excel Bloggers

Friday, May 20, 2011

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.

2 comments:

  1. the menu "show report filter" is not active, any suggestion?

    ReplyDelete
  2. I have not run into this however my understanding is that if you are pivoting data externally from an OLAP data cube that the show page option (report filter) will not work in 2010. What version are you using? Are you pulling data externally? Anyone else run into this problem?

    ReplyDelete


Ms. Excel- Resident Excel Geek