How to Group Two Excel Pivot Tables Differently Even if Based on the Same Data

Recently I was making a report in Excel and I needed to group the same data in several pivot tables a couple of different ways.  So I made one pivot, grouped it, made the second pivot, grouped it, but when it came time to admire my work, saw they were both grouped alike.  In other words, they grouped in lock step -- I couldn't group them differently. 

Then I had to teach myself how to group two pivots of the same data in different ways.  The key is to use different data caches for each pivot. 

To make the pivots use different caches, you need to use the Pivot Table Wizard.  In Excel 2007 and later, the Pivot Table Wizard isn't on the Ribbon so you'll have to use a keyboard shortcut to start the wizard and create a pivot table with its own pivot cache. Here are the steps to do so:

  1. Hold down the ALT key and then depress D and then P. This combo starts the Pivot Table Wizard.
  2. Select "Excel List or Database" and click the Next button.
  3. Select the data source for the pivot table and click Next.
  4. Next Excel shows a message box. Be careful! If you click Yes, it'll use the same pivot cache that you already set up for the first pivot table, which saves memory prevents you from creating separate caches upon which to make different groupings for separate pivot tables.
  5. In that same dialog, click "No," so Excel will create a separate pivot cache for the new pivot table.
  6. Complete the wizard and then set up your new pivot table.

You can now change the grouping each pivot table without affecting the other pivot tables that are based on the same data.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.