How to Merge Excel Files? 5 Proven Ways

(Note: This guide on how to merge Excel files is suitable for all Excel versions including Office 365)

Imagine you are working in Excel. You have entered, stored, formatted, and performed operations or functions with a wide variety of data. You would have done the same for different data in different workbooks. But, there might come a situation where you would want all your files in one place as a single file. What would you do?

In such a case, Excel offers you the ability to combine data from multiple worksheets or workbooks into one single workbook.

In this article, you will learn how to merge Excel files into one single file. We’ll see how to merge files as separate worksheets and also how to merge data from different workbooks into a single worksheet.

You’ll Learn:

Why Merge Excel Files?
How to Merge Excel Files as Separate Worksheets?
- By Copying and Pasting the Data
- By Copying and Moving Whole Sheets
How to Merge Data into One Sheet?

How to Unmerge Cells in Excel? 3 Best Methods

How to Save an Excel File? 4 Different Ways

Why Merge Excel Files?

There are a variety of reasons why you would want to merge Excel files.

It is easy to look up, search through the data, and reference them when you combine multiple worksheets into a single file.

Merging files also helps you to access data from a single place without having to search or locate it, thereby increasing the efficiency of your work.

Merging files makes them more presentable. Also, it facilitates the easy sharing of data. You will only have to send one file instead of multiple files.

Gives you a clutter-free way to store data. You would not have to store files under different names in folders. Also, merging files takes less space.

How to Merge Excel Files as Separate Worksheets

Merging Excel files is not as complicated as it seems. There are a few methods that we can use to easily merge them. Let us see how to merge Excel files from different workbooks into a single worksheet.

By Copying and Pasting the Data

This is one of the easiest and simple methods to understand and merge Excel files. Just by copying the data from the sheet in one book and pasting the data into another book, you can consolidate all the data into a common workbook.

For example, consider we have a workbook called Book1 which has 5 sheets namely Sheet A, Sheet B, Sheet C, Sheet D, and Sheet E. Now, we want to copy the data from this sheet into another workbook, Book2.

To copy all the data, first, select all the data from the worksheet (say, Sheet A from Book1) either by pressing Ctrl+A or by clicking on the small triangle which intersects the row and column headers in the top left corner.

Right-click on the selected data and click Copy or press Ctrl+C.

Now, open the workbook Book2.

Navigate to a new sheet and press Ctrl+V or click on the Paste button from the Home menu.

*Merge Excel Files by Copying and Pasting*

This pastes the data onto the new sheet in the new book.

Now, we have copied and pasted data from one workbook to another. In the same way, you can copy and paste any number of worksheets into different workbooks and consolidate the data into a single Excel file.

This method is simple and helps you combine a couple of books or sheets. But when there are more worksheets or workbooks, this can be a little inefficient and time-consuming. Also, the formatting might be a little disrupted when you copy data between worksheets.

By Copying and Moving Whole Sheets

This is another easy method that is similar to the copy-and-paste method. But, instead of copying the data, you will copy the sheets together and copy or move them to another workbook.

First, open the workbook to which you want to merge all the files. This will act as the destination.

Then, open the source workbook from which you want to move/copy. In this case, let us copy sheets B, C, and D from Book1 to Book2.

To copy/move the books, first, select the sheets. You can either hold Ctrl and select non-adjacent worksheets or hold the Shift key to select adjacent sheets. Once you select the sheets, they will appear in bold.

Now, right-click and select Move or Copy.

This opens the Move or Copy dialog box. From the To book: dropdown, select the workbook to which you want to move the sheets. In this case, we will be moving the sheets to Book2.

In the Before sheet: section, select (move to end). This will move the copied sheet to the end.

Additionally, check the checkbox for Create a copy, if you want to create a copy of the selected sheets onto a new workbook. This leaves the data in the source workbook intact.

Click OK.

This will copy the selected worksheets onto a new workbook. That is, the selected sheets will be moved from Book1 to Book2.

*Merge Excel files by using Move or Copy method*

In the same way, you can copy/move multiple worksheets from one workbook to a destination workbook easily.

How to Change Row Height in Excel? 5 Easy Ways

How to Split Cells Diagonally in Excel? 2 Easy Ways

How to Merge Data into One Sheet?

The above-mentioned methods help you to merge worksheets from different Excel workbooks. However, there might be some instances where you would want to merge Excel files into a single worksheet. In such cases, you can use the below-mentioned methods to arrive at the desired result.

Using Power Query

Power Query is a very useful tool in Excel that helps perform a variety of operations. Using Power Query, you can merge Excel files into a single workbook and help alter the data with ease.

Prerequisite: When using this method, store all the files you want to combine in a single folder. Also, take precautions to make sure the data are structured and in the same format as each other.

Now, open the Excel workbook in which you want to combine all the other workbooks.

Navigate to the Data tab. Under the Get&Transform Data section, click on the dropdown from Get Data. Click in From File and select From Folder.

*Select Folder from the Get Data option*

A dialog box opens. Select the folder which contains the files you want to merge and click OK.

This inserts all the data from the selected folder and shows you a preview of the selected files.

