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.