Microsoft’s spread­sheet program Excel offers various functions to merge contents from different work­sheets. This guide explains step-by-step how to combine work­sheets with entries that are identical or different.

How to merge Excel work­sheets quick guide

  1. Select the cell where you want to merge the data.
  2. Click on Data > Con­sol­id­ate in the ribbon.
  3. In the Reference field, select the cells you want to merge and click Add to include them in the ref­er­ences.
  4. Click on OK to merge the values.
Note

The following in­struc­tions apply to Excel in Microsoft 365 and Excel versions 2021, 2019 and 2016.

What are the benefits of merging data in Excel?

The advantage of combining data from different work­sheets is that you can create new Excel tables to work with customer or company data. In contrast to the features for merging cells and moving cells, merging data with the Con­sol­id­ate function or the Power Query Editor allows you to combine data from separate work­sheets into one worksheet.

HiDrive Cloud Storage
Store and share your data on the go
  • Store, share and edit data easily
  • ISO-certified European data centres
  • Highly secure and GDPR compliant

How to merge data in Excel with Con­sol­id­ate

If you want to merge separate work­sheets in Excel, use the Con­sol­id­ate feature. To use this feature, your Excel file need to have at least two tabs. In the following example, we’ll combine customer data from NYC and LA.

Step 1: Open the file with the work­sheets that you want to merge. Click on + next to the worksheet names at the bottom of the window to create the worksheet where the data should be merged. Name the worksheet. A name like ‘Con­sol­id­ated’ will make it easy to find.

Image: Creating a new worksheet for merged data
Open the Excel file con­tain­ing the data you want to merge and click on the plus sign to create a new worksheet.

Step 2: In the new worksheet, select the cell where you want to merge the data. In this example, we’ve chosen cell A1. Now click on Data in the menu at the top of the window. In the Data Tools section, select the symbol for Con­sol­id­ate.

Image: Go to ‘Data’ in the ribbon and click on ‘Consolidate’ in the Data Tools field
Click on Data and then select Con­sol­id­ate from the Data Tools field.

Step 3: The Con­sol­id­ate menu will open. This is where you can choose how Excel should merge the data (i.e., sum, average, max). In this example, we’ll choose the Sum option so that the values are added together.

Image: Consolidate menu in Excel
In the Con­sol­id­ate menu, you can choose how the data is merged in Excel.

Step 4: Minimise the Con­sol­id­ate menu by clicking on the arrow under Reference. You’ll now see the menu labelled Con­sol­id­ate – Reference in its collapsed form.

Step 5: Go to the first worksheet and select the data that you want to merge. You’ll now see the cells you selected in the Con­sol­id­ate – Reference window. Next, click on the small arrow in Con­sol­id­ate - Reference.

Image: Selection of worksheet cells that should be merged
Select the data and then click on the arrow in Con­sol­id­ate - Reference.

Step 6: Add the selected reference to All ref­er­ences using the Add button. Repeat the process for the second worksheet.

Image: Add table contents to all references
Go to Add to transfer the reference to ‘All ref­er­ences’ and then repeat the same process for the second worksheet.

Step 7: Go to the worksheet where you plan to merge the sheets. Click on the tick boxes next to Top row and Left column to ensure the sheet is properly formatted. Then click on OK.

Step 8: You’ll now see the merged data from the other Excel sheets in the new worksheet.

Image: Screenshot of the merged data in a new worksheet
You can now see the merged data in a new worksheet.
Microsoft 365 Business
The Office you know, only better
  • Up to 50 GB Exchange email account
  • Outlook Web App and col­lab­or­a­tion tools
  • Expert support & setup service

How to merge data in Excel with the Power Query Editor

For simple merging op­er­a­tions where both work­sheets have the same format­ting and in­form­a­tion, the con­sol­id­a­tion feature will suffice. However, if you want to merge sheets that contain, for example, different values for the same customer group, the Power Query Editor is your best bet.

Step 1: Go to the first worksheet and select the contents. Then click on the Data menu and af­ter­wards on From Table/Range. After the Create Table window pops up, click OK.

Image: Click on ‘Data’ and ‘From Table/Range’ to open the Power Query Editor
Select the table in the first worksheet, click on Data and From Table/Range and then on OK.

Step 2: The Power Query Editor will now open with the contents of the table you selected. To add the contents of the second table, click on New Source in the upper right-hand corner of the Excel window. Select File and then Excel Workbook.

Image: Import an Excel table via ‘New source’, ‘File’ and ‘Excel’
Go to ‘New source’ in the Power Query Editor and import the second table via File > Excel.

Step 3: Import the Excel file con­tain­ing the second table and click OK in the navigator that opens.

Step 4: Click on Merge Queries and then again in the dropdown menu.

Image: Go to ‘Combine’ and ‘Merge Queries’ in the menu
Select Combine in the menu and then Merge Queries.

Step 5: A window labelled Merge will open. Select the two tables and choose the columns with the same data to ensure con­sist­ent format­ting.

Image: Select matching column(s)
Select the matching columns in the Merge menu and click on OK.

Step 6: To make the contents of the table visible, click on the arrow symbol under Table 2 and untick the boxes next to the columns with the same data (in this case Column1). Tick the boxes for different data that should be added.

Image: Remove the tick for the corresponding columns
Make the content you want to add to the table visible by clicking on the arrow symbol and high­light­ing the desired in­form­a­tion.

Step 7: The editor will then merge the contents you selected into a single table. Click on Close & Load to place the merged table in a new Excel worksheet.

Image: Complete the merge using the ‘Close & Load’ button
Now insert the merged tables into a new Excel worksheet under Close & Load.
Go to Main Menu