At times, the Microsoft Excel data you need might be spread across multiple sheets or even multiple files. In such cases, it's much more convenient to consolidate all this information into a single document.

A quick solution is to copy and paste the cells you need and place 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, you can use smarter methods to accomplish the same goal. These methods will allow you to quickly and easily merge sheets or files in Excel.

How to Combine Multiple Excel Files

If you have multiple Excel files, perhaps each containing numerous sheets, you can combine them into one file with the Move or Copy Sheet command. This method of merging Excel sheets 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. From the top ribbon, select the Home tab.
  2. Within the Cells group, click Format.
  3. Select Move or Copy Sheet.
Move or copy sheet command in Excel

This opens the Move or Copy window. The To book dropdown lets you select the target spreadsheet where you want to send all your individual sheets. You can select (new book) to create a new file, or select an existing file.

Using Move or Copy in Excel

Use the Before sheet box to select where you want to place the target sheet (this will be blank if you're using a new book). When ready, click OK.

Repeat this process for all the sheets that you wish to merge. To save time, hold down the Ctrl key on your keyboard as you click the tabs to select multiple sheets.

Once done, save your new master document. You can also accomplish the reverse of this by splitting a large CSV into separate files.

How to Combine Excel Sheets Into One File With VBA

Rather than performing the above combination technique manually, the quicker way is to use a VBA macro to combine your Excel files. This will come in especially handy if you regularly need to merge multiple Excel files. You'll need to enable the Developer tab in Excel to use VBA.

First, ensure 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.

In the spreadsheet, head to the Developer tab. Within the Code section, select Visual Basic to open VBA. In the VBA window, open the Insert menu and select Module.

Inserting a module in Excel VBA

This'll create a new module where you need to input the code for your macro. You can copy and paste the following code from ExtendOffice:

        Sub GetSheets()
Path = "C:\FILE PATH\"
Filename = Dir(Path & "*.xlsx")
  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

Once you've pasted the code, change the path on the second line to wherever the files are stored on your computer. For instance, if the files were stored in a folder named MUO in your documents, the path would be:

        C:\Users\YOUR_USER_NAME\Documents\MUO
    

The macro is pretty simple. It first goes to the specified directory, and then finds all the .XLSX files. Once it got the Excel file, it opens it, makes a copy of every sheet inside it, and sends the copies to the open Excel file. The code repeats this until all Excel files are copied and merged.

Creating a VBA macro in Excel

Click the run button (or press F5) to execute the macro. This will immediately combine all the Excel sheets into your current file. Close the Visual Basic window to return to your spreadsheet and see the result. Don't forget to save the changes!

How to 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 Excel data is organized and 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 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. While it is possible to run the consolidation procedure in an existing sheet where there's already data, using a new sheet is generally more straightforward.

Using Move or Copy in Excel
  1. In the new sheet, select the upper-left cell where you want to place the consolidated data.
  2. Select the Data tab.
  3. Within the Data Tools section, click Consolidate.
  4. In the Function dropdown, select your desired summary function. The default is Sum, which adds values together.
  5. Click the up arrow button in the Reference field. If the data is in another file, use the Browse button.
    1. Highlight the range you wish to consolidate.
    2. Click Add to add the range to All references.
  6. Repeat step five until you've selected all the data that you want to consolidate.
  7. Finally, click OK.
Consodilated data in Excel

Excel will now consolidate and display the data. Note that the rows are expandable, and you can open each to see the values from different sheets.

In the consolidate window, you can check Create links to source data if you're going to continue to update the data in other sheets and want this new sheet to reflect that. You can also select which labels are carried across with the Use labels in checkboxes.

Merge Excel Data With Style

Merging data is not merely a technical feat; it's a strategic decision that impacts your workflow, analysis, and overflow efficiency. Although the methods here can help you quickly merge your data, merging sheets and files in Excel can still 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. It's better not to have to merge different data sets, but if you do have to, now you know a couple of ways to merge Excel data.