How to Merge Microsoft Excel Files and Sheets

Learn everything about how to merge sheets in Excel, plus how to combine multiple Excel files into one.

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, we've rounded up some smarter ways to accomplish the same task. These methods will allow you to quickly and easily merge sheets or files in Excel.

How to Combine Excel Sheets Into One File

If you have multiple Excel files, perhaps each containing numerous sheets, that you want to combine into a single file, you can do this 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.

This opens the Move or Copy window. The To book dropdown lets you select the master spreadsheet where you want to send all of your individual sheets. Select (new book) to create a new file, or select an existing file. Use the Before sheet box to specify the order that the sheets are in (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, you can select multiple sheets in a file by holding Ctrl as you click their tabs at the bottom.

Once done, save your new master document.

For the reverse of this, we've also covered how to split 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. This will come in especially handy if you perform this task regularly.

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

Next, 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. It's possible to run the consolidation procedure in an existing sheet where there's already data, but it's easier not to.

1. In this new sheet, select the upper-left cell of where you want to place the consolidated data.
2. Select the Data tab.
3. Within the Data Tools section, click Consolidate.
4. On 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. 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.
8. Finally, click OK.

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.

Source: MUO

How to Change the Scrolling Direction in Windows 11

If you want to reverse how the scroll wheel works, here's how to invert the scrolling direction in Windows 11.

How to Fix the Clock Watchdog Timeout Error in Windows

Running into the Clock Watchdog Timeout Error in Windows? Here's how you fix it once and for all.

How to Share a Calendar in Outlook

Want to share your calendar in Outlook to inform others about your schedule or list of events? Here's how to do it easily.