Dan asks another Excel 2007 question. How can you re-specify the source data for a pivot table in Excel? It used to be simple to do this in the pivot table wizard, but it is not obvious how to find the pivot table wizard in Excel 2007. In Episode 679, I will show you how to solve the problem in Excel 2007.
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.
Second question from Dan, remember Dan from yesterday.
Dan upgraded to excel 2007.
He says the other thing I can't find in excel 2007 is when I have a pivot table and I've changed the data like I paste a new data into the source and now it's five thousand rows instead of four thousand rows.
How do I respecify? Said It used to be real simple.
Right click the "Pivot Table" go to "Pivot Table Wizard", and you could just go back and change the data right here instead of 564 maybe it's, you know, 575 who knows.
Says I can't figure out a way to do that in excel 2007.
Lets go take a quick look at excel 2007 they changed the pivot table interface in excel 2007.
So we use Insert "Pivot Table" You don't really get to the wizard, you basically just get to the single screen that specifies the data.
Click OK and now we have a different interface so choose "Region", choose "Product" choose "Revenue" and then move "Product" over to "Column Labels".
Ok so simple enough We"ll right click now.
There is no option to go back to the Pivot Table Wizard but what we want to do is go up here to the Ribbon to the "Options" tab and there's now a big button that says "Change Data Source" Basically that is the way to get back to that step in the wizard where you can change the data.
So that's simple enough.
Now I know in previous podcasts I've covered Dynamic ranges and certainly using a dynamic range to define that data would solve this problem as well.
But let's just stay simple here and use the big button on the "Options" tab in order to be able to change that data source.
Thanks to Dan for sending in that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Second question from Dan, remember Dan from yesterday.
Dan upgraded to excel 2007.
He says the other thing I can't find in excel 2007 is when I have a pivot table and I've changed the data like I paste a new data into the source and now it's five thousand rows instead of four thousand rows.
How do I respecify? Said It used to be real simple.
Right click the "Pivot Table" go to "Pivot Table Wizard", and you could just go back and change the data right here instead of 564 maybe it's, you know, 575 who knows.
Says I can't figure out a way to do that in excel 2007.
Lets go take a quick look at excel 2007 they changed the pivot table interface in excel 2007.
So we use Insert "Pivot Table" You don't really get to the wizard, you basically just get to the single screen that specifies the data.
Click OK and now we have a different interface so choose "Region", choose "Product" choose "Revenue" and then move "Product" over to "Column Labels".
Ok so simple enough We"ll right click now.
There is no option to go back to the Pivot Table Wizard but what we want to do is go up here to the Ribbon to the "Options" tab and there's now a big button that says "Change Data Source" Basically that is the way to get back to that step in the wizard where you can change the data.
So that's simple enough.
Now I know in previous podcasts I've covered Dynamic ranges and certainly using a dynamic range to define that data would solve this problem as well.
But let's just stay simple here and use the big button on the "Options" tab in order to be able to change that data source.
Thanks to Dan for sending in that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
