Transcripts for the above video clip:

Pivot Tables 2007- Get Pivot Data

Occasionally, when you have a Pivot Table like this, you may want to extract certain of the numbers in order to use in other calculations within your spreadsheet. What you may have noticed is, if you click, for example, on that cell there, and type equals (=) and let’s say we want to extract day 7 (Sunday). When I click on the pivot table, you may see something like this where a formula is developed even though you weren’t trying to develop one (GETPIVOTDATA).

We need to explain this and just explain  how it can be switched off and why it can also be useful. What you’ll see is, when I click Enter it has gone and pulled out that number but using this GETPIVOTDATA formula. You need to understand what the GETPIVOTDATA formula is doing so we are just going to activate the Function Wizard and just understand what it is doing. You can try and work through this function on your own via the Function Wizard but I find that it is easier to actually click on a relevant cell and then look at what it is doing. So what you’ll  see is the first thing it says is Data Field, the name of your data field to extract your data from and you’ll see it says “Bill”. Because we’ve clicked on this cell here, it recognizes that the data it is looking at is the Sum of Bill so it knows that it is the Bill field – notice the inverted commas so if you wanted to create this yourself, you need to put the inverted commas.

The next item is the pivot table- so you’ll see this  is the reference to a cell or a range of cells in a pivot table. It needs to know where the pivot table starts so you’ll see Excel has automatically gone to cell A4 and said that cell is linked to a pivot table. The reason it does that cell is because as you move the pivot table around or you change items, make it bigger or smaller- we do know that that cell will always exist within the Pivot table.

The next item is something called Field 1 and you’ll see this is the field to refer to – if you read it, it  says Field 1, day of week so it knows that it is looking for the bill and it must go to the day of week- we know that that’s the day of week. And what must it do with the day of week? Well here in item it says it must extract the number 7 from it. And as you see, if you click on it, another field appears so we can keep doing this. But for now let’s  say OK. So you can see it has come here and extracted the 138.96. We can actually re work this so let’s go into the GETPIVOTDATA formula and change that to a 6, for example, because now I want to pull through Saturday. Let’s see if it gets it right – 128.73- and there’s 128.73.

With this knowledge now, we can actually build a little bit of intelligence into this formula. So, for example – over here I am going to put day of week and for now I’ll just put the number 6 in there. If I can then go to my “Get pivot data” and instead of referring to hard coded  numbers, I am going to change that number 6 to say – rather look at whatever is in that cell there. When I say OK, you’ll see it is pulling through that number there. If I go here and change this to, for example, a 1, the “Get pivot data” immediately knows  it is looking elsewhere and it looks up here  - 435. The benefit of the Get Pivot Data as well is that no matter what happens here, this will always be correct. So if someone comes here and maybe sorts this – moves the one (1) down to there it doesn’t matter. What it is doing, it is going, looking through the various bits of information, looking for the day of the week that is a one (1) and pulling through the relevant information.

Let’s now make a slightly more complex pivot table- so we’ll just delete that. Let’s take Destination Network and maybe put it in the Column Labels. So what we now have is Day of the Weeks, Destination Network and a whole bunch of numbers. For now I am going to go and say Please give me  = we can choose any one – let’s choose that cell there. Notice that the form is a little bit bigger, I am just going to push Enter for now and let’s just look what the formula is doing. Again the first thing – get the data field bill. Let’s put in the information from here- where is the pivot table? It is saying the pivot table is attached  to the cell in A4. The first thing – destination network – Vodacom. So it knows that  it must look in the destination network and look until it finds Vodacom. The next field it’s given – day of the week- you’ll see the item is 5 so that now knows it needs to find Vodacom and then find the 5 and bring through that particular number.

Let’s now build a bit of intelligence in, let’s put the Network here and we’ll type Vodacom, day of week here and let’s put the 5. We can now go back into our GETPIVOTDATA function and replace the word Vodacom with a cell reference and similarly replace the item 5 with item 2, with cell reference- say OK. Nothing much has changed but now I can, just as long as I get the spelling correct, change this to MTN – Enter- you’ll see the number has changed to 32 and if you look you’ll see the 32 ties in with that day of the week. So we can change that to  a day 1, for example, and you’ll see it goes to 108 which is that number there. So you can use the Get Pivot Data to extract information straight out of a pivot table. You can try and work straight from the Function Wizard but my suggestion is rather click on a cell,  (=) equals to one of the cells you want and then work through the syntax in the Function Wizard to make it do what you want to do.

Now occasionally you don’t actually want the GETPIVOTDATA function to operate. Let’s say that you know your Pivot Table is never going to change – it looks exactly like this- and you want to pull through this grand total.  But no matter what you do, if you say = and you touch the pivot table immediately you have got to Get Pivot  Data. There are two ways around this- the first way is you can’t click on the pivot – so I can see that this cell is in cell G13, so I can now go =G13, when I say Enter – I get that number and it will always refer there. So that’s where it is looking. The risk of course, with this- if, for example, I change this and I remove the 2 and I say OK, although it is no longer there it still looks at that cell and that is incorrect. So that is one way of doing it. But if you permanently want to do it –every time you  are going to be able to =, click on the pivot, but not  get the GETPIVOTDATA, you can change your options. The way you do that – click somewhere in the pivot table, go to the Pivot Table Tools and you’ll see under Options there is something called “Generate Get Pivot Data”  and it is ticked. If I untick it, you’ll see now it is unticked, I can now go to my pivot table and when  I say  = that cell, you’ll see the formula is just the normal reference. If you need to switch it back on, Pivot Table Tools, go to Options, Generate Get Pivot Data, and you’ll see it is back.