How to Merge Excel Files (Without Using VBA)

You might be surprised how much time is lost flipping between Excel workbooks trying to find information – especially if you have a lot to sort through. Learning how to merge Excel files will make finding information easier, faster, and more user-friendly (especially if you’re sharing workbooks).

While you can use VBA to combine files, not everyone is familiar with the programming language or creating Microsoft Excel macros. Here are five different ways to merge files without having to use VBA.

1. How to Merge Excel Files Manually
2. How to Merge Excel Files Using Excel’s Move or Copy Features 
3. How to Combine Excel Files Using Links
4. How to Merge Excel Files Using Power Query
5. How to Merge Multiple Excel Sheets into One

How to Merge Excel Files Manually

Copying the data from one file to another is the simplest and most direct way to combine two Excel files. This method enables you to copy data into separate destination worksheets or into a single worksheet simply by how you paste.

1. Navigate to the source worksheet.
2. Press Ctrl + A (Windows) or Cmd + A (Mac) to select the full sheet, or select a specific range to copy, and use Ctrl + C or Cmd + C to copy the selection. 
3. Navigate to the destination workbook and add a new worksheet if necessary.
4. After selecting a cell to tell Excel where to insert the data, press Ctrl + V or Cmd + V.

By performing a standard Paste (with Ctrl + V), Excel will paste not only the values, but also the formulas, formatting, and other cell attributes. If you only want to transfer the values, instead of doing a standard paste with Ctrl + V, do a Paste Special > Values. This will paste only the cell values and it will convert formula cells into their resulting static values.

This method works in a lot of different situations – but isn’t very automated. So, it’s probably best to use this strategy on a one-time project with small datasets. 

Using Excel’s Move or Copy Features 

One or more Excel sheets can be immediately copied from one Workbook to another by using the “Move or Copy Sheet” tool in Excel. This method will keep each worksheet in tact (if you need to combine data into a single sheet, check out the Power Query method below).

1. Open your Excel file.
2. While holding Ctrl (Windows) or Cmd (Mac) buttons, choose the sheets you want to copy to the destination workbook. The sheets you’ve selected will be highlighted. (Clicking on the sheets again will deselect them.) Note: alternatively, if the sheets are adjacent to each other you can select the first sheet, hold down the Shift key, and select the last sheet in the range of sheets. 
3. Right-click a selected sheet’s tab and choose Move or Copy.
4. In the resulting dialog, choose the destination workbook.
5. Choose where to insert the sheets under Before sheet. (You’ll be able to move them later if you want to.)
6. If you want to leave a copy of the selected sheets in the source file, click Create a Copy.
7. Select OK.

Now the destination workbook will include the selected sheets.

How to Combine Excel Files Using Links

In Excel, you can use links pasted in a cell of one workbook to link to another workbook. This method allows you to retrieve a value from an individual cell located in a different workbook.

1. Launch both Excel documents. 
2. Copy a cell in the source workbook that you’d like to reference. 
3. Navigate to the destination workbook.
4. Click the cell where you want your link pasted, select Paste Special, and then Paste Link.

When you select Paste Link, Excel should generate the following formula for you automatically:

='[file.xlsx]sheet'!$A$1

If you want to display other cells, you can remove the dollar signs from the formula above, making the cell reference relative. Then just copy and paste that formula into the other cells you want to display.

While this method works in extremely simple scenarios, it is not the preferred method for connecting multiple cells and multiple sheets. These external links are fragile and can easily break. If you have a large or complex dataset, this method will become unnecessarily complicated. You may wind up with errors that are difficult to fix if rows or columns are inserted, the cell location changes, or a file is moved, renamed, or unintentionally deleted.

Luckily, there’s a safer way to combine your Excel files together using one of the program’s most popular tools.

How to Merge Excel Files Using Power Query

Power Query allows users to import data into Excel and consolidate, shape, and edit it in a way that fits their needs. You can also use the tool to merge data located in multiple Excel files that are in the same folder (and subfolders). This method results in the data from separate workbooks being combined into a single data range on a single destination worksheet.

Note: if you are running a fairly modern version of Excel for Windows, you’ll have Power Query. But note that Power Query is not available in all Excel versions.  

If you have access to Power Query, here’s how to use it to combine data from multiple files.

1. Move every file you want to merge into a single folder (or organized in subfolders). 
2. Select the Data tab.
3. Click Get Data, then From File, and From Folder.
4. Choose the folder directory. 
5. Click OK.
6. Click Combine & Load to merge your Excel files. 

Note: If you’d like to change the data before loading the results into your destination worksheet, click the Transform Data button. The Query Editor will pop up. There, you can edit rows and columns, filter data, and more.

Using Power Query is an easy and safe way to merge your Excel files. The neat thing is that you aren’t limited to combining Excel files, either – you can merge formats like CSV, XML, JSON, and more! 

Plus, Power Query has a lot of powerful capabilities. You can get some practice using the tool by checking out our most popular Power Query tutorials including this one which focuses on multiple CSV files and this one which focuses on multiple Excel files.

FREE: Excel Speed Challenge

If you enjoyed this post, please check out our free Excel speed challenge.

Watch one short Excel video a day for 5 days. Total video time is only 45 minutes. Learn the Excel skills that can help you save an hour a week.

How to Merge Multiple Excel Sheets into One

You might want to combine multiple datasets into a single sheet for something like a presentation or dashboard. The result will be a table of aggregated values, with one row per unique label.

This is pretty easy to do, with one catch – your Excel data needs to be properly formatted before you get started. You’ll have to be sure your sheets are using identical layouts and data types, and that there aren’t any blank columns or rows. 

You’re ready to create a new worksheet once you’ve organized your data according to those requirements.

1. Choose the upper-left cell on the new sheet where you’d like to place the combined data. 
2. Click on the Data ribbon item.
3. Select Data Tools, then Consolidate.
4. Choose the summary function you want to use from the Function menu. SUM, which adds values collectively, is the default.
5. In the Reference area, select the range you want to merge, then click Add. Repeat this step until you’ve selected all the data you want to merge. 

When you click OK, Excel will create one row for each unique label and aggregate the values from all range accordingly. If you are familiar with PivotTables, this creates a static report that is similar in layout to a PivotTable, with one column of labels and one column of aggregated values.

If you plan to consistently update data in other sheets and want to have those changes reflected in the merged data, select Create links to source data. Then click OK.

Always Be Careful When Merging Excel Data

Excel workbooks tend to get more difficult to maintain the larger they become. Even if you use the safest methods of combining data, it’s always best to plan ahead and think about how future changes will affect your datasets.

It’s also a good idea to always back up your files before merging them together (or just keep backups in general). That way, your original workbooks will still be intact if anything ever goes wrong while combining them.

Do you prefer one of these methods for merging Excel files, or do you have a different way? Let us know in the comments!