Jump to content


Grouping the same data differently in two copies of pivot table


  • Please log in to reply
1 reply to this topic

#1 MikeyMike



  • Members
  • PipPip
  • 28 posts

Posted 11 September 2014 - 07:37 AM

Hello. I have a pivot table for data I'm  using. I created a new worksheet and copied the pivot table to the worksheet. On one worksheet I want to group the dates so that I can display them and months, quarter, and years. On the other worksheet I don't want the dates grouped at all. The problem is that when I group the dates on one worksheet, they are automatically grouped on the other worksheet. How can I treat the same date information differently on different worksheets within the same workbook? Thanks.

#2 Jonathan


    Forums Administrator

  • Administrators
  • 888 posts

Posted 11 September 2014 - 08:03 PM

Hi Mike,


By default, Excel automatically links Pivot Tables to the same 'data cache' if another Pivot Table exists that points to the same data.  This results in what you're seeing, where the same grouping settings are applied to all pivot tables that point to the same data.


Earlier versions of Excel would prompt the user before doing this, but that prompt has been removed in more recent versions.  Fortunately, it's still possible to create multiple unlinked pivot tables by using one of Excel's 'hidden' features: the Pivot Table Wizard.


You can access the PivotTable Wizard by adding it to your Quick Access Toolbar, using the same techniques as are used to access the 'hidden' Speak Cells features in the video lesson: Expert Skills Lesson 4-16 Use Speak Cells to eliminate data entry errors.  You can also access it by pressing <Alt>, then <D>, then <P>.


The PivotTable Wizard is the 'old' way of creating Pivot Tables. When you create a pivot table using the wizard, you will see this prompt:




In order to create a pivot table that is separate from any others, click No on this prompt.  You should then find that you can group its data completely separately from any other pivot tables.


As far as I'm aware, this is the only way to achieve what you're looking for in the more recent versions of Office.


For the benefit of other users, Pivot Table date grouping is fully explained in the video lesson: Expert Skills Lesson 5-18 Group by Date.


I hope this helps, but please feel free to reply if I can be of any more assistance.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

Also tagged with one or more of these keywords: Grouping

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users