In Excel 2002 , try to create a formula next to your pivot table. When you copy this formula down to other cells, the formula keeps pointing at the original cells! Is this a bug? No, it's a feature! Well, whatever it is, in Episode 377 I will show you how to stop the functionality so that things continue to work like they did back in Excel 2000.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
You know, when I started the netcast, I was going through the book “Learn Excel from MrExcel”, which has 277 tips.
We've gotten down to where there's only about 10 or 15 tips left in the book, and so I really need your help.
If you have an Excel question, I'd love to have you call in and be a guest on the podcast, just call in 866-581-0221.
You'll get voicemail, I'm going to say “Hey, this is Bob from Atlanta” or “Mary from Chicago” and give us your question.
I' love to answer your questions on the podcast, give me more material to keep the podcast going.
Today's question came in last week, when I was out doing one of my power Excel seminars, someone had recently upgraded from Excel 2000 to 2002.
This person was a Pivot table fan, but they were very annoyed with this new feature that popped in in Excel 2002 in Pivot tables.
They had a Pivot table here that had data in columns A, B and C, and next to the Pivot table they wanted to create a calculation.
Now, they hit the = and then use the mouse method to say they wanted to divide Profit by Revenue.
And annoyingly, instead of creating a simple formula like C2/C5, we get this very powerful function called GETPIVOTDATA, where it actually goes in and extracts a certain cell from the Pivot table.
Now this is really cool, I'm glad that Microsoft is trying to show us the functionality is there.
But in this particular case it's very annoying because, as we copy that formula down to the other cells, it continues to grab the original cells in row 5 for just the East region.
When we move down to other rows in the Pivot table, it doesn't dynamically change, so in this particular case, it's a very annoying feature.
Now, there's a couple of solutions to this.
The first solution is: Instead of using the mouse or the arrow key methods to end of the formula, one solution is literally to type equals C5/B5, and then you're able to copy that formula down to other cells, and it works perfectly.
Now, while the person in the seminar was happy with that solution, the much better solution is to find that icon for Generate GetPivotData, the icon looks like this, it's called Generate GetPivotData.
You basically want to add it to your toolbar and click it once to turn off that feature, and then forevermore, whenever you use the mouse to enter a formula, it will actually create the real formula instead of the GETPIVOT formula.
Now, to find this button, a little bit hard, you need to right-click a toolbar, choose Customize, and then in the Data category you need to scroll almost all the way down to the bottom to find the Generate GetPivotData.
Take that, drag it, drop it on your toolbar, and then in Excel 2002 and newer you can turn off this annoying feature.
There you go, and hey, please, don't forget, give us a call: 866-581-0221, with your Excel questions, and we'll add you to our netcast.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
You know, when I started the netcast, I was going through the book “Learn Excel from MrExcel”, which has 277 tips.
We've gotten down to where there's only about 10 or 15 tips left in the book, and so I really need your help.
If you have an Excel question, I'd love to have you call in and be a guest on the podcast, just call in 866-581-0221.
You'll get voicemail, I'm going to say “Hey, this is Bob from Atlanta” or “Mary from Chicago” and give us your question.
I' love to answer your questions on the podcast, give me more material to keep the podcast going.
Today's question came in last week, when I was out doing one of my power Excel seminars, someone had recently upgraded from Excel 2000 to 2002.
This person was a Pivot table fan, but they were very annoyed with this new feature that popped in in Excel 2002 in Pivot tables.
They had a Pivot table here that had data in columns A, B and C, and next to the Pivot table they wanted to create a calculation.
Now, they hit the = and then use the mouse method to say they wanted to divide Profit by Revenue.
And annoyingly, instead of creating a simple formula like C2/C5, we get this very powerful function called GETPIVOTDATA, where it actually goes in and extracts a certain cell from the Pivot table.
Now this is really cool, I'm glad that Microsoft is trying to show us the functionality is there.
But in this particular case it's very annoying because, as we copy that formula down to the other cells, it continues to grab the original cells in row 5 for just the East region.
When we move down to other rows in the Pivot table, it doesn't dynamically change, so in this particular case, it's a very annoying feature.
Now, there's a couple of solutions to this.
The first solution is: Instead of using the mouse or the arrow key methods to end of the formula, one solution is literally to type equals C5/B5, and then you're able to copy that formula down to other cells, and it works perfectly.
Now, while the person in the seminar was happy with that solution, the much better solution is to find that icon for Generate GetPivotData, the icon looks like this, it's called Generate GetPivotData.
You basically want to add it to your toolbar and click it once to turn off that feature, and then forevermore, whenever you use the mouse to enter a formula, it will actually create the real formula instead of the GETPIVOT formula.
Now, to find this button, a little bit hard, you need to right-click a toolbar, choose Customize, and then in the Data category you need to scroll almost all the way down to the bottom to find the Generate GetPivotData.
Take that, drag it, drop it on your toolbar, and then in Excel 2002 and newer you can turn off this annoying feature.
There you go, and hey, please, don't forget, give us a call: 866-581-0221, with your Excel questions, and we'll add you to our netcast.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
