Rather than turn off the evil GetPivotData like we did yesterday, how about trying to understand what GetPivotData actually does? Episode 1127 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Yesterday we talked about this annoying GETPIVOTDATA, and I showed you how to turn it off.
But!
I had said yesterday, that I had lunch with someone from Microsoft, and they say that inside Microsoft people use this all the time.
So, let's figure out what the heck it does, = GETPIVOTDATA.
Actually I came out here to Excel Help just to remind myself what it does, and it says we have to specify a data field, that's like Sum of Revenue, and then specify the Pivot table.
It's very easy to do that, just pick any cell inside the Pivot table, and then pairs of fields, field1 and the item that we're looking for, field2 and the item that we're looking for.
So given that, let's come back here and take a look at what's going on.
This is asking for Sum of Cost Of Goods Sold, that's one of our fields in the Pivot table.
To identify the Pivot table they just used A3, which is the top-left corner cell, it could be any cell in the Pivot table, and then pairs of fields.
So here they're saying “Hey, we have a field called Region, I want Central.
We have a field called Product, I want ABC.
We have a field called Date, I want 1, that was January.
A field called Customer, IBM.
Quarters, 1, Years, 2008.” And that gave us the answer.
Well, the problem with that is it's so hard-coded, we would never actually be able to, you know, use that for anything, I can't copy or anything.
But, here's the way that people at Microsoft use it, they parameterize that function.
So =GETPIVOTDATA, specify any cell, first of all, the field that we're looking for, so let's say “Sum of Profit”.
And then specify any field, I'm going to choose A3 just like they do, I'll Press F4, so I'll be able to copy this.
And then we start to do pairs of fields, so I put in the word “Region” looking for a region, and instead of typing which region I want, I'm going to point to it using, you know, G7.
Now I'm going to press F4 3 times to lock that down to the column, and we should see that the Central region was 1.3 million, copy it down, there’s East, there's West, ah, interesting.
Ok, let's copy that down to here, we're back to our 1.3 million, I’m going to edit the formula.
I can have up to a 126 pairs of fields and values, so I can ask for ,Product , nah, that should've been in quotes, see, I am new to this, “Product”.
I wonder if I have to capitalize it, I'm going to capitalize it just to be superstitious.
And then specify that I want this value up here in H12, and I'm going to press F4 2 times to lock that down to actually row 11.
So now we see the Central region did 495000 in ABC, I'll copy that throughout, alright and now, hey look, I have a 2-way lookup table, alright.
So you can start to see how the people at Microsoft were using this, what they do.
Remember when I built that Pivot table yesterday, it was an ugly, ugly Pivot table, I just threw every single field in, you know, which probably makes the worst looking Pivot table we’ve ever seen.
Doesn't matter, because that is just going to be sitting there as an intermediate result, and now I have a great way to, without using DSUMs or SUMPRODUCTs or anything, I've got to get any total or any value that’s in the Pivot table.
Tomorrow we'll take a look at exactly how we're going to do that, but I wanted to talk about the basic syntax of GETPIVOT today, and how we get to start to build these.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Yesterday we talked about this annoying GETPIVOTDATA, and I showed you how to turn it off.
But!
I had said yesterday, that I had lunch with someone from Microsoft, and they say that inside Microsoft people use this all the time.
So, let's figure out what the heck it does, = GETPIVOTDATA.
Actually I came out here to Excel Help just to remind myself what it does, and it says we have to specify a data field, that's like Sum of Revenue, and then specify the Pivot table.
It's very easy to do that, just pick any cell inside the Pivot table, and then pairs of fields, field1 and the item that we're looking for, field2 and the item that we're looking for.
So given that, let's come back here and take a look at what's going on.
This is asking for Sum of Cost Of Goods Sold, that's one of our fields in the Pivot table.
To identify the Pivot table they just used A3, which is the top-left corner cell, it could be any cell in the Pivot table, and then pairs of fields.
So here they're saying “Hey, we have a field called Region, I want Central.
We have a field called Product, I want ABC.
We have a field called Date, I want 1, that was January.
A field called Customer, IBM.
Quarters, 1, Years, 2008.” And that gave us the answer.
Well, the problem with that is it's so hard-coded, we would never actually be able to, you know, use that for anything, I can't copy or anything.
But, here's the way that people at Microsoft use it, they parameterize that function.
So =GETPIVOTDATA, specify any cell, first of all, the field that we're looking for, so let's say “Sum of Profit”.
And then specify any field, I'm going to choose A3 just like they do, I'll Press F4, so I'll be able to copy this.
And then we start to do pairs of fields, so I put in the word “Region” looking for a region, and instead of typing which region I want, I'm going to point to it using, you know, G7.
Now I'm going to press F4 3 times to lock that down to the column, and we should see that the Central region was 1.3 million, copy it down, there’s East, there's West, ah, interesting.
Ok, let's copy that down to here, we're back to our 1.3 million, I’m going to edit the formula.
I can have up to a 126 pairs of fields and values, so I can ask for ,Product , nah, that should've been in quotes, see, I am new to this, “Product”.
I wonder if I have to capitalize it, I'm going to capitalize it just to be superstitious.
And then specify that I want this value up here in H12, and I'm going to press F4 2 times to lock that down to actually row 11.
So now we see the Central region did 495000 in ABC, I'll copy that throughout, alright and now, hey look, I have a 2-way lookup table, alright.
So you can start to see how the people at Microsoft were using this, what they do.
Remember when I built that Pivot table yesterday, it was an ugly, ugly Pivot table, I just threw every single field in, you know, which probably makes the worst looking Pivot table we’ve ever seen.
Doesn't matter, because that is just going to be sitting there as an intermediate result, and now I have a great way to, without using DSUMs or SUMPRODUCTs or anything, I've got to get any total or any value that’s in the Pivot table.
Tomorrow we'll take a look at exactly how we're going to do that, but I wanted to talk about the basic syntax of GETPIVOT today, and how we get to start to build these.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
