• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

2 pivot tables in the same worksheet, changing the grouping from days to month changes on both pivot tables

Excel 2013

I have 2 pivot tables in 1 worksheet tab.

2 columns in each.

Grouping on date in the left column,  sum of cases sold on the right.

The first pivot table has the date group by days = 7

When I go to the second pivot table to change the grouping to monthly it changes the grouping in both pivot tables.

How do I prevent this from happening?

In this worksheet which I call data I have a web query that goes out and grabs the data.

I want to have a weekly pivot table and a monthly pivot table for viewing in table format and then I want to generate graphs off both pivot tables.
0
rjohnsonjr
Asked:
rjohnsonjr
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

By default, Excel will pull the two pivot tables from the same pivot cache. Grouping by a date field will be applied to the pivot cache and will affect all pivot tables that are drawn from that pivot cache.

To create a pivot table that is independent of the first pivot table, you need to use the pivot table wizard. You can use the keyboard shortcut Alt-D-P to start the pivot table wizard. You may get a warning about existing pivot tables and saving memory by using existing pivot tables. That is where you need to tell Excel that you want to create a new pivot table. If you click "No" the new pivot table will use a different pivot cache and you can create different groupings on that pivot table.

Be aware that for large data sets this will have an impact on the workbook size and memory, because both pivot caches will be loaded into the memory.

cheers, teylyn
0
 
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for teylyn's comment #a40525006

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now