In this tutorial you will learn to Consolidate Data From Multiple Worksheets in a Single Worksheet. This tutorial is relevant for all versions of Microsoft Excel. However, latest version has been refer
One of the best Excel options which I have learned about managing data is to “Consolidate Data From Multiple Worksheets in a Single Worksheet”.
Download the sample DEMO File
Here you have these four worksheets (DOWNLOAD this file from here) for four different zones which have the same structure.
…now here are the steps you need to follow to consolidate data from these multiple tabs.
Tutorial to consolidate data in Detail
- First of all, insert a new worksheet and name it “Total” (or whatever you want) and select cell A1 in that worksheet.
- Now, go to Data Tab ➜ Data Tools ➜ Consolidate.
- Once you click on consolidate, you will get a window like this (Just follow the steps, for now, I will explain about this window in second part of this post).
- Now, by using “Reference” input box, select the data to range from the first sheet where you have data (north zone).
- After that, click “Add”.
- By using the same method, add references from all the four worksheets.
- Now from the bottom of the window tick mark “Top Row” and “Left Column”. (As we have labels in the first row and first column).
- Click OK.
Once you click OK, it will instantly give you a consolidated data table with the same structure.
Add table heading in cell A1 and you can also use format painter to copy formatting from other worksheets.
Finally, you have a worksheet with consolidated data.
Other Options in Consolidate
Consolidate is a handy tool to consolidate multiple worksheets into a single sheet but we have a lot of other things in this.
Let me explain it one by one.
- Function: You can select a function from this drop down menu (sum, count, average, etc).
- Reference: Select data range from the worksheets you want to consolidate.
- Browse: If want to add a worksheet which is in another workbook, you can use this option.
- Add & Delete: Add or delete selected reference to the list of sheets to merge.
- Top Row: Use top row as a label.
- Left Column: Use left column as a label.
- Create Links To Source Data: If you want to create links for your source data tick mark this option. This is useful if you want to make changes in your source data after creating your consolidate sheet.
Conclusion
In the end, I just want to say, it’s an awesome option to consolidate data within seconds. The best part is you can capture N number of worksheets with it.
If you go with the manual method to consolidate data, it is a real pain. You know, doing one by one or using formula.
