How To Merge and Combine Excel Files
Do you need to combine, or merge, sheets in multiple Excel files into one single spreadsheet? If so, there are various ways you can combine worksheets, or selected data, from separate Excel spreadsheets into one. You can combine full sheets or merge selected cell ranges from multiple spreadsheets into one file. Excel has built-in options for data consolidation, but there are also a few handy add-ons for the application that you can merge sheets with.
Copy and Paste Cell Ranges From Multiple Spreadsheets
The good old copy (Ctrl + C) and paste (Ctrl + V) hotkeys might be all you’ll need to combine Excel files with. You can copy a range of cells in a sheet to the clipboard. That cell range can then be pasted into a new spreadsheet file. Excel even includes Copy and Paste options on its menus.
To copy and paste data, open a sheet in a spreadsheet you need to copy a cell range from. 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. Excel highlights the copied cell area as shown below.
Note that you can’t copy multiple items to the clipboard in Windows, so paste each cell range first before copying further cells. However, you can copy multiple items with third-party clipboard manager software packages covered in this Tech Junkie guide.
Open a blank spreadsheet to include the merged data in. 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. Or select Paste Special to select further pasting options from the submenu shown in the shot directly below.
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 with that option. The option doesn’t enable you to select cell ranges, but it’s fine for merging full sheets.
Open the files to copy, or move, sheets from and a spreadsheet to copy them to. Then right-click a sheet tab to copy at the bottom of Excel’s window. Select Move or Copy to open the window shown directly below.
Select a spreadsheet file to copy the sheet to from the To book drop-down menu. Click the Create a copy check box 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 copy 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 in 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. 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 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.