feed

How to Merge Microsoft Excel Files and Sheets

admin

Sometimes, the Microsoft 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.

You can copy and paste the cells you need in a pinch, placing them all on the same sheet. However, depending on how much data you're working with, this might take 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 busy work when it comes to merging sheets or files in Excel.

How to Merge Excel Sheets

It's easy to combine more than one sheet into a new workbook in Excel. Merge sheets by creating a new book:

  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.

How to Combine Sheets in Excel Into One File

The easiest way to merge sheets in Excel is by using the Move or Copy Sheet command. This method of how to merge sheets in Excel 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.

Related: Pros and Cons of Using Excel as a Project Manager

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 you're consolidating need to use exactly the same layout, with identical headers and data types. 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 a suitable way to merge two Excel sheets if you want to merge cells with text in them. It only works with numerical data. In a situation involving text, you'll need to use VBA to merge excel sheets.

Related: How to Work With Worksheet Tabs in Microsoft Excel

How to Combine Excel Sheets With VBA

If you want to merge sheets in Excel 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 regularly.

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.

For how to merge two excel sheets vis VBA macro, we consulted ExtendOffice. 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.

Related: How to Copy Formulas in Microsoft Excel

Take Caution Before Merging Excel Data

Merging sheets and files in Excel can be 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 you will use the file 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. An ounce of prevention, as they say.

← Older Post Newer Post →