Consolidating Ranges with Pivot Tables

I was asked about using a pivot table to consolidate the data in several worksheets this week. It is years since I did that. I remember doing it frequently in Excel 2003 but I cannot remember doing it with Excel 2007 and certainly haven’t done it in Excel 2010 and Excel for mac 2011.

What’s the point of telling you that? Well, I tried a quick solution in Excel 2007 yesterday afternoon and it didn’t work. I said, I really don’t remember the steps but it seems to me that things have changed. They have.

Thanks to for starting me off again although this page seems to be using Excel 2003.

The technique is as follows:

In Excel 2007, press <Alt>+D then press P and the Pivot Table and Pivot Chart Wizard opens that includes the option to analyse Multiple Consolidation Ranges, so select that.

The rest of the process is fairly self explanatory EXCEPT that it seems to be very keen for you to select page fields … the chances are you don’t want these at all so say no and no as appropriate.

As I said, the page I referred to above seems to show what happens in Excel 2003 so here are the Excel 2007 updated graphics from yours truly:

I created three worksheets and to keep things really simple, all three were exactly the same as each other: you want the same layout in reality but different values!

open by selecting <Alt>+D and then P

Pivot Table Wizard

Select Multiple consolidation ranges, click Next

I hope everything is clear here but if not, just comment on this post and I will respond.