How to Merge Excel Files and Sheets

Affiliate Disclosure: By buying the products we recommend, you help keep the lights on at MakeUseOf. Read more.

Advertisement

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.

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

This will sign you up to our newsletter

Enter your Email

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.

Excel Format Move or Copy Sheet

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.

Excel Move or Copy Sheet

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 How to Split a Huge CSV Excel Spreadsheet Into Separate Files How to Split a Huge CSV Excel Spreadsheet Into Separate Files One of Microsoft Excel's shortcomings is the limited size of a spreadsheet. If you need to make your Excel file smaller or split a large CSV file, read on! Read More , 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 Use Custom Views in Excel to Save Specific Worksheet Layouts Use Custom Views in Excel to Save Specific Worksheet Layouts Excel's Custom View setting makes it easy to view specific information on a crowded spreadsheet or to create different layouts for your data. Read More , 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.

Excel Consolidate Sheet Reference

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.

Excel Consolidate More Sheets

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 The Excel VBA Programming Tutorial for Beginners The Excel VBA Programming Tutorial for Beginners VBA is a Microsoft Office power tool. You can use it to automate tasks with macros, set triggers, and lots more. We'll introduce you to Excel visual basic programming with a simple project. Read More 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.

Excel VBA 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 The Beginner's Guide to Microsoft Excel The Beginner's Guide to Microsoft Excel Use this beginner’s guide to start your experience with Microsoft Excel. The basic spreadsheet tips here will help you start learning Excel on your own. Read More .

Explore more about: Microsoft Excel, Microsoft Office Tips, Spreadsheet.

Whatsapp Pinterest

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Arav
    October 4, 2019 at 7:17 pm

    dont see option of "save your workbook as an XLSM". Couldn't see XLSM format, there is no save as or either if save then no XLSM format.

  2. Adhaar Sharma
    July 18, 2019 at 8:25 pm

    You can use mergespreadsheets.com as well

  3. nick davis
    January 4, 2018 at 4:42 pm

    Or you could use power query and save time and be more dynamic.

    • Synkronizer
      May 14, 2019 at 11:40 am

      Using Synkronizer Excel Tool, Users can Merge Hundreds of excel files just a few clicks.

    • Manoj
      September 19, 2019 at 6:43 am

      Can you please help me here! Need to merge multiple sheets & make it dynamic that it should update the master sheet when there is a new record enterer in any of the sheet.

    • Manoj
      September 19, 2019 at 6:45 am

      Can you please help me here. Need to merge multiple excel sheets & make it dynamic that whenever there is a new record entered or changes made in existing record, have to update the master / consolidated sheet.