Site icon Professor Excel

Merge Sheets: Easily Copy Excel Sheets Underneath on One Sheet!

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:

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.

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):

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.

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

Method 4: Combine sheets with “Professor Excel Tools”

Combine worksheets with Professor Excel Tools.

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

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.


This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.


(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.
  1. Open the clipboard: Click on the small arrow in the right bottom corner of the Clipboard section (on the Home ribbon).
  2. Now you can see the clipboard.
  3. 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.
  1. Now, you can see all your copied ranges in the clipboard.
  2. Go to the sheet where you want to paste them underneath each other. Select the first cell.
  3. 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.

Exit mobile version