To give you an overview, we have a table of source data here and it's a
listing of transactions of people selling pet supplies. Then we created
a pivot table and it has two values. It has some of the total price and a sum of
the quantity. This table is telling us the dollar value of selling these cat
toys and how many we sold. If you come over to the right and you're just
wanting to tell a little story in these boxes here you just want to extract
that leashes that we sold. Let's break them out between brick-and-mortar and
online. What you think you would do, and it will work if you don't change
anything, is you would just hit an equal sign and then come over to the pivot
table and let's say we want leash sales for brick-and-mortar, left click in cell
D11, hit enter, and there you go. You have
$2,537. That matches here. If you want it for online, you can take
your mouse, go into the lower right hand corner of the cell that you have
selected until it becomes a plus sign, hold your left mouse key, and drag it
down. You have both of these values coming up. If that's what you wanted, and you're
never going to change this pivot table, then you're fine, just leave it like
that. But, if this pivot table is dynamic, I'm going to show you how it would break
these formulas and then what you can do to get around that. First, I'll
left-click into the pivot table and that will bring up the pivot table editor on the
right hand side. We'll scroll down to these two values. So we have them in this
order: total price and quantity. Let me just left-click on one and I'm
going to take the quantity and I'm going to put it above total price. This is
going to rearrange my pivot table and it put the quantities on the left and the
total price on the right. If you look at my formula here that was pulling the
price, now it shifted the quantity. The formula didn't change. If I left click in
it, it's still pointing at D11, but D11 has changed. It's not
returning the same amount. So there is a way to get around this
where if you build a formula looking at the pivot table, you can specify exactly
what value you want it to return. I'm just going to do ctrl Z just to get things
back to how they were. We'll come down into cell H10. I'm
going to type the = sign. That's how you always start a formula, and then I'm
going to start typing the name of my formula which is going to be GETPIVOTDATA.
I'm going to come down, left click on that with my mouse, and Google Sheets,
like it always does, is going to give us this little helper text to try to help
explain what's going on. The first field that it's trying to look
for is a value name. The value names are the darker ones here at the top. SUM
of Total price or SUM of Quantity. I'm wanting SUM of Total price. Since this
is a string, you start it with quotes. I would say SUM of total price.
That's not case sensitive, but I capitalized it the right way just so it
looks nice. I think that's important, right? Then you do a comma because
we're ready for the next value and the next value is just any cell in this
pivot table. The best practice is to choose one near the upper left hand
corner because, if you change this pivot table, a lot and it shrinks and grows.
You choose a value down here in the lower right hand corner, it may not be
part of the pivot table if you shrink the pivot table. So, we're just going to
specify B4 and we're going to put dollar signs before the column and dollar sign
before the row. That just fixes a reference and since our pivot table has
a little bit more detail below, you're going to need to specify these two
columns. So you're going to say the product is Leashes and the location is
Bric and mortar. We need it to be Leashes at the Brick and mortar location.
Leashes, if you look over to the left, are the product and the Brick and mortar
is the location. Of course, we're looking for let's do our quotes
again. Product , Is leashes and leashes is the value that I typed in H9.
I could type in Leashes, but I'm going to make this formula
a little bit more dynamic because if I come in here and I want to just type
over Leashes and type Fish food, this formula will still work. We're going
to say, instead of typing Leashes, we're going to click on H9 and I'm
going to go into this formula and I'm going to fix the row so I can drag this
formula down. You'll see that in a minute. We said the product's Leashes, then I
need to say that the location is Brick and mortar which is in G10. Again
I'm going to use a cell reference there but I'm not going to fix the row that
specifies the product because, when I drag this down, I'm going to want G10 to
increment to G11. I'll type enter now and then I'll go back to H10. I'm going
to copy it, I'm going to paste it into H11, and then increment it down. This is
the GETPIVOTDATA function again, but instead of Leashes and Brick and mortar,
it's looking at Leashes and Online. If you come over to the pivot table,
Leashes, Online, $20,143. If you watch me do that, and you see these
return the exact same amount, you would say well why would you put so much more
effort into it just to get the exact same amount, right? Who cares? Well there's
so many times that you can build a formula that relies on a value, but then
that value changes. So if I come into this pivot table, let's just come in
here again and change the order of the values and see what happens. If you
look at the formulas, the formulas that were simple cell reference broke. They're
not returning what you wanted. That's returning 10. That's the COUNT of the
quantities. That's not a dollar value. The formatting stayed the same. It still
looks like it's dollars, but it's not. But the GETPIVOTDATA function still works
because it's looking for Leashes in Brick and mortar and it also knows that
you want the SUM of the Total price. So, as with any formula, there's a few things
to keep in mind. One of them is the SUM of Total price. If you left clicked in
that and you just customize that header, let's just say we've called it Total
dollars, it's going to break the GETPIVOTDATA function because there is nothing
called the Sum of Total price anymore as it's looking at the
customized header. You just want to change it to say Total dollars, and it works with the
custom header. So we'll drag down that new formula, and there you go. So there's
one more - I'm just going to delete these for now I don't like looking at a broken
formula - another thing to keep in mind is that, if you change the pivot table, you
can rearrange things as long as these values that you're referencing with the
GETPIVOTDATA stay there. But, if they're not shown in the pivot table, they won't
return. So the GETPIVOTDATA function is built to pull things out of a pivot
table if they're visible. If you take out the Total price value, this function
is not going to work anymore. It's not calculating the total price. All it's
doing is pulling it from the pivot table and the value's not there. The function
doesn't work. Undo that change and there it is. So as you're building out formulas,
you can put this function inside of other formulas to pull these results out
and that will work better than just a cell reference because it updates as the
pivot table updates. If you want to see this file and the accompanying notes
and be able to work with it and also see a collection of eleven other videos on
how to build pivot tables and they will all have linked files and notes, you can
check out a course that I built down in the description. Next up is a video
about the feature of Slicers that you can add to a pivot table that will let
you filter the data on the fly and the controls will stay right on top of your
worksheet. See in that next video. Thanks!