5 Ways to Merge Excel Files and Sheets Into One Worksheet

In this article, it’s all about how to merge Excel sheets, plus how to merge multiple Excel files into one. Keep reading to learn more. It doesn’t take long before you have many different workbooks full of important spreadsheets if you’re a Microsoft Excel user. 

What happens when these different workbooks need to be combined together so that all the sheets are in the same place? At times, Excel can be challenging because it’s so powerful. You know that what you want to do is possible, but maybe you don’t know how to do it.

I’ll teach you some methods in this tutorial that you can use to combine Excel spreadsheets. When you need to combine multiple spreadsheets, don’t copy and paste the data from each sheet manually.

Ways to Merge Excel Files and Sheets 

1. Copy the Cell Ranges

The obvious method: Select the source cell range, copy and paste them into your main workbook.

The disadvantage: This method is very troublesome if you have to deal with several worksheets or cell ranges. On the other hand: For just a few ranges it’s probably the fastest way

2. Use the INDIRECT formula

The next method comes with some disadvantages and is a little bit more complicated. It works, if your files are in a systematic file order and just want to import some certain values.

You build your file and cell reference with the INDIRECT formula. That way, the original files remain and the INDIRECT formula only looks up the values within these files.

If you delete the files, you’ll receive #REF! errors.

Let’s take a closer look at how to build the formula. The INDIRECT formula has only one argument: The link to another cell which can also be located within another workbook.

1. Copy the first source cell.
2. Paste it into your main file using paste special (Ctrl + Alt + v ). Instead of pasting it normally, click on “Link” in the bottom left corner of the Paste Special window. That way, you extract the complete path. In our case, we have the following link:
   =[160615_Examples.xlsm]Thousands!$C$4
3. Now we wrap the INDIRECT formula around this path. Furthermore, we separate it into file name, sheet name and cell reference. That way, we can later on just change one of these references, for instance for different versions of the same file. The complete formula looks like this (please also see the image above):
   =INDIRECT(“‘”&$A3&$B3&”‘!”&D$2&$C3)

Important – please note: This function only works if the source workbooks are open.

Read: How to Convert Acsm to PDF Following A Comprehensive Guide

3. Merge files with a simple VBA macro

You are not afraid of using a simple VBA macro? Then let’s insert a new VBA module:

1. Go to the Developer ribbon. If you can’t see the Developer ribbon, right click on any ribbon and then click on “Customize the Ribbon…”. On the right hand side, set the tick at “Developer”.
2. Click on Visual Basic on the left side of the Developer ribbon.
3. Right click on your workbook name and click on Insert –> Module.
4. Copy and paste the following code into the new VBA module. Position the cursor within the code and click start (the green triangle) on the top. That’s it!

4. Automatically Merge Workbooks

The fifth way is probably most convenient:

1. Press the Merge Files button within the ‘Professor Excel’ ribbon.
2. Now select all the files you want to merge and all worksheets of these Excel files will be copied to the current workbook.

This procedure works well also for many files at the same time and is self-explanatory.

But you need a third party add-in, for example our “Professor Excel Tools“. You can download and use it for 7 days for free.

5. Manually Copy Worksheets

The next method is to copy or move one or several Excel sheets manually to another file. Therefore, open both Excel workbooks:

The file containing the worksheets which you want to merge (the source workbook) and the new one, which should comprise all the worksheets from the separate files.

1. Select the worksheets in your source workbooks which you want to copy. If there are several sheets within one file, hold the Ctrl key and click on each sheet tab. Alternatively, go to the first worksheet you want to copy, hold the Shift key  and click on the last worksheet. That way, all worksheets in between will be selected as well.
2. Once all worksheets are selected, right click on any of the selected worksheets.
3. Click on “Move or Copy”.
4. Select the target workbook.
5. Set the tick at “Create a copy”. That way, the original worksheets remain in the original workbook and a copy will be created.
6. Confirm with OK.

Bottom Line

Merging sheets and files in Excel can be rather complicated and messy. This illuminates one of the most important lessons about Microsoft Excel: it’s always good to plan ahead.

Merging different data sets after the fact is always going to cause a few headaches, especially if you’re working with large spreadsheets that have been in use for a long time.

When you start working with a new workbook, it’s best to consider all possibilities of how the file will be used further down the line.