Summarize Multiple Excel Worksheets – Consolidate Data By Position

There are many ways to Summarize the data that is stored in multiple Excel Worksheets or Workbooks. Pivot Tables are great for producing summaries. However, many people do not use – or do not know how to use – Pivot Tables, so let me demonstrate how to use Excel’s Consolidate Data Tool to get the job done.

Consolidate Data By Position

In this scenario, I will take the data from four identical worksheets and consolidate the sales numbers in a new worksheet. First, without using a “Link” to keep the data in the consolidated worksheet current and then I show you how to create a link to the Source Data.

But… there is a “Got’cha Step” when you link sources. It is possible to “double your sales numbers” without realizing it! This might make you feel good when you first see this. However, this is not good – when you are found out. And, trust me on this, someone will definitely find this error!

SUM Across Group of Excel Worksheets

As a bonus, I include another technique to SUM cells from multiple worksheets. Watch as I show you this “trick” – how to use the SUM() Function to total data across a contiguous group of Excel worksheets. It really is a great tip to learn!

Watch My Video on YouTube

Follow this link to watch my tutorial on my YouTube Channel – DannyRocksExcels

Related Video Tutorials

I continue this lesson on Data Consolidation in Part Two. Click on this link to see how to Consolidate Data By Category.

Watch my Video on iTunes

You can download and view this Excel Training Video at the iTunes Store. Follow this link to subscribe to the “Danny Rocks Tips and Timesavers” podcast.

My Video Training Resources

You can learn “The 50 Best Tips, Tricks and Techniques for Excel 2007” when you purchase my DVD – ROM!

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn


  1. Carlos Rubio says:

    Thank you for this chance to received your orientation to use “Pivot Tables” and others about Excel.

  2. Hi Carlos –

    Thanks! It is my pleasure to offer these video tips and resources to you.

    I appreciate you taking the time to write to me.

    Danny Rocks


  1. […] the original post here:  Excel's Consolidate Data Tool Summarizes multiple worksheets … By pivot | category: pivot, pivot data | tags: are-great, consolidate, excel, not-khttp, […]

  2. […] In Part One, I showed you how to Consolidate Data By Position –  where the range of cells was consistent in each of the Excel worksheets –  Click on this link to go to this lesson. […]

  3. […] Summarize Multiple Excel Worksheets – Consolidate by Position […]

  4. […] 1- Summarize Multiple Excel Worksheets – Consolidate by Position […]

Speak Your Mind