The last in my series of podcasts about GetPivotData. Today, we see how to use GetPivotData to overcome all the bad stuff in a pivot table. Episode 1128 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 another MrExcel netcast… I'm worn out!
I've been spending the last hour trying to get this Pivot table to do what my manager wants me to do.
And I think I'm pretty smart, I mean, I've got myself MrExcel(?), my manager is able to change these headings from Sum of Revenue to “Revenue “, and put in % of Total.
I tried to choose some formats here to make it look good, but you know, these percentages of the total are, like, there's no way to get this 204 as a percentage of the 599, just can-not-happen.
It's always The 204 is the percentage of the grand total, and I'm just so frustrated with this, OK.
But, let's use that cool Microsoft trick that we've been talking about the last couple of days, the GETPIVOTDATA.
So I inserted a new sheet, and I created a nicely formatted report, how I want it to be formatted.
Look at this, the word East repeated all the way down, and Central repeated all the way down, and West repeated all the way down.
And I even put in my % of Region calculation which the Pivot table can't seem to do.
Now, how we going to make this come to life?
I'm going to take the =, go back and point into my pivot table, this is a ABC East, so I go to ABC East, there's my number right there, and that's going to build the GETPIVOTDATA formula for me.
But now I'm going to go through and parameterize this, so where it says region East, I'm going to say that I always want to grab that from a $A6.
And where it says “Product”,”ABC” I'm going to parameterize that and say “That's always going to come from $B6.” Alright, good, and so now I can copy that down, so you look how it works.
And then I'm going to copy it over here where I had created a field called % of Total.
So “% of Total” instead of “Revenue”, doesn't like that, must have been “% of Total ”, without a space, there we go.
Alright, beautiful.
Now I wonder if, instead of literally typing the value in there, if I could point to a cell, wouldn't that be cool?
Of course, you know, I didn't do it that way, because it doesn't work.
So undo, you actually have to type the name in there.
Well alright, we can live with that, I'll format that nicely, and of course, we can copy that down.
And I also had a field called GP%, so we’ll come back here and change the “% of Total” to “GP%”, alright, get those GP%.
Now that we have that whole table working, we can copy it, paste it, paste it, and we are ready to roll, alright.
So now what are the advantages?
Well, I am using a Pivot table here so, as I change the underlying data, I can come back to my Pivot table, I can refresh and the Pivot table will be refreshed.
But then the actual printed report is going to come from this page, and the nice thing here, people say “Well you know, from month to month my Pivot table loses this formatting, and all kinds of bad things like that.” Well this isn't going to lose this format because it's just straight Excel, alright, but we get to take advantage of GETPIVOTDATA to reach into the Pivot table, grab those values.
So it's really kind of the best of both worlds, we're using all of the power of the Pivot table, but we're not stuck with any of those stupid limitations that they give us.
You know, back here in the Pivot table, if I wanted to insert a column between C and D, no deal, can't do it, not allowed.
If I come out of here outside of the Pivot table, Insert Column, cannot move a part of a Pivot table, yada yada yada, I get it, OK.
But over here on my report, uh yeah, sure, you want a column here, no problem, let's just put a tiny column in it, doesn't matter because it's just Excel, you know.
And so, I added a field up here for Q1, and then built a second report here, where I'm grabbing that parameter from B1.
So, you know, we can change this to be Q2, Q3, and now very quickly get a brand new report.
So lots of very, very cool things, I'm starting to see why the folks at Microsoft have been using GETPIVOTDATA.
You know, and I think for most the rest of the world we hate it, we wish it would get turned off, but once you start to understand how it works, I can see a lot of the power of GETPIVOTDATA.
Now, one thing to be aware of here is that the value that you're trying to get must be visible in the Pivot table.
So if I go back to my Pivot table here, I can imagine something where we would want the ABC Total without regions, and since that number is not visible in the Pivot table, it's not going to be available to us in GETPIVOTDATA.
Seems to be a bit of annoying limitation, so that's actually- Two days ago, when I started this whole conversation, I create a Pivot table where I just put everything in, in hopes, that any number I could possibly need, GETPIVOTDATA will be there.
Alright, so there you have it, an end of three days talking about GETPIVOTDATA, started out with Hate GETPIVOTDATA, Understand GETPIVOTDATA, and now I start to see where there might be some nice uses for GETPIVOTDATA.
A pretty interesting function.
Want to thank you 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 another MrExcel netcast… I'm worn out!
I've been spending the last hour trying to get this Pivot table to do what my manager wants me to do.
And I think I'm pretty smart, I mean, I've got myself MrExcel(?), my manager is able to change these headings from Sum of Revenue to “Revenue “, and put in % of Total.
I tried to choose some formats here to make it look good, but you know, these percentages of the total are, like, there's no way to get this 204 as a percentage of the 599, just can-not-happen.
It's always The 204 is the percentage of the grand total, and I'm just so frustrated with this, OK.
But, let's use that cool Microsoft trick that we've been talking about the last couple of days, the GETPIVOTDATA.
So I inserted a new sheet, and I created a nicely formatted report, how I want it to be formatted.
Look at this, the word East repeated all the way down, and Central repeated all the way down, and West repeated all the way down.
And I even put in my % of Region calculation which the Pivot table can't seem to do.
Now, how we going to make this come to life?
I'm going to take the =, go back and point into my pivot table, this is a ABC East, so I go to ABC East, there's my number right there, and that's going to build the GETPIVOTDATA formula for me.
But now I'm going to go through and parameterize this, so where it says region East, I'm going to say that I always want to grab that from a $A6.
And where it says “Product”,”ABC” I'm going to parameterize that and say “That's always going to come from $B6.” Alright, good, and so now I can copy that down, so you look how it works.
And then I'm going to copy it over here where I had created a field called % of Total.
So “% of Total” instead of “Revenue”, doesn't like that, must have been “% of Total ”, without a space, there we go.
Alright, beautiful.
Now I wonder if, instead of literally typing the value in there, if I could point to a cell, wouldn't that be cool?
Of course, you know, I didn't do it that way, because it doesn't work.
So undo, you actually have to type the name in there.
Well alright, we can live with that, I'll format that nicely, and of course, we can copy that down.
And I also had a field called GP%, so we’ll come back here and change the “% of Total” to “GP%”, alright, get those GP%.
Now that we have that whole table working, we can copy it, paste it, paste it, and we are ready to roll, alright.
So now what are the advantages?
Well, I am using a Pivot table here so, as I change the underlying data, I can come back to my Pivot table, I can refresh and the Pivot table will be refreshed.
But then the actual printed report is going to come from this page, and the nice thing here, people say “Well you know, from month to month my Pivot table loses this formatting, and all kinds of bad things like that.” Well this isn't going to lose this format because it's just straight Excel, alright, but we get to take advantage of GETPIVOTDATA to reach into the Pivot table, grab those values.
So it's really kind of the best of both worlds, we're using all of the power of the Pivot table, but we're not stuck with any of those stupid limitations that they give us.
You know, back here in the Pivot table, if I wanted to insert a column between C and D, no deal, can't do it, not allowed.
If I come out of here outside of the Pivot table, Insert Column, cannot move a part of a Pivot table, yada yada yada, I get it, OK.
But over here on my report, uh yeah, sure, you want a column here, no problem, let's just put a tiny column in it, doesn't matter because it's just Excel, you know.
And so, I added a field up here for Q1, and then built a second report here, where I'm grabbing that parameter from B1.
So, you know, we can change this to be Q2, Q3, and now very quickly get a brand new report.
So lots of very, very cool things, I'm starting to see why the folks at Microsoft have been using GETPIVOTDATA.
You know, and I think for most the rest of the world we hate it, we wish it would get turned off, but once you start to understand how it works, I can see a lot of the power of GETPIVOTDATA.
Now, one thing to be aware of here is that the value that you're trying to get must be visible in the Pivot table.
So if I go back to my Pivot table here, I can imagine something where we would want the ABC Total without regions, and since that number is not visible in the Pivot table, it's not going to be available to us in GETPIVOTDATA.
Seems to be a bit of annoying limitation, so that's actually- Two days ago, when I started this whole conversation, I create a Pivot table where I just put everything in, in hopes, that any number I could possibly need, GETPIVOTDATA will be there.
Alright, so there you have it, an end of three days talking about GETPIVOTDATA, started out with Hate GETPIVOTDATA, Understand GETPIVOTDATA, and now I start to see where there might be some nice uses for GETPIVOTDATA.
A pretty interesting function.
Want to thank you 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!
