How to Merge Excel Files and Sheets

What is Excel?

Microsoft Excel is a spreadsheet program that is used to record and analyze numerical data. Think of a spreadsheet as a collection of columns and rows that form a table. Alphabetical letters are usually assigned to columns and numbers are usually assigned to rows.

How to Merge Sheets in Excel

Excel makes it easy to combine more than one sheet into a new workbook:

1. Open the sheets you want to merge.
2. Click Home > Format > Move or Copy Sheet.
3. Use the dropdown menu to select (new book).
4. Click OK.

Merge Excel Sheets Into One File

The easiest way to merge sheets in Excel is by using the Move or Copy Sheet command. This method has its own limitations, but it is quick and straightforward.

First, open up the sheets that you want to merge into the same workbook. From there:

1. Head to Home
2. Click Format
3. Select Move or Copy Sheet

You should see the controls for where to move the selected sheets and the order for those sheets.

Use the dropdown to select (new book). This will serve as the master spreadsheet where you send all of your individual sheets. You can use the Before sheet box to specify the order that the sheets are in.

Repeat this process with the rest of the sheets you want to merge. Then save your new master document.

Merge Excel Data Into One Sheet

Sometimes, you might want to take more than one dataset and present it as a single sheet. This is pretty easy to accomplish in Excel, so long as you take the time to ensure that your data is formatted properly ahead of time.

There are two important conditions for this process to work correctly. First, the sheets that you’re consolidating need to use exactly the same layout, with identical headers and types of data. Second, there can’t be any blank rows or columns.

When you have arranged your data to those specifications, create a new worksheet. It is possible to run the consolidation procedure in an existing sheet where there’s already data, but it’s easier not to.

In this new sheet, head to the Data tab and click Consolidate. Select Sum from the dropdown and then use the button in the Reference field to access your spreadsheet so you can select the data you need.

To add the next dataset, click Add and then select the data in the same way. Do this for all the datasets that you want to merge. You can even draw from other workbooks by using the Browse button, which is Select in the Mac version of Excel.

Check the box titled Create links to source data if you’re going to continue to update the data in other sheets and want this sheet to reflect that. You can also select which labels are carried across with the Use labels in checkboxes, as shown above.

Finally, click OK.

Unfortunately, this process isn’t suitable if you want to merge cells with text in them—it only works with numerical data. In that situation, you’ll need to use VBA.

Combine Excel Files With VBA

If you want to combine sheets from several workbooks in a snap, the best way is to write a simple VBA macro. This will come in especially handy if you carry out this task on a regular basis.

First, make sure that all the files you want to combine are in the same folder on your computer. Then, create a new Excel spreadsheet that will bring them all together.

Go over to the Developer tab and select Visual Basic. Click Insert > Module.

Copy and paste the following code,

Sub GetSheets()  Path = "C:\[PATH TO FILES]"  Filename = Dir(Path & "*.xls")   Do While Filename <> ""   Workbooks.Open Filename:=Path & Filename, ReadOnly:=True   For Each Sheet In ActiveWorkbook.Sheets   Sheet.Copy After:=ThisWorkbook.Sheets(1)   Next Sheet   Workbooks(Filename).Close   Filename = Dir()   Loop  End Sub

Make sure to change the path to wherever the files are stored on your computer.

Next, save your workbook as an XLSM file so that macros are enabled. Then run the macro and you should find that you have a single workbook that contains all the sheets from all the files in the folder.

Take Caution Before Merging Excel Data

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.