Learn Excel from MrExcel podcast, episode
2013 - GetPivotData May Not Be Entirely Evil! I'll be podcasting this entire book, click
that “i” on the top-right hand corner to subscribe.
Alright, back in episode 1998 I talked briefly about this GetPivotData problem. If we calculate
a % variance and we're outside of the Pivot table pointing inside, and I use the mouse
or the arrow key, so 2019/2018-1. This answer that we're going to get here is correct for
January, but when we double click to copy that down, the formula does not copy, we get
the January answer all the way down. And when we look at it, we're getting GetPivotData,
I didn't type GetPivotData, I just pointed to those cells, and this started happening
back in Excel 2002 without any warning whatsoever. And at that point I said that the way to avoid
this is to type up the formula C5/B5-1, and you'll get a formula that you can copy. Or
if you just hate GetPivotData, if it's “completely evil”, go to the Analyze tab, don't open
the Options button by the way. Go back to the Pivot table, go to the Analyze tab, open
the dropdown next to Options, uncheck this box, it's a global setting. Once you turn
it off, it'll be off forever, alright. Most of the time the questions I get are “How
do I turn GetPivotData off?” but every once in a while I will get someone who loves GetPivotData.
And I was having lunch with Rob Collie when he was still at Microsoft, and he said “Well,
our internal customers love GetPivotData.” I said “What? No, everyone hates GetPivotData!”
Rob says “You're right, outside of Microsoft, absolutely, they hate GetPivotData.” I'm
talking about the accountants inside of Microsoft, and later I met one who now works for the
Excel team, Carlos, and Carlos was one of the accountants who use this method.
Alright, so here's what we have to do. We have our report, a data set here that for
every month we have the plan for each store, and then at the bottom we are accumulating
actuals. Alright, so we have actuals for January through December, but only have actuals for
a few months, the months that have gone past. And what our manager wants us to do is build
a report with stores down the left-hand side, only the Texas stores, of course, to make
life more difficult. And then going across we have months, and if we have an actual for
that month, we show the actual, so January actual, February actual, March actual, April
actual. But then for the months where we don't have actuals we switch over and show the budget,
so budget out through December, and then a total totaling everything, alright. Well,
when you try and create this Pivot table, yeah, it doesn't work.
So insert PivotTable, New Worksheet, you put Store down the left-hand, side that's beautiful,
put Months across the top, put Type across the top, put Sales here, alright. So here's
what we get that we have to start to work with, so we have January actual, January plan,
and then the completely useless January actual plus plan. No one will ever use this, but
I can get rid of these gray columns, that's easy enough, some here to this cell, go to
Field Settings, and change the Subtotals to None. But there's absolutely no way for me
to remove the January plan that won't also remove the April May June July plan, alright,
there's no way to get rid of this. So at this point every month, I'm stuck selecting the
entire Pivot table, going to Copy, and then Paste, Paste Values. It's not a Pivot table
anymore, and then I start manually deleting the columns that don't appear in the report.
Alright, that's the normal method, but the accountants at Microsoft have added an extra
step in January, it takes 15 minutes, and this that step allows this Pivot table to
live forever, right? I call this the world's ugliest Pivot table, and the accountants at
Microsoft accept that this is the world's ugliest Pivot table, but no one will ever
see this report except for them. What they do, is they come here to a new sheet, and
build the report that their manager wants. Alright, so here's the stores down the left-hand
side, I even grouped it into Houston, Dallas and Other, it's a nicely formatted report.
I've highlighted the totals, you'll see that when we get some numbers in, there's currency
on the first row, but not these subsequent rows, blank rows. Ooh, blank rows in the Pivot
table. And one tiny bit of logic up here, where I can put the through date in cell P1,
and then I have a formula here that analyzes that IF the month of the through date is > this
column, and then put the word Actual, otherwise put the word Plan, alright. So all I have
to do has change this through date, and then the word Actual flip over to plan, Alright.
Now, here's what we do, we're going to allow ourselves to be GetPivotData’d, right? I'm
not sure that that's a verb, but we're going to allow Microsoft to GetPivotData. So I start
building a formula with an =, I grab the mouse, and I'm going to go look for January actual
Baybrook! So I go back into the world's ugliest Pivot table, I find Baybrook, I find January,
I find actual, and I click Enter, and let them do it to me, alright, there we go, we
now have a GetPivotData formula. I remember the day that I did this, it was like, you
know, after Rob explained to me what they were doing, and I went back and tried it.
Now all of a sudden, all my life, I've been getting rid of GetPivotData, I've never actually
embraced GetPivotData. So what it is, is the first item is what we're looking for, there's
a field called Sales, this is where the Pivot table starts, and it can be any cell in the
Pivot table, they use the top-left hand. Alright, this is a field name “Store”,
and then they've hardcoded ”Baybrook”, this is a field name “Month”, they've
hardcoded “January”, this is a field name “Type”, and they've hardcoded “Actual”.
THAT's why you can't copy it, because they've hardcoded the values. But the accountants
at Microsoft, Carlos and his co-workers realize “Whoa, wait a second, we have the word Baybrook
here, we have January here, we have Actual here. We just need to change this formula
to point to the actual cells in the report instead of being hardcoded.” Alright, so
they call this parameterizing the GetPivotData. Remove the word Baybrook, come over here and
click on cell D6. Now, I need to lock this down to the column, alright, so I press the
F4 key 3 times, get a single $ before the D, alright. For the month of January I remove
the hardcoded January, I click on the cell E3, I'll press F4 twice to lock it down to
the row, E$3. Type Actual, remove the word Actual, click on E4, again F4 twice, alright,
and I get a formula that now pulls that data back. I'm going to copy that, and then Paste
Special, choose Formats, Alt E S F, see the F is underlined there, E S F Enter, and then
now that, I've done that I'll just repeat with F4, F4 is a redo, and F4. Alright, so
now we have a nice-looking report, it has blanks, it has formatting, it has the single
accounting underline under each section, at the very bottom it has the double accounting
underline. Right, you never get this stuff in a Pivot
table, that's impossible, but this report is driven from the Pivot table. So then what
we do when we get the May actuals, come back here, paste them in, go refresh the world's
ugliest Pivot table, and then here on the report just change the through date from 4/30
to 5/31. And what that does is that causes this formula switch over from the word Plan
to Actual, which goes and pulls the actuals from the report, instead of the plan, alright.
Now, here's the thing that- this is a great, right? I can see where I would do this a lot
if I still, you know, worked in accounting. The thing that you have to be really careful
about is if they build a new store, you have to know to add it in manually, right, the
data is going to show up in the Pivot table, but you would add it manually. Now this one
is a subset of all the stores, if it was reporting all the stores, I would probably out here,
outside of the print range, have something that pulled the grand total from the Pivot
table. And then I would know, if this total doesn't match the grand total from the Pivot
table, that something's wrong, and have an IF function down here Saying “Hey there's,
you know, new data that's been added, be very careful.” They have some sort of a mechanism
to detect that new data is there. But I get it, it's a cool use. So, while most of the
time GetPivotData just drives us crazy, there can actually be a use for it. Alright, so
that's tip #21 out of 40 in the book, buy the book right now, order online, click that
“i” on the top-right hand corner. Long, long recap today, alright: GetPivotData
happens when a formula points inside of a Pivot table, a formula outside the Pivot table
points inside. While the initial formula is correct, it won't copy. Most people hate GetPivotData
and want to prevent it. So you can build a formula without the mouse or the arrow keys,
just type the formula, or turn off GetPivotData permanently, ah, but there's a use, alright.
So we have to build a report with actuals for past month, budget for future. Normal
workflow, create a Pivot table, convert to values, Delete columns. There is a way to
remove the subtotals by using Field Settings, getting rid of that January actual plus plan.
Instead we're just going to create the world's ugliest Pivot table with too much data.
Build a nicely formatted, just plain old report worksheet with maybe a little bit of logic
to change the word Actual to Plan. And then from the first report cell, the first place
where numbers are going to be in that report, type an =, go point to the Pivot table and
allow GetPivotData to happen. We examine the syntax of GetPivotData, so it's the field
to return, Sales, where the Pivot table lives, and then pairs of criteria, the field name
and the value. We're going to remove the hardcoded value and point to a cell, pressing F4 3 times
locks only the column, pressing F4 2 times locks only the row, copy that formula, Paste
Special Formulas. I threw in an extra tip there that F4 is a redo, so I only had to
go to the Paste Special dialog once, and then for the next Paste Special Formulas just used
F4. Next month add the data, refresh the Pivot table, change the through date. Make sure
they didn't build any new stores, you know, have some sort of a mechanism, either manual,
or a check formula, check it out. Thanks to iTrainerMX on Twitter, who suggested GetPivotData,
also Carlos and Rob from Microsoft, Rob now from Power Pivot Pro. Carlos for using this,
and Rob for telling me that Carlos was using it, I met Carlos later, and he confirmed yes,
he was one of the accountants who used this all the time at Microsoft, alright, there
you go. Well hey, I want to thank you for stopping
by, we’ll see you next time for another netcast from MrExcel!
