Excel Consolidate Data from Multiple Worksheets in a Single Worksheet

Get FREE Advanced Excel Exercises with Solutions!

When we produce mass data through our work, consolidation of data from multiple sources needs to be combined in order to make decisions or for forecasting. In this article, I will show you how you can consolidate your necessary data from multiple worksheets in a single worksheet and work more easily to get the outcome.

Table of Contents hide

Download Practice Workbook

3 Methods to Consolidate Excel Data from Multiple Worksheets in a Single Worksheet

1. Use Excel Power Query to Consolidate Data From Multiple Worksheets in One Worksheet

2. Integrate Data from Multiple Worksheets into Single Worksheet with Excel Consolidate Tool

3. Excel VBA to Combine Data from Multiple Worksheets into Single Worksheet

Conclusion

Download Practice Workbook

Download the following workbook to practice by yourself.

Consolidate Data from Multiple Worksheets.xlsm

3 Methods to Consolidate Excel Data from Multiple Worksheets in a Single Worksheet

Here we have 3 Datasets which include the Student Number, Name, and Semester Marks of 15 students.

The below figure is showing Dataset 1 including the Student Number, Name, and Semester Marks of the first 5 students of a class of 15 students.

The second figure is showing Dataset 2 which contains the Student Number, Name, and Semester Marks of another 5 students.

Finally, the below figure is showing Dataset 3 including the Student Number, Name, and Semester Marks of the last 5 students of the class.

1. Use Excel Power Query to Consolidate Data From Multiple Worksheets in One Worksheet

When Consolidation of multiple worksheets into one worksheet is needed, Power Query is undoubtedly one of the best methods. Power Query is a very useful tool to consolidate data from multiple worksheets into one worksheet. It helps us to consolidate as well as transform data. Follow the below steps.

STEPS:

First, go to the Data tab.
Next, the step is to click the Get Data feature.

Now, from the Get Data drop-down, we get the ’From Other Sources’ option which can lead to the ‘Blank Query’ feature.

Power Query Editor Window will pop up instantly.
We have to use the below formula:

=Excel.Currentworkbook()

Later From the content uncheck ‘use original column name as prefix‘ and press OK.

The ultimate result will look like the below image:

Similar Readings

2. Integrate Data from Multiple Worksheets into Single Worksheet with Excel Consolidate Tool

To consolidate data from multiple worksheets we can also use a handy tool in Excel which is the Excel Consolidate tool. I will now show you how to use the Excel Consolidate tool to get consolidated data from different worksheets in one worksheet in an easy way. Kindly follow the below-mentioned steps accordingly:

STEPS:

First, we, have to select the Data tab.
Secondly, click on the Consolidate feature present in the Data Tools option.

Now the Consolidate window will pop up.

Then we have to first select Sum from the Function option.
After that, we have to select the ‘Range’ of ‘Dataset 1’ in the ‘All references’ by expanding the Expand Dialog icon.
By clicking the ‘Add’ option we include the range of ‘Dataset 1’ in ‘All References’.
We get Dataset 2 and Dataset 3 data in All references through the same method.
Now, we have to check the ‘Top row’ option in the Use label in option and then check ‘Create links to source data’.
At last, click OK to finish.

Data containing ‘Marks’ from the ‘3 datasets’ are ‘Consolidated’ under the same header.
The result will be like the below image:

3. Excel VBA to Combine Data from Multiple Worksheets into Single Worksheet

Excel VBA can help three different worksheets consolidate into one workbook. In order to consolidate multiple worksheets into one workbook using an Excel VBA feature, we need to follow certain steps.

STEPS:

In the beginning, open the Developer tab from the ribbon.
Then, click on Visual Basic to open the Visual Basic Editor from the Developer.

Now, select the Module from the Insert option from the ribbon.

A blank module will be opened in a new window
In this blank module, we have to paste code to consolidate the three datasets.
Next, copy the following code below in the blank module:

Sub ConsolidateData()
Worksheets("VBA").Range("B4:D9").Value = Worksheets("DATASET1").Range("B4:D9").Value
Worksheets("VBA").Range("B10:D14").Value = Worksheets("DATASET2").Range("B5:D9").Value
Worksheets("VBA").Range("B15:D19").Value = Worksheets("DATASET3").Range("B5:D9").Value
End Sub

The below image shows what it looks like when we paste code to generate consolidated data in VBA.

After that, select Run or press the F5 key to run the code

The final data to be consolidated using VBA are like the below image:

Conclusion

Through this article, you can consolidate multiple Worksheets into a single Worksheet in Excel. If you have any questions, suggestions, or feedback please let us know in the comment section. For more in-depth knowledge and help please check the ExcelDemy.com blog on a regular basis.