Sabrina Ayon
While working with Microsoft Excel, combining worksheets can help us organize the data because we may integrate similar data into a single workbook. Combining is the system that integrates several documents into a single file. In this article, we will demonstrate different ways to combine multiple worksheets into one workbook in excel.
Download Practice Workbook
You can download the workbook and practice with them.
4 Ways to Combine Multiple Worksheets into One Workbook in Excel
To get all the data in one workbook or to organize multiple worksheets data in one workbook, we can follow the ways explained in the bellowed section.
1. Combine Multiple Worksheets into One Workbook Using Power Query
The best technique to merge or combine data from numerous Excel worksheets into a single workbook is to use Power Query. The data must be formatted in the same way when integrating data from different Excel worksheets. That is, the set of rows and the order in which they appear should be the same.
Suppose, we have three different worksheets in the workbooks shown in the picture down. Now we want to combine them into one workbook. To combine multiple worksheets into one workbook using a power query, we need to go along with the procedure.
STEPS:
- Firstly, we need to open the power query, for this to go to the Data tab from the ribbon.
- Secondly, from Get & Transform Data click on Get Data.
- Thirdly, select From Other Sources under theGet Data drop-down menu
- Further, click on Blank Query from the From Other Sources drop-down menu.
- This will launch the Power Query Editor.
- Now, type the following formula in the formula bar of the Query Editor.
=Excel.CurrentWorkbook ()- Press the Enter key to continue. This will display all of the table names in the whole workbook along with any named ranges and/or connections that may be present.
Please keep in mind that the Power Query formulae are case sensitive, so make sure you use the correct formula else you will get an error.
- Furthermore, in the Content header cell, click on the double-pointed arrow.
- Then, uncheck the box that says ‘Use original column name as prefix.’
- Click on the OK button to close the window.
- The above steps will merge all the worksheets’ content into one single workbook.
- Finally, if we close the Power Query, we can see that all the data is now combined and shows up in one single workbook.
Read More: How to Combine Multiple Workbooks to One Workbook in Excel (6 Ways)
2. Merge Worksheets into One Workbook with Excel Consolidate Tool
The built-in Excel Consolidate tool is the easiest way to consolidate data in Excel. The Consolidate feature can give complete data from numerous sheets into one workbook if we wish to summarize it. Perform the following steps to combine the data into a single worksheet
STEPS:
- First of all, go to the Data tab on the ribbon.
- Second, click Consolidate, under the Data Tools category.
- Next, select any of functions you wish to utilize to condense your data from the Function box. In this case, we’ll use Sum.
- After that, pick the range on the first worksheet in the Reference option by clicking the Expand Dialog icon.
- Then, to get that range added to the all references list, click the Add button. Repeat this process for all of the ranges that we wish to consolidate.
- Now, check the Top row or Left column check-mark-boxes under Use labels in.
- Further, if you want the combined data to update automatically when the data source updates, check-mark Create links to source data. Excel will construct connections to the main spreadsheets, as shown in the picture below.
- Finally, click OK.
Please keep in mind that consolidating data into a blank sheet is recommended. If your main worksheet already has data, ensure there are blank rows and columns to accommodate the combined information.
- And, you can see the data of every worksheet is now combined into one single workbook, this will mainly combine all the values.
Read More: How to Merge Multiple Excel Files into One Sheet (4 Methods)
3. Combine Multiple Worksheets into One with Move or Copy Feature
To combine multiple worksheets into one workbook, we can use Excel’s Move or Copy feature. For this, we need to follow some steps down. Let’s look at those procedures to combine several worksheets into one workbook.
STEPS:
- Firstly, access the workbooks that will be combined into one workbook. Then, choose the worksheets from the source workbook to duplicate or move or combine into the one workbook.
- Secondly, right-click on the sheet tab, then pick Move or Copy from the menu that appears after choosing the required worksheets.
- Next, when the Move or Copy dialog appears, select the workbook you want to move or copy worksheets into from the To book drop-down menu. So, we select Book1.
- In the Before sheet box, choose MainSheet, check the Create a copy box, and then hit the OK button.
- This will combine or move or copy multiple worksheets from two or many workbooks into one.
Read More: How to Merge Multiple Excel Files into One Sheet by VBA (3 Criteria)
4. Excel VBA to Combine Multiple Worksheets into One Workbook
With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. Suppose all the different worksheets data in Sheet1. Now we want to combine all those Sheet1 data from different workbooks into a single workbook. To use the VBA code to combine multiple worksheets into one workbook, let’s follow the procedure.
STEPS:
- Firstly, go to the Developer tab from the ribbon.
- Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
- Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.
- This will appear in the Visual Basic Editor where we write our codes to create a table from range.
- Thirdly, click on Module from the Insert drop-down menu bar.
- This will create a Module in your workbook.
- And, copy and paste the VBA code shown below.
VBA Code:
Sub Copy_Worksheets()
Dim wrkbk As Workbook
Dim Str As String
Str = "Sheet1"
For Each wrkbk In Workbooks
If wrkbk.Name <> ThisWorkbook.Name Then
wrkbk.Worksheets(Str).Copy_
Before:= ThisWorkbook.Sheets(1)
End If
Next
Set wrkbk = Nothing
End Sub- After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.
- Finally, This will combine all the Sheet1 worksheets data from different workbooks into one single workbook.
Read More: How to Compare and Merge Excel Workbooks (3 Easy Steps)
Conclusion
The above methods will assist you in Combine Multiple Worksheets into One Workbook in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
