How to drag Getpivotdata Formula
Excel GETPIVOTDATA Function to Pull Data from a PivotTable
0:00 / 0:00
Quality:
Sharing buttons:
hello and welcome to this video tutorial
from computer gargoyle comm and in this
video we are going to look at how to use
the get pivot data function of Excel now
the get pivot data function is used to
query and extract data from a pivot
table so it's kind of a pivot table
lookup that's what it is
now this feature is turned on by default
in Excel and that is because it can be
extremely useful for what we need it to
do so take this simple pivot table I
have at the moment and I'm going to use
this function to extract the data that I
want and we'll see some of the benefits
of why you might want to use it but then
going to see a second example of where
it doesn't really help us and we want to
standard reference instead and we look
at how we can turn this feature off okay
let's look at the benefits of it to
start with though and if I just click in
any cell of this spreadsheet and start a
formula by typing equals and click on
one of the cells of the pivot table and
immediately in that cell they write the
get pivot data function and what this is
telling me at the moment the first part
of the function tells me that it's
extracting data from the total sales
value field so just to make sure we're
happy that is the name of the filter the
numbers you can see it mentioned and top
left there's a name of that field
that's my values area if you will now a
free that is a cell within the pivot
table any cell it can be now that's the
cell in the top left hand corner and if
you ever find yourself writing this
yourself you're encouraged to click on
that one because it's reliable and if
the pivot table changes in height and
width we know that cell will be part of
it
theoretically though it could be any of
the cells in here doesn't have to be a
free it then just chooses the field to
use it from so you see you've got filled
one item one field
to item two I'm just waiting for this
box down here when I mentioned what I
just mentioned so in the product
category field it's got to be a grains
and cereals product from the year's
field it's gotta be the year of 2012 and
from that extract the correlating value
from total sales value and that's what
it does is very specific in what it
wants but the benefit of that sowed it
you know a PivotTable generally change
over time it's gonna maybe get bigger
maybe it's smaller maybe people are
gonna sort this data by like the values
or something and that's going to move
the values around and these things will
happen and so by using a function like
get pivot data it will always find you
what you want it would always find the
grains and cereals value from 2012 for
example no matter where it is because a
lookup now that's a very good thing
about it a lot of like kind of haters of
get pivot type table data get pivot data
will say that because it's so specific
it's so structured that is a negative so
I've got an example here where to combat
that in cell g4 I've got a little drop
down this little data validation list of
the different products and let's imagine
that this is probably on a different
sheet this this is a whole different
report in a typical real world example
it's on the same sheet here silicon for
learning purposes see what's going on
but people want to be able to choose a
product and then automatically return
what the total is and because I've got a
pivot table it makes sense to use that
rather than interrogating the big list
so in here if I was to go ahead and do
that if I type equals and I've got
produce at another moment so let's click
on the grand total for produce and it's
going to write this in extract data from
total sales value a free is a cell in
the pivot product category field produce
and that's what I wanted to do if I
press Enter
surprise surprise it's got it from that
cell but that sounds not be 11
no no no it's not 11 that is the produce
data the grand total produce because get
pivot data's involved and that's good
but I want to be able to choose this
drop-down list above choose confections
or choose dairy and I wanted to change
at the moment it's not so let me revisit
my formula and where I've got produce on
the end and these inverted commas I'm
going to select cell G 4 and press enter
and now when I change that drop down
list above to condiments or to produce
or to seafood you can see that value
changing so I've given my get for the
date or a dynamic element by referring
to that cell and by doing things like
that you can make the most of that
function it can be really helpful
function for you in your kind of summary
sheets your reports your dashboards
whatever you've got going on if you have
got pivot table data it can help you
extract the data you want from it but
yet you can still keep a dynamic nature
to it it doesn't have to be that
structured now that's an example where
it can be helpful but let's click on
sheet2 here and have a go example where
maybe it's not going to be so helpful
for us so same data but a different
pivot table got a filter for a country
at the top currently Switzerland in
there and I've got these values sorted
largest to smallest so even if I change
it so I've got dairy at the top
condiments at the bottom I've changed a
filter for Argentina so different here
confections at the top grains at the
bottom all the data is moved around is
actually a bit smaller as well there's
only seven categories but I switch that
back now which row Elevens got the total
in if I switch that back to Switzerland
the totals in 12 so it's a completely
different table now so let's imagine for
some reason possibly on a different
sheet and that I want to return the
value from the best product at any point
in time in the pivot table now there's
no point in me at the moment
usually might get pivot data because
that will put dairy products in so if I
was to type equals and click on that
value it writes my get pivot data
functioning get the dairy product
category from tell sales value but I
don't want dairy product I want whatever
the best one is and because I've got
that sort going on that could be a
different product it depends what the
country is and it also depends what's
doing well you might still be
Switzerland but in a month's time it's a
different products doing well so that
doesn't really help me right now so what
I'm going to do instead is simply type
it in I'll put equals I'll just type B
for and that will allow me to use a
standard sheet reference instead of one
of those get pivot data functions it's
not helpful for me right now and if I
press ENTER and I get my value and then
I filter my pivot to Argentina for
example now I'm returning whatever the
best-selling product is I've always got
the best-selling product not dairy just
not always gonna be dairy and that's the
example where it's helped me to avoid
get pivot data now if that is what
you're going to be using the pivot table
if you don't think you go in to use get
pivot data and it is to standard it is
the default thing so you've got to type
in your references to kind of avoid it
if you click in there it's gonna use it
what I could do is I could click my
analyse tab and the pivot table tools at
the top it may say options if you're in
2010 version of Excel and on the far
left I've got an options drop-down list
and in there generate get pivot data and
it's ticked if I turn it off just click
on it to toggle it off
now when I use references and click
inside the table it uses a normal sheet
reference so if you don't think you're
going to use that function you can
disable it or stock completely disabled
but by default you can turn it off that
is I guess your choice and it works for
me in this example but remember in a
previous example when I was talking
about how specific it was from the years
and from the categories in the pivot
table it's got a change in height and
stuff changing width these are different
years there'll be other years coming or
if these are days of the week or even
weeks it's going to change more
regularly you may need that get pivot
data to get the specific thing you're
after I hope you found this video useful
please check out some of our other video
tutorials on our YouTube channel and
come check us out at computer gauger com
you
