How to Merge Excel Files

There are various ways to combine worksheets, or selected data, from separate Excel spreadsheets into one. Depending on how much data you need to merge, one method may work better for you than another. Excel has built-in options for data consolidation, but there are also a few handy add-ons that merge sheets for you with ease.

Follow along in this article to learn all about the various ways to merge excel files.

Copy and Paste Cell Ranges from Multiple Spreadsheets

The good old Copy (Ctrl + C) and Paste (Ctrl + V) hotkeys might be all you need to combine Excel files. You can copy a range of cells in one sheet and paste them into a new spreadsheet file. Excel includes Copy and Paste options in its menus. For more advanced pasting, Excel offers multiple options such as Values, Formulas, Keep source formatting width, and several more.

Excel Basic Copy and Paste Functions

1. Copying: Open the sheet you need to copy cells from and select the cells you need. Hold the left mouse button and drag the cursor over the cell range to select it. Press Ctrl + C to copy the selected sheet area. You can also right-click within the cells and select Copy.
   
2. Pasting: Open a blank spreadsheet to include the merged data. Select a cell and press the Ctrl + V hotkey to paste to it. Alternatively, you can right-click and select Paste from the context menu.
   

Excel Advanced Copy and Paste Functions

If you did not notice the difference in the images above, you see that the basic paste option placed the content in the new spreadsheet’s current format. More specifically, the cell widths were the same in the pasted spreadsheet whereas they were different in the original. This scenario demonstrates the potential need for advanced copy-and-paste functions.

Sometimes, you need to keep the source’s layout and formatting or just require the formulas to paste into an existing layout. Other times, you need the column width to match or a linked picture to remain linked. The list goes on, but you get the idea.

The Advanced Paste Options in Excel

1. Copying: Open the sheet you want to copy cells from and select the cells you need. Left-click the mouse and hold while dragging the cursor over the desired cells. Release and press Ctrl + C to copy all highlighted cells.
   
2. Pasting: Open an existing or blank spreadsheet to paste the copied cells in. Right-click and select Paste Special from the context menu. This example pastes the cells using the original source’s width. Notice that the advanced paste option is showing a preview of the paste results when hovered over.
   
3. Confirm the changes are what you need. To compare the before and after differences, you can use Undo and Redo functions.
   

Combine Sheets in Excel Files with the Move or Copy Option

The Move or Copy tab option is one you can select to copy full sheets to another Excel spreadsheet. Thus, you can copy or move numerous sheets from different files into one spreadsheet. The option does not enable you to select cell ranges, but it is fine for merging full sheets.

1. Open the files you want to move and a spreadsheet to copy them to, and then right-click a sheet tab at the bottom of Excel’s window and select Move or Copy.
   
2. Choose the sheet (book) you want the file moved to by using the drop-down list and click the Create a copy checkbox to copy the selected sheet. If you do not select that option, the sheet moves from one spreadsheet to the other.
3. Press the OK button to close the window. Now the spreadsheet you selected to move to will also include the sheet. The sheet’s tab includes a (2) to highlight that it’s a second copy.
   

The Consolidate Option

Excel has a built-in Consolidate option that you can select to merge more specific cell ranges from alternative spreadsheets together into a single worksheet. This is a great option for combining data in table list formats. The data ranges in the separate spreadsheets should be in list format with tables that have column and row headings like the one shown below, which is a database table layout.

1. First, open the documents containing the data to be consolidated and a blank spreadsheet, otherwise the master worksheet, which will include the merged cell ranges.
2. Click the Data tab and select the Consolidate option.
   
3. Next, ensure Sum is selected for the Function dropdown menu, then click in the Reference textbox and enter the spreadsheet file that includes a cell range you need to merge, or select it from the Browse option. The selected file path is then included within the Reference box.
   
4. When you have selected all the required cell ranges from the other spreadsheet files, select the Top row, Left column, and Create links to source data options on the Consolidation window. Press OK to generate the consolidation worksheet. Then a single sheet that consolidates all the cell ranges from selected spreadsheet files will open.

This YouTube page includes a video demonstration of how you can combine sheets from separate files with the Consolidation tool.

Third-Party Add-ons That You Can Merge Excel Files With

If Excel does not have enough built-in consolidation options for you, you can add a few third-party tools to the software.

• The Consolidate Worksheets Wizard is one third-party add-on that you can combine, consolidate and join worksheets from multiple Excel files with. The add-on is retailing at £23.95 on the Ablebits.com website, and it’s compatible with all the most recent Excel versions from 2007 up.

• Kutools is an Excel add-on that includes a plethora of tools. A combine is a tool in Kutools that you can merge multiple sheets from alternative Excel files into one spreadsheet with. That enables you to set up a spreadsheet that includes links to all the combined worksheets as in the snapshot directly below. This Kutools for Excel page provides further add-on details.

Merging Excel Files

So, you can merge and combine Excel files with the Copy and Paste, Consolidate and Move or Copy options, or third-party add-ons. With those options and tools, you can bring numerous sheets together from multiple Excel files into one single spreadsheet and consolidate their cell ranges.