Merging data in Excel: A step-by-step guide
Microsoft Excel comes with a variety of ways to merge data from different worksheets. In this guide, we’ll show you step-by-step how to merge data in Excel.
Advantages of merging data in Excel
There are two ways that data can be merged in Excel: with the “Consolidate” function or with the “Power Query Editor”. The advantage of combining data from different worksheets is that you can create new Excel tables for working with customer or company data. In contrast with the features for merging cells and moving cells, you can combine data from separate worksheets into one table.
Merging data in Excel with “Consolidate”
If you want to combine separate tables in Excel, use the “Consolidate” feature. The prerequisite is that your Excel file has at least two worksheets. In the following example, customer data is combined.
Step 1: Open the file with the worksheets that you want to merge. Click on the plus sign next to the worksheet names at the bottom of the window to create the worksheet where you will merge the data. Name the worksheet appropriately (e.g. “Merge”).
Step 2: In the new worksheet, select the cell where you want to merge the data. In this example, it’s cell A1. Now click on “Data” in the menu at the top of the window; in the section “Data Tools”, select the symbol for “Consolidate”.
Step 3: The Consolidate menu will open. This is where you can set how Excel merges the data (i.e. sum, average, max). In this example, we’ll choose the “Sum” option to have the values added together.
Step 4: Collapse the Consolidate menu by clicking on the arrow under “Reference”. You’ll now see the menu labeled “Consolidate - 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 Consolidate - Reference window. Next, click on the small arrow in Consolidate - Reference.
Step 6: Add the selected reference to “All references” using the “Add” button. Repeat the process for the second worksheet.
Step 7: Go to the worksheet where you plan to merge the tables (here the worksheet named “Merge”). Click on the checkboxes for “Top row” and “Left column” to ensure the proper formatting of the table. Then click on “OK”.
Step 8: You’ll now see the merged Excel data in a new table.
Merging data in Excel with the Power Query Editor
For simple merging operations where both tables have the same formatting and contents, the consolidation feature will suffice. However, if you want to merge tables that contain, for example, different values for the same customer group, the Power Query Editor will be your best bet.
Step 1: Go to the first worksheet and select the table. Then click on the “Data” menu and afterwards on “From Table/Range”. After the “Create Table” window pops up, click “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”.
Step 3: Import the Excel file containing the second table and click “OK” in the navigator that opens.
Step 4: Click on “Merge Queries” and then again in the dropdown menu on “Merge Queries”.
Step 5: A window labeled “Merge” will open. Select the two tables and choose the columns with matching contents, so that the common formatting will be preserved.
Step 6: To make the contents of the table visible, click on the arrow button under “Table 2” and uncheck the boxes next to columns with matching contents (in this case Column1). Check the boxes for contents that should be added.
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.