Rivky writes in to ask how to do multiple consolidation ranges in Excel 2007 pivot tables. This feature has been on the pivot table wizard for the last 10 years, but it is not at all obvious in Excel 2007. In Episode 556, you will learn how to find this and all of the other commands hidden in Excel 2007.
This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy. Download a new two minute video every workday to learn one of the tips from the book!
This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy. Download a new two minute video every workday to learn one of the tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel podcast.
I'm Bill Jelen, today we have a question sent in by Ricky; he asked payback in Excel 2003 it was possible to create a Pivot Table that uses multiple consolidation ranges, so here's the Pivot Table and Pivot Chart Wizard step 1 o 3, and sure enough we can say that the data comes from multiple ranges but in Excel 2007; when we use the new insert Pivot Table icon it automatically jumps into hey where is your table range there's nothing here that says that we can create multiple consolidation ranges, and you wonder how you can do that, well it turns out that Microsoft left this functionality in, they just took it out of the mainline user interface.
So what we have to do is we have to Right Click on the Quick Access Toolbar and say customize Quick Access Toolbar, when we go in and customize this is where we can add icons and I always starts here with popular commands but I'm more interested in the second category; the category called commands not in the ribbon these are all things that Excel can do but they didn't have space for or I didn't think they were important enough to add to the ribbon if we scroll down into the peas, there's something called Pivot Table and Pivot Chart Wizard, we click add and it moves over to now be part of our Quick Access Toolbar, click OK.
So now I select a cell at my data set rather than use insert Pivot Table, I hit this icon and it's wild they actually took the time to update the art on this even though they left it out of the mainline system and you can choose multiple consolidation ranges and all of the functionality that was in Pivot Tables in excel 2003.
So it's bizarre that it's there even with new art but they didn't think it was important enough to add to the ribbon you have to add it to your Quick Access Toolbar.
Great question, thanks for stopping by; we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen, today we have a question sent in by Ricky; he asked payback in Excel 2003 it was possible to create a Pivot Table that uses multiple consolidation ranges, so here's the Pivot Table and Pivot Chart Wizard step 1 o 3, and sure enough we can say that the data comes from multiple ranges but in Excel 2007; when we use the new insert Pivot Table icon it automatically jumps into hey where is your table range there's nothing here that says that we can create multiple consolidation ranges, and you wonder how you can do that, well it turns out that Microsoft left this functionality in, they just took it out of the mainline user interface.
So what we have to do is we have to Right Click on the Quick Access Toolbar and say customize Quick Access Toolbar, when we go in and customize this is where we can add icons and I always starts here with popular commands but I'm more interested in the second category; the category called commands not in the ribbon these are all things that Excel can do but they didn't have space for or I didn't think they were important enough to add to the ribbon if we scroll down into the peas, there's something called Pivot Table and Pivot Chart Wizard, we click add and it moves over to now be part of our Quick Access Toolbar, click OK.
So now I select a cell at my data set rather than use insert Pivot Table, I hit this icon and it's wild they actually took the time to update the art on this even though they left it out of the mainline system and you can choose multiple consolidation ranges and all of the functionality that was in Pivot Tables in excel 2003.
So it's bizarre that it's there even with new art but they didn't think it was important enough to add to the ribbon you have to add it to your Quick Access Toolbar.
Great question, thanks for stopping by; we'll see you next time for another netcast from MrExcel.
