How to Merge Two Excel Files?

Accessing data from multiple Excel files can be time-consuming and hard.

You often have to switch between multiple open windows, it’s messy and you feel like there’s data all over the place.

So, when you have to access data that is spread out over multiple Excel files, it makes sense to merge the sheets from all these files into one single Excel workbook.

This keeps your data organized and saves a lot of referencing time.

In this tutorial, we show you three ways to merge two Excel files:

• Using the Move or Copy feature
• Using VBA
• Using Get & Transform tools

Table of Contents

How to Merge Two Excel Files

Imagine you have two separate files:

These two files contain order-related information (for an imaginary company).

File1 has 2 tabs (named Apr-Jun 2021 and Jul-Sep 2021) and File2 has a single tab (named Jan-Mar 2021).

Let’s say you want to merge File1 and File2 by bringing both sheets of File1 into File2.

As mentioned before, there are 3 ways to do this. The method you choose will depend on your requirement and how your data is structured.

Let us look at each method one by one.

Using the Move or Copy Feature to Merge Two Excel Files

This is the simplest and the most commonly used method to merge two Excel Files.

This method is suited in situations where you have to merge two or more Excel files that have only a few sheets, and you need to do it only once in a while.

Below are the merge two Excel files manually:

1. Select all the tabs that you want to move or copy from File1. Since we want to move all the sheets from File1, we simply select the first tab, hold down the shift key and select the last tab.
2. Right-click on your selected tabs and click on ‘Move or Copy’ from the context menu that appears.

1. This opens the ‘Move or Copy’ dialog box.

1. Click on the dropdown under ‘To book:’.
2. From here, you can select the file (or workbook) to which you want to move or copy the selected sheet(s). Since we want to transfer the sheet to File2, we can select the File2.xlsx option from this list.

1. You should now see the different move or copy options available in the list under ‘Before Sheet:’. Select where in File2 you want the sheet moved. Since Apr-Jun 2021 comes after Jan-Feb 2021, it makes sense to place it at the end (as the last sheet of File2). So select (move to end)

1. If you want to retain a copy of the sheets in File1, then check the box next to ‘Create a copy’.

1. Click OK.

All sheets of File1 should now get copied to File2, so that it now contains 3 sheets in all.

Note that for this method to work, you need to have both the files open (one from which you want to copy the sheets and the one where you want to copy these sheets).

Also, note that this method will create a copy of sheets in File1 and add these to File2. These sheets will still remain in File1.

As I mentioned, this method is fine if you want to do this once in a while with files that have less number of sheets. If you have more sheets, there is always a chance of human error. In such cases, you can use the VBA or the Power Query method covered next.

In case you have more than two Excel files that you want to merge, you can follow the same process, where you combine two Excel files, and then combine the third/fourth file with it.

Using VBA to Merge Two Excel Files

The second method to merge two Excel files uses the following VBA script:

'Code by Steve Scott from https://spreadsheetplanet.com Sub MergeSheets() Dim SourceWb As Workbook Dim TargetWb As Workbook Set SourceWb = Workbooks("File1") Set TargetWb = Workbooks("File2") Dim SourceSheet As Worksheet For Each SourceSheet In SourceWb.Sheets SourceSheet.Copy After:=TargetWb.Sheets(TargetWb.Sheets.Count) Next End Sub

The above script simply takes two files, “File1” and “File2“, cycles through each sheet of File1 and appends a copy of it to File2.

All you need to do is replace the filenames “File1” and “File2” in the above code with your required source and target files.

For this code to work, your Excel files need to be open. In case you want the code to open a closed file, copy sheets from one file to another, and then close the files again, you can do that too (you will need to modify the code to do that).

Note: This script copies the “File1” sheets and pastes them into “File2”. This means the original sheets within “File1” remain as they were.

To run the above script, follow the steps below:

1. Make sure both files that you want to merge are open.
2. Navigate to Developer->Visual Basic.

1. When the Visual Basic window opens, click Insert->Module.

1. Copy and paste the above code into the newly opened window.

1. Edit the code according to your requirement. For example, change the filenames in lines 4 and 5, or make other changes if you need to.
2. Run the code by clicking on the Run button, as shown below:

You should now find the sheets of File1 and File2 merged into File2, while retaining a copy of the original sheets in File1 too.

Note: If you cannot find the Developer menu option, navigate to File->Options->Customize Ribbon and check the Developer option. Click OK.

Using Get & Transform Tools (Power Query) to Merge Two Excel Files

If you’re using Excel 2016 or newer versions, you can also use the Get & Transform tool to merge two or more Excel files.

The Get & Transform tool is a Power Query feature that allows you to import data from multiple sources quickly and easily.

The great thing is that you can use it to make changes to your source data as required before importing it to the new workbook.

Here are the steps that you need to follow in order to merge two Excel Files using the Get & Transform tool:

1. Open File2
2. From the Data tab, select New Query (from the Get & Transform group).

1. Select From File ->From Workbook from the dropdown list that appears.

1. This opens the Import Data window, from where you can select the workbook from which you want to import the sheets. Select File1 and click Import.

1. This opens the Navigator window, where you can see a list of all the sheets and tables from File1.

1. Since we want to import more than one sheet, check the box next to ‘Select multiple items’.

1. Check the boxes next to the sheets you want to import. In our case, we select both.

1. Click on the dropdown arrow next to ‘Load’ at the bottom of the window.
2. Select ‘Load to’.

1. From the Load To window, check the radio button next to ‘Table’ and click ‘Load’.

1. Wait for the query to complete processing.

You should now see two new sheets in File2.

Notice that the names of the sheets are just the generic Sheet1 and Sheet2, so you’ll have to double click on each tab and change the sheet names (if you need to).

That’s it! You have now merged File1 into File2, which now contains all 3 sheets!

Also, notice that the data in the newly imported sheets are in the form of an Excel table. If you don’t want that, you could simply convert them back to ranges by navigating to Table Design->Convert to Range.

When you are asked to confirm, simply click OK.

In this tutorial, we showed you 3 ways to merge two files in Excel.

The first two methods (using Move or Copy and using VBA) are more commonly used since they have been around for a while.

The last method (using Get & Transform) is the lesser-used method since it is fairly new and only available in newer Excel versions.

However, Power Query is increasingly becoming the go-to tool for businesses and other industries, so it makes sense to learn how to bring together data from multiple sources into the framework, to facilitate further processing.

Other articles you may also like:

• How to Convert a CSV File to Excel?
• How to Convert a Text File to Excel?
• How to Make Excel File Read Only
• How to Insert an Excel file into MS Word
• How to Unmerge All Cells in Excel?
• How to Copy Multiple Sheets to a New Workbook in Excel
• How to Group and Ungroup Worksheets in Excel
• How to Open Excel File [xls, xlsx] Online (for FREE)
• Microsoft Excel vs Google Sheets – Which One Is Better for You?
• Switch Between Excel Workbooks (Shortcut)
• How to Combine Two Columns in Excel (with Space/Comma)

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.