Click on the dropdown from Combine and select Combine & Load.

This in turn opens the Combine Files dialog box.

From the Sample File dropdown, select any file and select any sheet under the Display Options Parameters. This file will act as a template based on which the data from other sheets will be formatted.

You can see the preview of the data in the corresponding sheet. Check the checkbox for Skip files with errors, if you want Power Query to ignore the errors.

Once you have verified the data, click OK.

After you click OK, the data from the workbooks are combined and reflected in a new sheet. You can see that the data from Book1 and Book3 are combined and reflected in a new Excel sheet in the current workbook.

Note: You can see that only Sheet A from Book1 is imported and combined with Sheet E from Book2. This method is better suited when you have to combine a single worksheet from different workbooks.

Using the Consolidate Data Option

This is another useful tool in Excel to combine data into a single worksheet from different workbooks. Using the Consolidate option in the Data section, you can combine multiple worksheets into one.

Remember: While using this method to merge files, make sure the files you want to merge are opened.

First, open a new worksheet in a new workbook. This will act as the destination where all the files will be merged.

Navigate to Data. Under the Data Tools section, click on the Consolidate button.

This opens the Consolidate dialog box.

Since we will be adding data one after the other, select Sum from the Function: dropdown.

In the References textbox, enter the cell range you want to consolidate and click on Add. You can add data from different worksheets in different workbooks by using the up arrow or by clicking on the Browse button. Here, we have selected the data from Sheet A in Book1 and Sheet E in Book3.

Once you click on Add, the references will be added to the All references: tab. From here, you can remove any references you don’t want to consolidate using the Delete button.

If you want the consolidated data to be dynamic, i.e., the data in the destination changes when the source is changed, click on the checkbox for Create links to source data.

Finally, click OK.

This consolidates the data from the selected source references into a single sheet. You can use the [+] and [-] buttons on the left of the sheet to view the data.

*Merge Excel files using Consolidate option*

Converting Files to CSV

Another effective way to merge Excel files is by changing their format and then reverting to their original format. In this case, let us convert the Excel files Book1 and Book3 to CSV file format and then combine them.

The first step is to convert the two Excel files into CSV format. To do that, open the Excel file. Navigate to File and click on Save As.

Click on More Options. This opens the Save As dialog box. Select the storage location and rename the file. Under the Save as type: dropdown, select the CSV (Comma delimited).

Click on Save.

This saves the Excel files(.xlsx) in the .csv format.

The next step is to use the Command Prompt to merge files.

While using this method, make sure you have placed the files you want to combine in the same folder.

Now, right-click in the empty space and select Open in Terminal.

By default, Windows opens Powershell Editor. Click on the small dropdown next to the new tab or press Ctrl + Shift + 2 to open the Command Prompt.

This opens the Command Prompt Editor.

In the Command Prompt window, enter the command cd (space) the folder path in double quotes. In this case, the command will be cd “C:\Users\Adam\Documents\Merged CSV File”.

Press Enter.

This shows that any command you enter is specified to the current folder.

Enter the command copy *.csv mergedfile.csv in the prompt and press Enter. The “mergedfile” is a variable and it denotes the file name. You can change the file name by changing this section.

Once the execution is complete, you can see the names of the workbooks combined in the Command Prompt.

In the folder, you can see the combined CSV in the name of “mergedfile”.

Let us now convert the CSV file back to an Excel file.

First, open the file in Excel.

Again, navigate to File and click on Save As.

Choose the location and rename the file. More importantly, change the file type back into Excel Workbook (.xlsx) from the Save as type dropdown and click on Save.

*Merge Excel files using Command Prompt*

Also Read:

How to Add Page Numbers in Excel? 2 Easy with Options

How to Hide Formula Bar in Excel (and Show)? 3 Easy Ways

How to Find and Replace in Excel? A Step-by-Step Guide

Frequently Asked Questions

Why am I getting an error when I merge files using Power Query in Excel?

When merging files using Power Query, there are two prerequisites. First, make sure the files you want to merge are in the same folder. Second, make sure the data in all the worksheets have the same data sections, structure, and formatting. Sometimes, this may also be a cause for the error.

Why isn’t the command prompt working in Windows 11?

To open the Command Prompt in Windows 11, open any folder and right-click to open the options dialog box. In the pop-up, select the option Open in Terminal. By default, Microsoft opens Windows PowerShell. To open Command Prompt, click on the dropdown next to the new tab and select Command Prompt or press Ctrl + Shift + 2.

How to merge Excel files as separate worksheets?

To merge the Excel files with the data in the corresponding worksheets, you can either copy and paste the data from one workbook to another in different worksheets. Another method is to select the worksheets, right-click on them, and select Move or Copy. You can then select the workbook you want to move to and move the worksheet with the data intact.

Closing Thoughts

Merging Excel files provides a sure way to access all the data from a single file, and proves to be greatly efficient when storing or sharing data.

In this article, we saw how to merge files in Excel in 5 proven ways. You can either merge files with data in their corresponding worksheets, or you can consolidate all the data from different workbooks into a single worksheet. You can choose the method that suits your purpose the best.

If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!