One of the things that you may have to do when working with data is to merge multiple workbooks or Excel Files into one.
It could simply be a matter of putting all sheets into one workbook. Or it could be as complicated as combining all similarly structured tables into one mega table.
For example, let’s say you were sent multiple sales data from the different branches of the company that you’re working for.
All of the sent data are structured similarly. Your task is to make a consolidated sales report of all the branches of the company.
To make the task easier, you may merge the sent data into one workbook (and create a mega table).
In the above example, doing it manually will take a considerable amount of time.
Fortunately, Excel has tools that allow its users to efficiently merge multiple Excel files into one workbook. In this article, you’ll be learning about the different methods to do just that.
Are you now ready to learn how to merge multiple Excel files into one workbook?
Well, let’s get started then!
Putting Multiple Sheets In One Workbook
Suppose you want to put multiple sheets from multiple workbooks in one place.
It’s simply a matter of copy-pasting or moving the sheets from one workbook to another. Here are the steps to do so.
- Make sure that the workbooks that contain the sheets that you want to put in one place are open. You may want to assign one of the workbooks as the main workbook. It’s where you’ll be putting all the sheets.
- Open one of the workbooks that’s not the main workbook. Select the sheet/s that you want to put into the main workbook. To select multiple adjacent sheets, click on the first sheet tab that you want to put into the main workbook. Then, while holding down the Shift key, click on the last sheet tab that you want to transfer. Doing so will automatically select all the sheet tabs in between. To select multiple non-adjacent sheets, press and hold the Ctrl key. Then, click on the sheet tabs that you to put into the main workbook.
- Right-click on any of the selected sheets. This will present you with several options. Select Move or Copy from among them. This will open the Move or Copy dialog box.
- In the box below “To book”, select the destination workbook (or the main workbook). Then select where you’ll be inserting the sheet in the box below “Before sheet” (we’ll be selecting move to end for now). If you want to retain a copy of the sheets in the source workbook, tick the box before “Create a copy”. Click the OK button after you’re done.
- You have successfully put multiple sheets from multiple workbooks into one workbook.
Things to Note
- You cannot copy or move sheets that contain a table using this method. You will have to convert any table into a range of cells if you want to move or copy the sheets
Merging Similarly Structured Data Into One Workbook (Using Power Query)
If you want to merge similarly structured data from multiple sheets into one table, then you may want to use Excel’s Power Query feature.
This nifty feature allows users to import data and consolidate data into Excel.
Then, the user may shape and edit the consolidated data in a way that fits their needs. But more importantly, you can use Power Query to merge data from multiple Excel files (provided that they are in the same folder).
Power Query is available in Excel 2016 and later versions.
It can be made available in Excel 2010 and 2013 by downloading it as an add-in (it’s free and can be downloaded from Microsoft’s website).
Preparing the Data
- Make sure that the Excel files that contain the data that you want to merge are located (or organized in subfolders) in the same folder
- The data that you want to merge should be structured in the same way. This means that the data in the Excel files should have the same number of columns, same worksheet names, same column headers, etc.
Merging the Data
- Open the worksheet where you want to merge the data into a single table.
- Open the Data tab. Then on the left side of the ribbon, you’ll see the Get Data Click on it. From the options, select From File, then From Folder.
- Select the folder that contains the Excel files that contain the data that you want to merge. Click the Open button after doing so. This will open a new window.
- The new window will show details pertaining to the files located in the folder. At the bottom, you should see a Combine button. Click on it. You’ll be presented with three options. [Combine & Transform Data] will merge the data from the files into one table, and then you’ll be transferred to another window where you can edit the table before loading it into the workbook. [Combine & Load] will merge the data from the files into one table and then load it into the workbook. [Combine & Load To] will merge the data from the files into one table and then lets you choose where to load the resulting table. Click the option that best meets your needs.
- You have successfully merged similarly structured data from multiple Excel files into one workbook.
Things to Note
- You can merge other file formats such as CSV, XML, and JSON via Power Query
- Power Query is case-sensitive. Keep this in mind when merging data. Make sure that the worksheet names are exactly the same
- As long as the column headers are the same, it doesn’t how they are ordered from workbook to workbook. Power Query will automatically match the data with the same column headers
- You can also use Power Query to merge data from Excel files that don't follow the same structure. You will have to do a bit of editing though to have them be organized in one table.
Conclusion
And those are the different ways you can merge data from multiple Excel files into one workbook.
I hope that you'll be able to use your learnings here in your future endeavors.
Spotted an outdated link or a mistake? Email editors@fundsnetservices.com and we will review it.