Practical Methods To Merge Excel Sheets

If you’re a frequent Excel user, you’ve probably come across these issues: you needed to merge multiple worksheets from your sales department into one master sheet to simplify your analysis. You might have also wanted to combine multiple sales worksheets into a single workbook. The copy-paste feature is effective for small data sets with identical structures. But for large data sets, 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 actually have a built-in function to merge sheets. You can either manually copy-paste the sheets or merge them using Coupler.io —an automation tool used to import data to Google Sheets, Microsoft Excel, and BigQuery from different sources. The tool is simple to use, and it doesn’t require any formulas or codes to work. You can check out more integrations with Excel here. 

First, you need to set up a Coupler.io account. Click Add New importer and configure the following parameters: source, destination, and schedule. 

Source

• Select Microsoft Excel as a source app from the Application list.
• Connect the Microsoft account (OneDrive or OneDrive for Business) you want to import data from. 
• Click on File to select a workbook from your Microsoft account.  
• Select the sheets you want to merge (in this case, Sheet 1, Sheet 2, and Sheet 3). Optionally, click Continue to merge a specific range of sheets. Click Jump to Destination Settings. 

Destination

• Select Microsoft Excel as the data import destination application. 
• Connect the destination Microsoft account. 
• Select the workbook to import your merged sheets to. 
• By default, Coupler.io copies data to the A1 cell. However, you can change this by using the Cell address setting. 

• Select the import mode of your data: select replace to fully update the previously imported information with the latest data. Alternatively, select append to add new rows under the last imported entries. 
• To customize automatic data refresh, use the Schedule feature. 

Schedule

• Toggle the Automatic data refresh to select your schedule preferences.

                                                                 6-Schedule-setting-on-Coupler.io

After configuring your schedule settings, click Save and Run to view the combined sheets. This is our result: 

As you can see in the image above, Coupler.io adds a “Sheet Name” column to help you differentiate the merged data. As well as merging sheets in Excel, you can also use the Coupler.io solution to combine Google Sheets into one. 

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 merge them using Coupler.io and then use the Remove Duplicate 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. 

• To remove the duplicates in the merged data, go to Data tab => Data Tools => Remove Duplicates. 

• In the Remove Duplicates box, untick the My data has headers option to remove duplicate headers. Then, use the Columns section to tick or untick the columns that contain duplicate values (in our case, we ticked all the columns). Click OK. 

• Click OK on the box that notifies you of the number of duplicates removed. As you can see in the image below, 3 duplicate values were removed, and we’re now left with unique values. 

How to merge two Excel sheets based on one column

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

• Before merging our sheets, we need to create separate Power Query connections for our Finance Table and Sales Table sheets. Let’s start with the Sales Table sheet. Select the cell you want to merge. On your Data tab, go to the Get & Transform section and click From Table.  

• From the subsequent Create Table box, tick or untick My table has headers. For this tutorial, we’ll leave it ticked because our Table has headers. Then click OK.

• From the resulting Power Query Editor, click on Close and Load. Select Close and Load To… from the drop-down list. 

• From the Load To dialog box, select Only Create Connection. Click Load.  

• A Workbook Queries box will open on the right-hand side of your workbook with the name of your table/range. Repeat the same process for other tables (in this case, Finance Table). 
• After creating a separate connection for the Finance Table sheet, the Workbook Queries box should contain both connections as shown below:  

Step 2: Merge the sheets

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

• In the Merge box, add Finance Table and Sales Table to the first and second drop-down lists, respectively. In both tables, select the matching column (in this case, Product ID). Once you select the matching column, it’ll turn green. 
• In the Join kind box, leave the default option as is. Click Ok. 

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: Fill-up the Sales Table

• Click on the two-sided arrow beside the Sales Table. From the subsequent dialogue box, select the column(s) you want to copy from your second table (in this case, Sales Table). 

• From the dialogue box shown below, keep the Expand box ticked. Select the column(s) you want to add from the second table. In our case, we selected Items and Amount ($). Untick the Use original column name as prefix box if you don’t want the column to be prefixed with the table name where it’s derived from. Click OK. 

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

Step 4: Import the merged data to Excel

• On the Power Query Editor box, select Close and Load To… from the Close and Load drop-down list. From the Load To box, click on Table and choose whether to save your table as a New worksheet or an Existing worksheet (we saved the table as a new worksheet). Then click Load. 

In Excel, our merged table will appear this way: 

Merge Excel sheets from different files

Merging sheets from different files can be quite tricky, especially when you’re dealing with large data sets. This is where Excel macros come into play. Macros are codes used to automate tasks in Excel. The Visual Basic for Applications (VBA) is one way to generate Excel macros. For instance, we’re going to use the 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

• On your new workbook, Go to the Developer tab => Code => Visual Basic to open the Visual Basic Editor (VBE). Alternatively, you can use Alt + F11 on Windows (or Fn + Opt + F11 if you use Mac), to open the VBE.

• Once the VBE box opens, right-click on the worksheet you want to merge and insert the following code: 

Sub mergeFiles()
    'Merges all files in a folder to a main file.
    
    'Define variables:
    Dim numberOfFilesChosen, i As Integer
    Dim tempFileDialog As FileDialog
    Dim mainWorkbook, 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

• To run your macro, select the Run/Sub Userform (F5) on the code window as shown in the image below. Alternatively, press F5. 

• From the desktop explorer, select the Excel files that you want to combine your sheets from. For this tutorial, we decided to compile our Excel files (Finance Table and Sales Table) into the same folder (Personal Library). Then click Open.

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

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 a method you can also use to merge Excel sheets with common columns.

Writing code is a viable option of merging sheets in Excel; however, not everyone knows how to code. This is where the Coupler.io solution could prove a boon: it’s an exceptional tool for merging multiple Excel sheets with ease on a custom schedule, irrespective of your coding experience. 

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