Dan from Philly checks in with some good uses for GetPivotData - the function that most people love to hate. If you've ever been annoyed when Excel starts using this function, take a step back and see the actual uses for the function. Episode 807 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.
Well, today we're going to visit the get pivot data function.
Now, normally, I complain about this because it drives me crazy.
Let's actually took a look at when it would really be useful.
I have a pivot table here.
COUNTRY's going down the side, SALES and PROFIT going across, and initially I just built a formula to grab Japan’s sales so that formula is =B8.
Well, you know, the problem with pivot tables is they're flexible.
If we sell to new customers this week or, you know, new data gets in there, the order of those countries may change.
So, let me go do that and I'm going to show you a cool trick with this.
This is the data that my original pivot table was built on and, normally, if I add new rows, I'm going to have to go back through the pivot table wizard, but instead, in Excel 2003, I'm going to press CONTROL+L. CONTROL+L sets up a list.
In Excel 2007, that's called CONTROL+T now, and the beautiful thing about this is any data we paste in this empty row below the data, the pivot table will automatically extend to include that new data without us revisiting the wizard.
So, I have a few new records that I add in, paste that right below my data, and the list extends.
That's great.
Now, let's go back to the pivot table.
Be careful here.
Japan is pulling from B8.
It had 75 in SALES.
When I go into the pivot table and I refresh, it will go back, it'll pull those new rows in, but you see it now says Japan is doing a 100 which is incorrect.
That's because Japan moved.
Japan moved from one row to another when I added the new countries.
So, one approach here is to use VLOOKUP, alright?
So, here we have a VLOOKUP formula that says, hey, go look for Japan within the pivot table, but you already see that we have a slight problem in that the pivot table grew when we refreshed.
Before, the pivot table used to just go down to row 10.
Now, it goes to row 12.
I suppose that you could make your VLOOKUP just be extra long, include a thousand rows or something like that, but the workaround here is not to use a VLOOKUP at all.
The workaround is to allow Excel to specify the get pivot data function.
So, when I build this formula, I'm going to type the = sign and I'm going to go click on Japan SALES and you see that it builds a formula that says, hey, look, we're looking for SUM of sales.
We're looking for that field instead of SUM of profit, and the beautiful thing about this is we don't have to specify anything except for the top left corner of the pivot table, and think about it as this pivot table changes it's going to add new rows or add new columns.
The top left corner is static.
It's going to stay exactly where it is, and then I get to specify pairs of arguments.
In this case, I'm saying our country is = to Japan.
You might have a pivot table with more fields, you could say country is = to Japan and product is = to ABC.
You could continue to specify additional pairs there.
The nice thing about get pivot is no longer how many rows we had, or if that sequence would change, we're not going to run into any problems.
It’s still going to be able to grab Japan no matter where Japan is.
Now, let's go back to the original data set.
I'll type some new data in here, and when I refresh the pivot table, even though Japan moved from row 10 to row 11, the get pivot data function continues to grab the right field.
[ =GETPIVOTDATA(“SUMOFSALES”,$A$3,“COUNTRY”,“JAPAN”) ] Now, the one problem with this is it’s possible that you've turned this functionality off.
In most of my seminars, when someone gets frustrated that they can't enter a formula that points into a pivot table, I show them how to add that icon to their toolbar and turn it off.
So, if this is not working for you, you might want to go customize your toolbar and turn that feature back on.
Well, I want to thank Dan from Philly.
Dan sent in a suggestion a couple of weeks ago, how to autofilter the pivot table, and Dan followed up with cool uses for get pivot data.
So, at his suggestion, I'm passing it on to you.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Well, today we're going to visit the get pivot data function.
Now, normally, I complain about this because it drives me crazy.
Let's actually took a look at when it would really be useful.
I have a pivot table here.
COUNTRY's going down the side, SALES and PROFIT going across, and initially I just built a formula to grab Japan’s sales so that formula is =B8.
Well, you know, the problem with pivot tables is they're flexible.
If we sell to new customers this week or, you know, new data gets in there, the order of those countries may change.
So, let me go do that and I'm going to show you a cool trick with this.
This is the data that my original pivot table was built on and, normally, if I add new rows, I'm going to have to go back through the pivot table wizard, but instead, in Excel 2003, I'm going to press CONTROL+L. CONTROL+L sets up a list.
In Excel 2007, that's called CONTROL+T now, and the beautiful thing about this is any data we paste in this empty row below the data, the pivot table will automatically extend to include that new data without us revisiting the wizard.
So, I have a few new records that I add in, paste that right below my data, and the list extends.
That's great.
Now, let's go back to the pivot table.
Be careful here.
Japan is pulling from B8.
It had 75 in SALES.
When I go into the pivot table and I refresh, it will go back, it'll pull those new rows in, but you see it now says Japan is doing a 100 which is incorrect.
That's because Japan moved.
Japan moved from one row to another when I added the new countries.
So, one approach here is to use VLOOKUP, alright?
So, here we have a VLOOKUP formula that says, hey, go look for Japan within the pivot table, but you already see that we have a slight problem in that the pivot table grew when we refreshed.
Before, the pivot table used to just go down to row 10.
Now, it goes to row 12.
I suppose that you could make your VLOOKUP just be extra long, include a thousand rows or something like that, but the workaround here is not to use a VLOOKUP at all.
The workaround is to allow Excel to specify the get pivot data function.
So, when I build this formula, I'm going to type the = sign and I'm going to go click on Japan SALES and you see that it builds a formula that says, hey, look, we're looking for SUM of sales.
We're looking for that field instead of SUM of profit, and the beautiful thing about this is we don't have to specify anything except for the top left corner of the pivot table, and think about it as this pivot table changes it's going to add new rows or add new columns.
The top left corner is static.
It's going to stay exactly where it is, and then I get to specify pairs of arguments.
In this case, I'm saying our country is = to Japan.
You might have a pivot table with more fields, you could say country is = to Japan and product is = to ABC.
You could continue to specify additional pairs there.
The nice thing about get pivot is no longer how many rows we had, or if that sequence would change, we're not going to run into any problems.
It’s still going to be able to grab Japan no matter where Japan is.
Now, let's go back to the original data set.
I'll type some new data in here, and when I refresh the pivot table, even though Japan moved from row 10 to row 11, the get pivot data function continues to grab the right field.
[ =GETPIVOTDATA(“SUMOFSALES”,$A$3,“COUNTRY”,“JAPAN”) ] Now, the one problem with this is it’s possible that you've turned this functionality off.
In most of my seminars, when someone gets frustrated that they can't enter a formula that points into a pivot table, I show them how to add that icon to their toolbar and turn it off.
So, if this is not working for you, you might want to go customize your toolbar and turn that feature back on.
Well, I want to thank Dan from Philly.
Dan sent in a suggestion a couple of weeks ago, how to autofilter the pivot table, and Dan followed up with cool uses for get pivot data.
So, at his suggestion, I'm passing it on to you.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
