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.

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!

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.

01 03 2012 at 12:02 pm

It works, great. You are really excel “MASTER”

Thank you very much for the support, every learning in excel is an great value addition to my professional life.