GetPivotData

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!