Introduction

It is pretty easy these days to set up and manage a pivot table (PT). It is not too difficult to create a pivot chart (PC) either. There is a problem, though, when you might want to create two or more different charts from the same PT. This page explains two approaches to dealing with this issue.

Ordinary PT

Let’s start by creating an ordinary PT. Go on, then! You’ve got some data and you know you want to analyse it so off you go! You might have a PT like this:

and a PC like this:

You can see how I did this from this overview screenshot:

Good! Now imagine we want a new time series PC, with the PT revised as you can see below; and to do this I had to create a new PT based on the existing database that I also used for the first PT and PC:

To make the changes we see so far, you just change the groupings for the new PT. In the first case I grouped the dates under … Years + Quarters + Months

In the second case I grouped the dates under … Year + Days

The Problem

The problem is that when I change the grouping in my second PT and in my new PC, the first PT and PC also change. This happens because Excel anticipates your memory and other system needs and bases both PTs and PCs on the same cache of data. It always does this until you tell it not to.

The Solutions

Delete or Add a row

I will present this solution with a warning: it is a bit clumsy if you are dealing with a small database because what you do is to change the database by changing the input range by leaving off one row of data. For example, if your original database is in Sheet1!A1:A20, you would create a new PT using the range Sheet1!A1:A19. Therefore, two PTs are not equivalent and Excel will create a new PC for as you change the grouping in the PT. The example I am using here has more than 5,500 rows of data so deleting one row is not so bad but be careful with this approach if your database is much smaller.

You can, of course ADD a new row the database and Excel will create the new PT and charts for you but look at this inconvenience:

You can see that there are blanks in the PT now. Of course, this is not a surprise and you can easily deselect them but be ready for a mess if you don’t!

Use the Old PT/PC Wizard

Excel is over 30 years old now and we are in the 21st century but sometimes we need to turn the clock back to the days when the men wore polyveldt shoes and the ladies wore crimplene dresses!

  • Open a new worksheet
  • Alt+D+P … that is press the Alt key … keep it pressed then click the d key then the p key and you will see this:

  • Feel free to experiment with this wizard but for now we are going to accept these defaults by clicking Next
  • In the next screen enter the SAME range for the database you are already using … Next
  • As soon as you click Next, Excel realises you are using the same database and presents you with this:

  • You have to click No for Excel to create a separate PT for you. You can see that the downside of the selection is that you will use additional memory and your file size will be bigger … but the benefit is that we can now create our new, different PC.
  • Finally, Excel asks where you want to put your PT with this screen:

There you are: create your new PT now and your new PC and they will not interfere with your existing PT and PC.

Conclusions

Every now and again we might want to create two or more charts from one database via a pivot table. This page has shown two ways of doing that. There are other ways, especially if you are using external data sources; but for now this should be enough for most of us! Another way is to create the pivot table and then copy and paste from it … but you lose flexibility that way.

There is no Excel file to download for this since it is best you work on your own data and your own PTs.

 

Duncan Williamson

18th August 2017

 

%d bloggers like this: