MrExcel's Learn Excel #556 - Multiple Consolidation 2007

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 20, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top