Transcripts for the above video clip:

Pivot Tables 2007- Multiple Consolidation Ranges

With Pivot Tables, the ideal is to have a nice clean data base to include  in your Pivot Table. But it is not always possible to have a nice data base like this. So here is a very simple example, where I have a number of items all over the place that represent a form of a data base and you’ll see I have got a few more here. We need to include them in a Pivot Table and basically give us the ability to consolidate them.

In order to do this you can use something called Multiple Consolidation Ranges. In Excel 2007, it has become a little  bit harder to find the Multiple Consolidation Ranges item. In order to do it, just hold down your Alt key, click your D key and then click your P key and the old Pivot Table wizard will appear and you’ll see it asks for the where the data, the list the Excel data source or Multiple Consolidation ranges. This is the one we want, we will click on it, we can say Next. You can see it says you can create a Pivot Table that uses ranges from one or more worksheets.

The question it is going to ask is how many Page Fields do you want. Now it is important to understand this. What we have here is a summary and these two places are in a place called Gauteng, this is Natal. So at some point I want to be able to differentiate all the items that belong to one province, versus all the items that belong to another province. Now I know there are only two provinces here. So I want two page fields. You’ll see the option is to create a single page field or else you say that you are going to create the page fields. I am going to say next and I will create the page fields. We have now got this tool bar and we are going to work through it.

The first thing we need to do is specify how many page fields we want, and because I know there are two provinces I am going to click on the two and you’ll see immediately Field 1 and Field 2 is open for us.

The next thing to realize is that it is quite a powerful tool in the sense that, if you look closely you’ll see that the data bases are slightly different. This one has got fuel, oil and shop and this one has got fuel, oil, shop and car wash and this is back to fuel, oil and shop. Even the order doesn’t really matter. So all we need is click in the range and we highlight the entire area. OK, I’m just going to add it. I need to then tell it – where does this range fit? Is it field 1 or otherwise Province 1 or field 2, Province 2. So I am going to say this item called Rivonia belongs in Field 1. OK. So now we have set up and we have told it that this range is in Field 1 called Rivonia.  I can now go and identify another range. In this case this area here, I am going to add it. By default it stays on this one, and I am going to go here and say please change that to the word Benoni and the title here because I know that  Benoni  is also in Province 1. So now if I click on the first one, you’ll see that is Rivonia, go to Benoni. We’ll add a third one, this is going to be Durban, add it. In this case I know that Durban is not part of this province, but rather part of that province. I am going to delete Benoni, put Durban in Field 2. So now that’s   Rivonia, that’s Benoni and that’s Durban. I can keep going. So on the second sheet, I’ll have  a few more items – Pinetown – again I’m going to highlight it, add it  - Pinetown I know belongs to this province as well. So I’m going to put it into Pinetown and check that they are still aligned- it has gone to that sheet. I will click and highlight Brakpan and add it. Brakpan I know belongs to the first province. I am going to remove Pinetown here and I am going to put it back into….OK. We are going to add one more – highlight that – add,  this one here called PMB, add it and I know that PMB belongs to Province 2 – to Natal- delete that and we’ll call it PMB – and we must just check that everything looks like it is in sync. It is perfect so we go next.

We are now asked where we want to place the Pivot Table - either in an existing worksheet or in a new worksheet. I am going to put it in a new worksheet and now I’m going to say Finish. What you’ll see is it creates a Pivot Table and notice that Page 1 and Page 2 – if  I click on page 1  and remember I had the Field 1, Province 1 – there’s my Benoni, Brakpan and Rivonia. Page 2- Durban, Pinetown and PMB. So I can actually change these names. This I know is a province called Gauteng and this here is Natal. In the Pivot Table itself you’ll see it has pulled all the information through and managed to re-order it so I am going to put my Sales back to the  top and then it goes cost of sales and gross profit and in this case we don’t need the grand total. I am going to go to Pivot Table Options, just remove the grand total from the rows and the grand total for columns.

Now what we have got is a very quick Pivot Table which has consolidated all those details. If, for example, I just  want to see what is happening in Benoni, I switch Benoni  on there – go to Natal and say please show me the blanks there and you’ll see these numbers, will correspond to these numbers so 2082 –2082 for fuel. So you can now take multiple ranges and consolidate them into one Pivot Table and  from there you can use your normal Pivot Table features and functions.