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

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

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

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 Ferdous

Farzana Ferdous is a dynamic professional with a strong engineering background and a wealth of versatile work experience. With a passion for dreaming big and staying motivated, she has excelled in various fields and is always eager to take on new challenges. As a driven and motivated individual, Farzana is committed to continuous learning and professional growth.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF