How to copy data from multiple sheets to one sheet in Excel

By Randall Blackburn Updated February 12, 2019

Inhaltsverzeichnis Show

Method 1: Copy and paste worksheets manually
Method 2: Use the INDIRECT formula to merge sheets
Method 3: Merge sheets with a VBA Macro
(New) Method 5: Merge sheets using the Office clipboard

If you manage data in multiple Excel spreadsheets, you can use the Consolidate Data wizard to merge data from several spreadsheets or workbooks into one master sheet. The Consolidate Data wizard enables you to merge data by cell position or by category name. If all of the spreadsheets use the same category headings, you can merge data by category name regardless of where the categories and data are located on the spreadsheets. You can also drag to select the desired cells to merge in the wizard to reference the cells by position only, regardless of category name or data type.

You'll often want to have Excel consolidate data from multiple spreadsheets with similar data. This is often useful for adding together sales data from multiple sources, combining entries from employee timesheets or consolidating data from survey responses.

Whatever the reason, you can merge data from one Excel spreadsheet to another using Excel's Consolidate Data wizard. If you have only a few spreadsheets to combine, and the column headers and orders are identical, you can also simply copy and paste the data into a master sheet, though the sheet won't automatically update if the others change.

Open Excel, and then open the first of the spreadsheets to be merged. The spreadsheet can be in the same workbook or in a different workbook file. Ideally, you should keep the consolidation process simple by opening all spreadsheets to be merged in the same workbook, or Excel file, but this isn't always possible depending on how the sheets are created
Click “File," and then click “New" to create a blank spreadsheet. This blank spreadsheet will be the “master” and will contain the merged data from your open spreadsheets.
Click the upper left cell in the master spreadsheet where the data from the first spreadsheet will begin.
Click the “Data” tab in the Excel ribbon menu, and then click “Consolidate” in the Data Tools section. The Consolidate Data wizard launches.
Click the “Function” drop-down box, and then click “Sum” if you just want to replicate the original data in the master sheet. You can also click another function to perform a calculation on the data as the sheets are merged.
Click the “Browse” button and navigate to the workbook containing the first spreadsheet to merge -- this is necessary only if the spreadsheet is in a different workbook. Click the file, and then click “OK.” The file path is added to cell references for the merge. This is why having all spreadsheets open in a single workbook simplifies the process. If all spreadsheets are located in the same workbook, the file path is not necessary in the References field.
Click the “Top Row” check box to merge by categories listed along the top row. Click the “Left Column" option if categories are listed down the left side of the spreadsheet in row headers. To merge by position and not category, leave the “Top Row” and Left Column” check boxes empty.
Click the first cell in the first spreadsheet to be merged, and then drag the cursor over all cells to merge. The reference is updated with the selected cells in the Reference field of the Consolidate Data dialog box. Note that you can click the “Collapse / Expand” button on the right side of the Reference field to collapse the dialog box so you can easily access the spreadsheet. Click the button once again to restore the dialog box.
Click the “Add” button. The references are added to the wizard.
Click “Browse,” and then navigate to the second spreadsheet if located in another workbook. Click the sheet, and then click “OK.”
Drag the cursor over the data block to merge in the second spreadsheet, and then click “Add.” The references from the second sheet are added to the wizard.

Repeat opening each spreadsheet, and then selecting the data to merge. Click the “Add” button in the Consolidate Data wizard to add the references. After all references from all spreadsheets are listed in the Consolidate Data wizard, click “OK.” The data from the selected spreadsheets is merged into the master sheet. Be sure to save the master sheet once all data is consolidated.

The Consolidate Data dialog box includes the “Create Links to Source Data” option. When this option is selected, the merged data includes links to the data in the original spreadsheet. When the original spreadsheet is updated, the merged spreadsheet is automatically updated, similar to an Excel Pivot Table.

Let’s assume you have many worksheets, all in the same structure. Or they are at least in a similar structure. Now, you want to combine them into one worksheet. For example copying them underneath each other so that you can conduct lookups or insert PivotTables. In this article, you learn four methods to merge sheets in Excel.

Method 1: Copy and paste worksheets manually

In many cases it’s probably the fastest way to just copy and paste each sheet separately. That depends of course on the number of worksheets you want to combine and their structure. Some comments:

Try to use keyboard shortcuts as much as possible. For example for selecting the complete worksheet (Ctrl + A), copying the data (Ctrl + C), navigating to your combined worksheet (Ctrl + Page Up or Page Down) and pasting the copied cells (Ctrl + V).
Also the shortcut of pressing Ctrl on the keyboard and clicking on the little arrow in the left bottom corner of your worksheet could help. That way you jump to the first or last worksheet in your Excel workbook.
This method is especially useful if you just have to merge sheets once. If you need to do it repeatedly (for example you get new inputs every week or month) it’s probably better to check out methods 2 to 4 below.

Method 2: Use the INDIRECT formula to merge sheets

You can use Excel formulas to combine data from all worksheets. The main formula is INDIRECT.

This method has some disadvantages, though.

The INDIRECT formula in general is slow because it’s volatile. That means, it calculates each time Excel calculates something.
Using a combination of INDIRECT is usually unstable and error prone.
It takes some work to set up the INDIRECT formula.

On the other hand, it has one major advantage: If you spend effort to set it up, this method is dynamic. That means when your input updates, the merged worksheet updates as well.

Approach

Set up the INDIRECT formula for merging sheets.

The INDIRECT formula can access any cell from a link (or better: an address) you provide. Please refer to this article to learn more about the INDIRECT formula. So you only have to provide the addresses for each cell in each worksheet you want to combine. Therefore, you should prepare a worksheet the following way (please refer to the screenshot on the right-hand side):

Column A contains the sheet name.
Column B contains the row number.
Starting from Column C, you should add the column letters.

So let’s assume that you want to get the value from cell A1 of Sheet1. You would need then all the parts ‘Sheet1’, column ‘A’ and row ‘1’. Combining them in the INDIRECT formula would lead to the following formula. The formula in cell C4 is =INDIRECT(“‘”&$A4&”‘!”&C$2&$B4) .

Download

Copy this worksheet into your Excel file and all sheets are merged automatically.

You want to save some time? We prepared a worksheet which can merge sheets automatically. What do you have to do? Download this workbook (~7 MB) and copy the only sheet into your own workbook. That’s it.

Please note the following comments.

This method requires to enable macros (a list of all worksheets in your workbook is automatically created). When you want to save your workbook, you will be asked to switch to the XSLM file format.
This model works for up to 50 sheets with 200 rows each (10,000 cells are prepared). If you need more, you have to extend it. The reason for this restriction is that the file is already quite large and requires some calculation performance.

Method 3: Merge sheets with a VBA Macro

You feel confident enough to use a simple VBA macro? Please insert the following code into a new VBA module. If you need assistance with VBA, please refer to this article.

Sub Merge_Sheets() 'Insert a new worksheet Sheets.Add 'Rename the new worksheet ActiveSheet.Name = "ProfEx_Merged_Sheet" 'Loop through worksheets and copy the to your new worksheet For Each ws In Worksheets ws.Activate 'Don't copy the merged sheet again If ws.Name <> "ProfEx_Merged_Sheet" Then ws.UsedRange.Select Selection.Copy Sheets("ProfEx_Merged_Sheet").Activate 'Select the last filled cell ActiveSheet.Range("A1048576").Select Selection.End(xlUp).Select 'For the first worksheet you don't need to go down one cell If ActiveCell.Address <> "$A$1" Then ActiveCell.Offset(1, 0).Select End If 'Instead of just paste, you can also paste as link, as values etc. ActiveSheet.Paste End If Next End Sub

Combine worksheets with Professor Excel Tools.

You like to use the most convenient way? Try the Excel add-in Professor Excel Tools.

Just select all the worksheets you’d like to merge,
click the button “Merge Sheets” and
click on “Start”.

Alternatively, you can further refine your desired settings: Do you want to add the original sheet name in column A? No problem.

Also, define the copy & paste mode as shown in the screenshot on the right-hand side.

(New) Method 5: Merge sheets using the Office clipboard

The first method above already dealt with copying and pasting sheets manually. There is one more trick here: Use the Excel clipboard to merge sheets. It’s actually quite simple, just follow these steps.

Steps 1-3: Use the Office clipboard to quickly copy worksheets underneath each other.

Open the clipboard: Click on the small arrow in the right bottom corner of the Clipboard section (on the Home ribbon).
Now you can see the clipboard.
Next, go through each worksheet. Copy all ranges which you later want to merge on one worksheet.

Steps 4-5: Use the “Paste All” button of the clipboard to merge the sheets.

Now, you can see all your copied ranges in the clipboard.
Go to the sheet where you want to paste them underneath each other. Select the first cell.
Click on “Paste all”

That’s it. Especially with larger files, this method could save some time compared to method number 1 above. One small disadvantage: You can further adjust the pasting method, for example using paste special to paste values only.