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”.
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.”