Lets say you have lots of project tables in excel which contains break up of each phase man hours i.e. coding, deployment etc. Now if you  have 10 projects like this and you would like to see a consolidated value of all these projects with header labels ( Project name ) and Phase labels i.e. Coding etc. and the man hours. Something like this :

Consolidating values in excel

Microsoft Office Excel 2007 has an in built feature which allows you do this. You can not only switch formulas quickly but it also remembers the range you choose and includes the labels. The image above is generated exactly using it.

Where to find it ?

In office 2007 excel Ribbon menu

• Go to Data tab
• Find Consolidate under Data Tools
• Select the ranges ( You can include headers also )
• Add it and click ok.

Consolidate options in office excel 2007

How to select Ranges / References Properly

In case you want to use Labels which are in top row and left column , this is how you should select them

How to select ranges for consolidating

There are few more thing you can do with this which I would leave it for you to work on. If you got any doubts , feel free to ask or suggest in comments.