This guide will explain several methods you can use to merge two files in Excel.
When working with datasets found in different workbooks, merging the data found in both files may be necessary.
Let’s take a look at a quick example of a situation where you may need to merge two Excel files.
Suppose you have to aggregate order data over the months of January and February. At the end of each month, all order information for that month is dumped into its own file.
Since we will have to use data from two files, it becomes more difficult to summarize all the data. How can we merge the two datasets in Excel?
Excel comes with several methods that you can use to transfer data from one sheet to another. First, the user can use the Move or Copy option to transfer the current sheet to another workbook.
Alternatively, users can use the Paste Link feature to return references to another sheet. The last and most powerful tool we can use to merge two files is the Get Data option found in the Data tab.
Each of these methods has its own advantages and disadvantages, which we will discuss in more detail later.
Merging data into a single workbook can help users with their workflow. Users will no longer have to switch between multiple workbooks when all necessary data is consolidated.
Now that we know when to merge two Excel files, let’s take a look at sample spreadsheets that merge data from multiple files.
A Real Example of Merging Two Files in Excel
Let’s take a look at a real example of a spreadsheet that merges two files in Excel.
The example below shows the result of adding an outside dataset to our current worksheet using the Get Data feature. This feature is an easy way to import data from another file. Not only does this option work with Excel files, it also supports other file formats such as .txt, CSV, and JSON.
In the example below, we’ve used cell references to get data from another workbook. The filename is written in between square brackets.
You can make your own copy of the spreadsheet above using the link attached below.
If you want to try merging two files in Excel yourself, continue reading this guide for a step-by-step breakdown of each method.
How to Merge Two Files in Excel
This section will guide you through each step needed to merge data from two different files in Excel. You’ll understand how you can use different options, such as the Move or Copy feature and the Paste Link option.
Follow these steps to start merging two files in Excel:
- In one of your files, right-click on the current sheet’s tab at the bottom of your screen. Click on the Move or Copy option.
- Next, select the second file in the To book dropdown menu. You can select where to place your sheet. In this example, we’ll simply place our current sheet at the end of the target workbook. Click on OK to perform the move.
- In the second file, you should now find an additional sheet. In the example below, our Sheet1 has been relabeled as Sheet1(1) since a sheet of that name already exists in the second file.
- Another way to merge two files is through Excel’s Paste Link option. This feature allows combining data from different sheets by using cell references. To start using this method, select the range to paste into another spreadsheet. Type the shortcut Ctrl + C to copy the range.
- In another spreadsheet, right-click on an empty range and hover over the Paste Special option. Click on the Paste Link icon seen in the example below.
- We will add the selected data to the sheet as a cell reference. Since this is a cell reference, changes made to the original sheet should reflect in the second sheet.
- Another method for merging files is the Get Data option found in the Data tab. For this example, select the From File > From Excel Workbook to add data from another Excel file.
- Select the workbook and sheet that contains your chosen data. Click on Load to insert the data into your current sheet.
- In the Import Data dialog box, choose the Table option. Next, select where in the worksheet you want to place the new data. In the example below, we’ve chosen to add our data in cell E1. Afterward, click on the OK button.
- Your new data should now be added to the current sheet.
This step-by-step guide should be all you need to merge two or more files in Excel. Our guide has shown multiple ways to consolidate data, including the Move or Copy feature and the Get Data option.
Organizing your datasets is made easier with Microsoft Excel’s wide library of tools and functions. With so many other Excel functions available, you can surely find one that suits your use case.
Are you interested in learning more about what Excel can do? Subscribe to our newsletter to find out about the latest Excel guides and tutorials from us.
