Tip:
Highlight text to annotate it
X
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.