in this video we're going to talk about pivot tables and get pivot
data function in both Excel and Google Sheets
it's pretty much the same exact function so if you know how to use it and one you know how to
use it in the other so I have this data set so I'm going to go ahead and create a pivot table
in both Google Sheets and Excel really quickly here so I'll go under insert to a pivot table
create it's going to add a new worksheet under values I'll have sales
and I'll use some function in that and on the rows I'll use region for now and there
it is our total sales by region the same thing in Excel go under insert pivot tables
hit okay take sales put it in values take region in rows let's quickly format these numbers
to currency just to make it look nice so first of all let's just understand why we need get pivot
data function to begin with and how exactly we actually use it so the thing with pivot tables
is because this data right now is connected to this particular Source what's going to
happen if you change your source and update to different regions this report will update as
well and as it updates what's going to happen these regions may not be in the same position
so for example if you decide someplace to do some sort of formula using this grand total
like 20 percent of that what could happen is when this Updates this grand total can actually move a
line down or a line up and then all of a sudden you're not pointing to the right cell anymore
and that same idea applies if I use any other number here to do some sort of calculation
to avoid that issue we can use get pivot data function and the way we do that we use equals
get pivot data function and the first part of this function is the value and the value is the
actual value field from the pivot table you want to grab if you don't understand the difference
between value fields and non-value Fields in pivot tables I recently made a pivot table video using
data Studio I really encourage you to take a look at that but if you already know what a value field
is in our case right here my value field is sales so I'll go ahead and provide that as text so in
quotes comma and the second required argument here is any cell from the pivot table so basically you
want to just give it a starting point where it's always going to be there so typically that would
be the first cell in the top left corner of your pivot table because you're always going to have
something in there so at this point if I just go ahead and close parentheses hit enter you'll see
I get this amount right here and that same exact thing works the same exact way in Excel right so
if I go to my Excel spreadsheet and paste that same formula here not in my case the pivot table
in Excel is not in A1 it's actually in A3 so I'll change that upper left corner hit enter and there
it is we get this total amount now that's how we get the overall grand total now if you want
to get one of these numbers in the middle let's say the one for West Region then what I can do
I can go back here and add a comma and we follow that by the name of the field and what that field
means let me hit Escape for a second is the column from which these values come from so in this case
that's this region so I'm gonna go ahead and do that so I'll do comma and again that's text region
like so comma and then you have the actual item name so the item name means the actual value you
have from here so for example if I want West I'll just type West and if I'd enter I'll get
that amount as you can see 4. West now because these are text values you can type those values
in a cell and refer to those and what I mean by that I could go here and type West and then go
to this cell and instead of hard coding this I could just point to this D4 which is where the
value actually is and if I enter you'll see I'll get that same amount now the advantage of this if
you wanted to get all of these Maybe I could copy and paste those values here and now because the
first one is Midwest this number is Midwest number and now to drag this down I'm going to make sure I
lock this A3 cell so I'm gonna do dollar a dollar three this is that first cell in the pivot table
upper left corner which is always going to remain the same so we'll just keep it the same by using
dollar signs and at this point if I just drag this formula down you'll see I'm gonna get the right
value for each one of these regions because what happens if this goes to the next line it points
to the correct region and we're going to get our values and at this point it doesn't matter when
you update your data Maybe these things will move up and down or whatever it doesn't matter because
we're still gonna go find the right value field in the right region column and we're going to match
this which is in this case Northeast now in this particular case in my pivot table I only have one
row breakdown like this so let's go ahead and delete this and I'm going to go back to this
pivot table and let's add something in columns too so I'll go ahead and do something like sales wrap
and now I got my wraps listed in columns and regions in rows as a matter of fact
I'm going to switch these I'll put this here and this here like so so let's say
this is your pivot table and you want to point to a particular value from here
so yet again if I just go here and do my get pivot data function it doesn't matter where exactly but
I'm going to do it here we're going to start with that same sales value field comma we're
going to point to that same upper left corner of the pivot table I'll add some dollar signs here
comma then the field I'm pointing to so again let's just start with that region example
and then comma and then the particular region I'm after so if I do West
this should give us the total for west region
this is the total as you can see which is this total down here 2.5 million or so let's just
make this formatted like this so it looks better so that's this amount down here now let's say you
want to grab the actual number for Grace here so to do so you'll go back to this add a comma and
then you use the next field you want to point to and the field I'm going to hit Escape for a
second the field name here is see sales wrap so I'm going to go ahead and go back to this and do
sales rep comma and then the actual sales rep we're after so I said I wanted to do Grace
I'll do Grace hit enter and that should give us the amount for grace in that particular region
now even if I go back to this particular pivot table now and move regen say to rows
so now everything is in a different shape in the pivot table right but this number is still going
to work it doesn't matter because we're still going to grab the right value and then again
if you don't want these values to be hard coded maybe you want this to say like West here and
Grace here you can of course go to your formula and point to this West here and to this cell
instead of Grace I'll point to this cell G6 which is the one that has Grace I hit enter I'm going
to get the same amount and at this point if you wanted to get let's say a few other people here
let's say all of these are in west region you can drag it down
that would work just fine and if you wanted those same names let's say in Midwest region two
then you could of course do that too and if we just double check for Jennifer Midwest
which should be see Midwest here Jennifer hundred five thousand eight hundred fifteen eighty seven
and that works just fine as you can see now all of this works exactly the same way obviously in
Google Sheets now let's say you work with dates so dates are not going to be probably identical in
Google Sheets and Excel it's still going to be the same idea but values may not match the same way
so I'm going to give you that difference so you can see what I'm talking about so if I take this
data right now I'm gonna get rid of this and I'll take this and I remove this region and sales rep
from here and I'm gonna take this date column so just to refresh your memory here this is my dates
I'm going to go back here I'm gonna take that date column and I'm going to move it to rows and what
Excel does it will by default make this groups for you see like by year by month now I will take this
quarter out of here and I'll take this date out of here so I'll just have years and months and
I'll probably I can open these but I'm probably also going to move years to columns just to make
this easier to read so there it is we got year to year by month now notice here this is called years
space open parenthesis date close parentheses so whatever that call name is here and here as well
whatever you have now don't look at what I have in this particular case you're gonna
have to check what you have in that particular position and that's what you need to use so now
if you want to grab a value from this pivot again you're going to use that get pivot data function
and again the field is sales the value field the cell is this one upper left corner so I'll use
dollar signs just in case I want to drag this down comma then I'll start with Fields so for example
if I do year space date like so comma and then I'm going to point to the year I want to use and for
this I'm going to do 2021 so right now let's just close and hit enter and see what happens
so as you can see I got an error so if I have an error that means I made a mistake
someplace here let's go find out what this should have been so I'm going to click on
this see right here it's not year it's years so I'm gonna go back here and switch this to years
and now of course it works and it gives us the total for that year and if you want this to be a
total for a particular month let's say for June then you go back here and before I do that let
me just double check it's called months date so I'm going to go to this and I'll do months
like so comma and then I'll use June
and there it is that should be our total for June in 2021 [Music] and of course it
works so this is how I got to this in Excel now let me do this in Google Sheets 2 which
is honestly not going to be different but you just have to pay attention to values
so if I go to Google Sheets here I'm going to get rid of this and I'll take this and remove
this region out of here I'll replace this rose with that date column now Google Sheets doesn't
automatically group your dates but you can still manually group them I can right click go create
pivot date group and then you can choose how you want this grouped so I'll do year month
so now you can see in rows here I have date and then Dash and then year Dash month like this
so you want to keep in mind all the spaces all the special characters all those things
now again if I want to grab a particular value here I can just go get pivot data
and then I'll do sales column I'll point to this cell I'll use some dollar signs here
just for good practice comma and then the column so in this case this is the
column name so that would be date space and then this Dash and then year month
comma and then finally the actual value I'm after so let's say I want to do this 2020 December so
I'll do 2020 Dash d e c of course this is all a single line it just goes to the next line just
because I'm zoomed in so if I'd enter you'll see I'll get that value 321 995 right here
that works just fine now if you want to do it more like Excel when I have a separate year and
a separate month you can do that too so if I get rid of this for a second so what I can do here I
can go back here right click on this go to my group options and just do something like month
and then I can add that date column one more time and take the second one and group it by year
and then we could take one of those and move it to columns section let me zoom out a little bit here
so I'll take this year move it to columns this way it will look pretty much identical to how
that Excel pivot table looked like and yet again see we got this date Dash month and date Dash year
so we need to refer to those so if I just go here and do equals get pivot data and I'll take that
oops let's get back to this I'll take that sales value field and I'll point to this
cell so as usual by default if I just do this much that would be your grand total meaning this
total down here then I'm gonna go back here and add a comma
let's do year first so I'll take date Dash year again don't forget spaces have to match
exactly the same way and let's do the value so I'll do 2021 so I'll hit enter apparently
I didn't get the name of the column right so I'm going to go back and fix that see I have
parenthesis open like I did in Excel there's no parenthesis here so we're gonna do this
and there it is now you can see this gives us the right value for this year and of course
if you want to get a particular month you can just go ahead and do the other column
and I'll do it for June right now and as you can see that works just fine I'm
gonna X Out of This sidebar because it gets on my way at this point I have my
values I have my Pivot data and of course as usual if you want to have some sort of
values here so let's say I'm gonna go June and I'll just do a few months here
then we can of course go back here and instead of hard coding 2021 here in the formula we can
point to the cell and then instead of hard coding June here we can point to this cell
and then we can drag this formula down and that should give us the right month year combination
total right there one last thing I'm going to mention here if you update your field names you
need to make sure you come back and update them in your formulas too so what I mean by that right
now this here is called Date Dash month so let's say you go here and just rename it to like months
so now you're going to see I get a bunch of Errors because we don't have the right column name so I
can go here and instead of having this date month thing now I have to use that new name months
and if I drag this down to update the rest of these you should see that they work fine
and there it is that's your get pivot data function let you know if you have questions
and as always thanks for watching please subscribe and I'll see in the next one