Coupler.io Blog

Practical Methods to Merge Excel Sheets

If you’re a frequent Excel user, you’ve probably come across these issues: you need to merge multiple worksheets from your sales department into one master sheet to simplify your analysis. You might also want to combine multiple sales spreadsheets into a single workbook. The copy-paste feature is effective for small datasets with identical structures. But for large datasets, errors are inevitable. So what tools or commands can you use to easily merge Excel files? Read this guide to find out.

How to merge Excel sheets

How would you merge the three Excel sheets (Sheet 1, Sheet 2, and Sheet 3) below?

Excel doesn’t have a one-click function to merge sheets. You can either manually copy sheets or merge them using Coupler.io. It is a reporting automation platform that automatically collects and blends data from over 400 sources and keeps it up to date on a schedule.

Let’s see how to merge data from the above three sheets using Coupler.io.

Step 1: Collect the data

We selected the source and destination as Microsoft Excel in the form below. Use it to get started.

In the source settings, connect your Microsoft account, select the Excel file and the 3 sheets you want to merge.

Step 2: Transform data

In this step, you’ll see a preview of the merged data from the 3 sheets. You can also organize and transform your data using the following options.

Once the data is ready, go to destination settings. Select the file and Excel spreadsheet where you want to export this combined data.

You can also send this consolidated data to other destinations like BigQuery, Data Studio, Google Sheets, and more for further analysis and visualization. Check out more integrations with Excel.

Step 3: Schedule transfer

Turn on automatic data refresh. Specify the details of the data transfer – interval, days of the week, time preferences, and timezone.

The combined data from the three sheets looks like this.

Similarly, you can also combine multiple Google Sheets into one.

Merge sheets using name patterns

If you plan to add more worksheets over time and they follow a naming pattern, you can use a regular expression instead of selecting each sheet individually. When new sheets match your pattern, they’ll be automatically included in future imports.

For instance, let’s say you have monthly ads budget reports named costs Jan 2026, costs Feb 2026, costs Mar 2026, and so on. Rather than listing each one manually, apply the pattern {sheet-name}.+:

In this example: costs .+ 2026

You can apply regular expressions to various naming scenarios. Below are the most frequently used patterns:

Match all sheets:

Match sheets by year:

Match sheets by name prefix (starts with):

Match sheets by name content (contains):

Match sheets by name suffix (ends with):

Merge Excel sheets without duplicates

Excel doesn’t provide a built-in function to merge sheets and remove duplicates in one click. However, you can either copy-paste sheets or consolidate them using Coupler.io and then use the Remove Duplicates command to remove duplicate entries.

For example, let’s remove duplicate entries (that is, the red and blue cells) from the Data I sheet below.

How to merge two Excel sheets based on one column

You can do this with two methods.

Let’s see how these methods work and which one is best for you.

Method 1: Coupler.io

Suppose you have two Excel sheets in the same file with sheet names, Finance Table and Sales Table. You want to merge the data from these two tables based on the column ‘Product ID.’

The source and destination are preselected in the form below. Use it to get started.

In the source settings, connect your Microsoft account, select the Excel file and the sheet you want to combine. In our case, we chose the sheet ‘Finance Table.’

Then add one more source. Select the source app as Excel, connect your account, and select the second sheet ‘Sales Table’ from your Excel file.

After connecting the Excel sheets, move on to the next step. Here, you’ll see two options.

Select join and specify the key column as ‘Product ID’ in both sheets.

Once you proceed, you’ll see a preview of the joined data from the two Excel sheets based on the key column ‘Product ID.’

Specify where to load the data in the destination settings. Once you save and run the data flow, you can see the merged data from two sheets (Finance table and Sales table) based on the column ‘Product ID.’

Method 2: Power Query

You can use Power Query to merge Excel sheets based on a common column. Power Query is available as a free add-in on Excel 2010 and 2013, while it’s a built-in feature from Excel 2016 onward.

Let’s merge two sheets (Finance Table and Sales Table) with a matching column (Product ID) as seen in the screenshots below:

Step 1: Create Power Query connections

Step 2: Merge the sheets

Select a sheet, go to Data => New Query => Combine Queries => Merge as shown in the screenshot below:

After completing the Merge steps, the Power Query Editor will add an extra column (Sales Table). But the extra column contains only Table. In the next steps, we’ll show you how to fix it.

Step 3: Expand the Sales Table column

After merging, your new table will contain records from both tables.

Step 4: Import the merged data to Excel

In Excel, our merged table will appear this way in a single sheet:

Merge Excel sheets from different files

Merging sheets from different files can be quite tricky, especially when you’re dealing with large datasets. This is where Excel macros come into play. Macros are codes used to automate tasks in Excel. Visual Basic for Applications (VBA) is the language used to write Excel macros. Here, we’re going to use VBA code to merge sheets from different Excel files.

For ease of access, we advise that you compile your Excel files into the same folder. Here are snapshots of the Excel files we’ll be merging.

Step 1: Insert macro to workbook

Sub mergeFiles()
    'Merges the selected files into the main file.
    
    'Define variables:
    Dim numberOfFilesChosen As Integer, i As Integer
    Dim tempFileDialog As FileDialog
    Dim mainWorkbook As Workbook, sourceWorkbook As Workbook
    Dim tempWorkSheet As Worksheet
    
    Set mainWorkbook = Application.ActiveWorkbook
    Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    'Allow the user to select multiple workbooks
    tempFileDialog.AllowMultiSelect = True
    
    numberOfFilesChosen = tempFileDialog.Show
    
    'Loop through all selected workbooks
    For i = 1 To tempFileDialog.SelectedItems.Count
        
        'Open each workbook
        Workbooks.Open tempFileDialog.SelectedItems(i)
        
        Set sourceWorkbook = ActiveWorkbook
        
        'Copy each worksheet to the end of the main workbook
        For Each tempWorkSheet In sourceWorkbook.Worksheets
            tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
        Next tempWorkSheet
        
        'Close the source workbook
        sourceWorkbook.Close
    Next i
    
End Sub

We’ve successfully merged Excel sheets from different Excel files into a new workbook.

Using VBA to merge multiple sheets is not a walk in the park. Therefore, you can always use Coupler.io to do this. It lets you merge data from different files without coding and organize data on the go.

What’s the best option?

Excel macros are perfect for merging Excel sheets from different files for users who know how to code. All you need to do is write the proper piece of code and run it on demand. Power Query is also quite useful to merge Excel sheets with common columns.

But if writing code is not your cup of tea and you do not want to juggle between different tools to merge Excel sheets, then Coupler.io is your all-in-one no-code solution. You can combine multiple Excel files and sheets automatically on a custom schedule.

Lastly, before choosing a merging option in Excel, ensure you consider the structure, content, and volume of your data. Now, go on and Excel!

Exit mobile version