How To Merge and Combine 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.
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
- 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.”
- 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 didn’t 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
- 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.
- 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.
- 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 doesn’t enable you to select cell ranges, but it’s fine for merging full sheets.
Open the files you want to move, and a spreadsheet to copy them to. Then right-click a sheet tab at the bottom of Excel’s window. Select Move or Copy to open the window shown directly below.
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 don’t select that option, the sheet moves from one spreadsheet to the other. 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.
First, open a blank spreadsheet, otherwise the master worksheet, which will include the merged cell ranges. Click the Data tab from which you can select a Consolidate option. That opens a Consolidate dialog box that includes a Function drop-down menu. Select Sum from the drop-down menu.
Next, click Browse on the Consolidate window. Then you can select to open a spreadsheet file that includes a cell range you need to merge. The selected file path is then included within the Reference box.
Press the Collapse Dialog button on the right of the Reference box to select a cell range within the selected spreadsheet. After selecting the required cells, you can press the Expand Dialog button on the right of the Consolidate – Reference window to return to the main Consolidate window. Then press the Add button, and you can select the cell ranges from all the other spreadsheet files much the same.
When you’ve 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 doesn’t 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.
So you can merge and combine Excel files with the Copy and Paste, Consolidate and Move or Copy options,000 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.