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 techmiqe 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!
I continue this lesson on Data Consolidation in Part Two. Click on this link to see how to Consolidate Data By Category.
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.
And, 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!”
Consolidate Data from Multiple Excel Worksheets By Position [ 6:53 ] Play Now | Play in Popup | Download (597)
August 23rd, 2010 at 10:35 PM
[...] 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, [...]
August 26th, 2010 at 6:03 AM
[...] 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. [...]
December 14th, 2010 at 5:06 PM
[...] Summarize Multiple Excel Worksheets – Consolidate by Position [...]
January 1st, 2011 at 9:09 AM
[...] 1- Summarize Multiple Excel Worksheets – Consolidate by Position [...]
April 5th, 2011 at 12:45 PM
Thank you for this chance to received your orientation to use “Pivot Tables” and others about Excel.
April 5th, 2011 at 1:02 PM
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