Excel Consolidate Data from Multiple Worksheets in a Single Worksheet

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.


Download Practice Workbook

Download the following workbook to practice by yourself.


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.

Use Excel Power Query to Consolidate Data From Multiple Worksheets into One Worksheet

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

Use Excel Power Query to Consolidate Data From Multiple Worksheets into One Worksheet

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

Use Excel Power Query to Consolidate Data From Multiple Worksheets into One Worksheet


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.

Use Excel Power Query to Consolidate Data From Multiple Worksheets into One Worksheet

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

Use Excel Power Query to Consolidate Data From Multiple Worksheets into One Worksheet

  • Power Query Editor Window will pop up instantly.
  • We have to use the below formula:
=Excel.Currentworkbook()

Use Excel Power Query to Consolidate Data From Multiple Worksheets into One Worksheet

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

  • The ultimate result will look like the below image:

Read More: How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)


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.

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

  • Now the Consolidate window will pop up.

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

  • 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:

Read More: How to Consolidate Data from Multiple Ranges in Excel (2 Easy Ways)


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.

Excel VBA to Combine Data from Multiple Worksheets into Single Worksheet

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

Excel VBA to Combine Data from Multiple Worksheets into Single Worksheet

  • 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.

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

  • 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:

Read More: How to Consolidate Multiple Worksheets into One PivotTable (2 Methods)


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.


Related Articles

Farzana
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo