5 Easy Ways to Merge Data from Two Excel Sheets

Merging data from multiple Excel sheets can streamline your workflow, making it easier to organize, analyze, and report information efficiently. Whether you're combining sales data, inventory lists, or any other datasets, Excel offers several methods to get the job done. Here's how you can merge data from two Excel sheets in five straightforward steps.

1. Using VLOOKUP Function

The VLOOKUP function is one of the most common ways to merge data when you have a common identifier in both sheets.

• Step-by-Step Process:
  1. In the sheet where you want to combine data, ensure there’s a unique column to match the data from the second sheet.
  2. Type the following formula in the cell where you want the merged data to appear: =VLOOKUP(A2,Sheet2!A:B,2,FALSE), assuming Sheet2 has the lookup column in column A and the value to merge in column B.
  3. Drag the formula down to copy it to other cells.

💡 Note: The VLOOKUP method assumes the lookup value is on the left side of the column you want to merge from.

2. Using Index and Match

For more flexible merging where the lookup column doesn’t need to be on the left, use the INDEX and MATCH functions together.

• Step-by-Step Process:
  1. Select the cell where you want the merged data to appear.
  2. Enter the formula: =INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)).
  3. Extend the formula down the column to apply it to other cells.

3. Power Query to Merge Sheets

Power Query is a powerful tool in Excel for data manipulation, including merging sheets.

• Step-by-Step Process:
  1. Select any cell in your first sheet and go to Data > From Table/Range.
  2. In the Power Query Editor, go to Home > Merge Queries to combine with another table (sheet).
  3. Choose the second sheet and ensure the matching columns are set correctly.
  4. Select the columns you want to merge and click OK.
  5. Choose the join kind (e.g., Left Outer, Full Outer, Inner, etc.) as per your need.
  6. Close & Load the query back into Excel.

4. Using XLOOKUP (Excel 365)

If you’re using Office 365 or Excel 2021, XLOOKUP provides a modern alternative to VLOOKUP.

• Step-by-Step Process:
  1. Select the cell where you want the merged data.
  2. Use the formula: =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B).
  3. Copy the formula down to merge data for the rest of the cells.

5. Consolidate Feature

When dealing with multiple sheets containing similar data, Excel’s Consolidate feature can help merge data automatically.

• Step-by-Step Process:
  1. Select the data range where you want to see the consolidated result.
  2. Go to Data > Consolidate in the Ribbon.
  3. Choose the function you want to use for consolidation (e.g., Sum, Count, Average).
  4. Add the ranges from both sheets that you want to merge.
  5. Choose whether to link to the source data or not.
  6. Click OK to apply.

These methods provide a variety of ways to merge data from two Excel sheets, each with its own advantages depending on your data structure and the specific requirements of your task. By understanding and applying these techniques, you can enhance your productivity and make data management a seamless part of your workflow.

What if I want to merge data without any common identifiers?

+

Without common identifiers, merging data becomes more challenging. Consider using the Consolidate feature for basic operations or manually creating a relationship between sheets.

Can I use Power Query with older versions of Excel?

+

Power Query, known as Get & Transform in older Excel versions, is available from Excel 2010 with the Power Query add-in or in Excel 2016 and later as part of the standard features.

How can I update merged data dynamically?

+

Using formulas like VLOOKUP, INDEX/MATCH, or XLOOKUP will allow your merged data to update automatically as changes are made to the source data.