How to Merge Excel Files and Sheets

Sometimes the Excel data you need is split across multiple sheets or even multiple files. It can be significantly more convenient to have all of this information in the same document.

Unlock the FREE "Essential Excel Formulas" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

Read our privacy policy

In a pinch, it’s possible to copy and paste the various cells you require, placing them all in the same sheet. However, depending on how much data you’re working with, this might end up taking a lot of time and effort.

Instead, consider some of the smarter ways to accomplish the same task. These methods might just allow you to skip some of the busywork when it comes to merging sheets or files in Excel.

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 limitations, but it’s quick and straightforward.

First, open up the sheets 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 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.

For splitting a large Excel spreadsheet into separate files, check out our tutorial.

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’ve arranged your data to those specifications, create a new worksheet. It’s 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. Take a look at our Excel VBA programming tutorial for beginners if you like.

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 perform 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.

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

Copy and paste the following code, taken from a guide by ExtendOffice:

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.

Excel is great at making documents that you can refer to and use for an extended period of time, but decisions made early on can cause or prevent problems later. For more help with Excel, take a look at how to create an Excel pie chart and our beginner’s guide to Microsoft Excel.

Read the full article: How to Merge Excel Files and Sheets

Read Full Article