Home » Office Tips

How to consolidate values in Excel [ Microsoft Office ]

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

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

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

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.

Tagged with: | Need more help ? Ask your Questions at our Support Center | Follow us on Twitter @TSNW or Facebook
Facebook Share Print This Post

Posted on 2nd November 2008 by Ashish Mohta , A Professional Tech blogger, Editor and Writer who talks about solving day to day problems of people who use computer. He also writes on How to use the applications like Office, PC tips, Online tools,Browsers and more. All posts by Ashish Mohta | Connect with me @ Twitter | Linkedin | Facebook | Stumble

One Comment »

  • Praveen said:

    its clear and nice explanation .. thank you

    Regards,
    Praveen

Leave your response!

Be nice. Keep it clean. Stay on topic. No spam.