The fastest way to merge sheets depends on whether your data has headers and how the sheets are organized

If your sheets have the same columns in the same order, you can copy all the data from one sheet and paste it below the data in another — Excel will stack the rows automatically. If the sheets have different structures or you need to pull specific columns, you'll use formulas or the built-in consolidation tools instead. The method you choose depends on how many sheets you're combining, whether the data changes often, and whether you need the merged result to update automatically when the source sheets change.

Most people start with copy-paste because it's the fastest for a one-time merge. But if you're combining sheets regularly or the source data updates, formulas or Power Query will save you time in the long run by handling the merge automatically.

Key Takeaways

  • Copy and paste works fastest when sheets have identical column layouts and you only need to do this once.
  • VLOOKUP or INDEX/MATCH formulas let you pull matching data from multiple sheets based on a common identifier like an ID number or name.
  • The Consolidate tool (on the Data menu) automatically combines data from multiple sheets using sum, average, count, or other calculations.
  • Power Query (in Excel 2016 and later) can combine sheets and automatically refresh when source data changes, but requires more setup.
  • Keeping sheets separate with formulas that reference them is often simpler than merging if you need to update the source data regularly.

Copy and paste when sheets have identical structure

The simplest merge is when all your sheets have the same columns in the same order. Open the first sheet and select all the data including headers. Press Ctrl+C (or Cmd+C on Mac) to copy. Click on the second sheet, click on the cell below the last row of existing data, and press Ctrl+V to paste. Repeat for each additional sheet.

This method works best when you're combining a small number of sheets and don't expect the source data to change. If you paste over the headers each time, you'll end up with duplicate header rows in the middle of your data — select those rows and right-click to delete them. A cleaner approach is to copy only the data rows (not headers) from the second sheet onward, so the headers appear only once at the top.

Use formulas to pull data from multiple sheets by matching a key field

When sheets have different structures or you need to match data based on a common field (like a customer ID or product code), formulas are more reliable than copying. VLOOKUP is the most common choice. The formula looks up a value in one sheet and returns a matching value from another sheet in the same row.

The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For example, if you're on Sheet1 and want to find a customer's email from Sheet2 based on their ID, you would write =VLOOKUP(A2, Sheet2!A:D, 4, FALSE). This looks for the value in cell A2 (the ID) within columns A through D on Sheet2, and returns the value from the 4th column (email). The FALSE at the end means "exact match only."

INDEX/MATCH is more flexible than VLOOKUP because it works with columns in any order and can search in any direction. The formula is =INDEX(return_range, MATCH(lookup_value, search_range, 0)). If you're looking for a customer's phone number in Sheet2 based on their name in Sheet1, you would write =INDEX(Sheet2!C:C, MATCH(A2, Sheet2!B:B, 0)). This finds the name from A2 in Sheet2's column B, then returns the corresponding value from Sheet2's column C.

Consolidate data using automatic sum, average, or count

If you need to combine numerical data from multiple sheets and calculate totals, the Consolidate tool saves time. This tool is built into Excel and works when your sheets have the same row and column labels but different data values — for example, sales figures from different regions or different months, all organized the same way.

First, create a new sheet where you want the consolidated result to appear. Click on the Data menu, then select Consolidate (the exact location varies by Excel version — look for it under Data Tools or in the Analysis section). In the dialog box, choose the calculation you want (Sum, Average, Count, etc.). Then click the button next to "Reference" and select the data range from your first sheet, including headers. Click Add, then repeat for each additional sheet. Click OK, and Excel will combine all the data using your chosen calculation.

The Consolidate tool creates a static result — it doesn't update automatically if the source data changes. If you need live updates, you'll need to re-run the consolidation or use Power Query instead. For most one-time consolidations, this is the fastest built-in option.

Use Power Query to merge sheets with automatic updates

Power Query (called Get & Transform in some Excel versions) is built into Excel 2016 and later. It lets you combine multiple sheets and set up the merge to refresh automatically whenever the source data changes. This is useful when you're working with data that updates regularly.

Open a new sheet where you want the merged data. Go to the Data menu and click Get Data (or New Query). Select From Other Sources, then Combine Sheets. Excel will scan your workbook and show you the sheets available. Select the sheets you want to merge and the column you want to match them on (if they have different structures). Power Query will create a query that combines the data. You can then refresh this query anytime by right-clicking it and selecting Refresh, or set it to refresh automatically when you open the file.

Power Query has a steeper learning curve than formulas or copy-paste, but it's worth learning if you regularly combine large datasets or need the merge to stay current without manual work. Once you set it up, you don't have to touch it again unless the structure of your source sheets changes.

Keep sheets separate with cross-sheet formulas instead of merging

Before you merge sheets, consider whether you actually need to. If the source sheets change frequently or you need to track data by its original source, keeping sheets separate and using formulas to reference them is often simpler. You can create a summary sheet that pulls data from multiple sheets without physically combining them.

For example, if you have a sheet for each month's sales, you can create a "Year Total" sheet with formulas like =SUM(January!B:B, February!B:B, March!B:B) to add up the same column across all months. This way, when January's data updates, the year total updates automatically without you having to re-merge anything. This approach keeps your original data intact and makes it easier to audit where numbers came from.

Troubleshooting common merge problems

If your VLOOKUP or INDEX/MATCH formula returns #N/A, the lookup value doesn't exist in the other sheet, or the spelling or spacing doesn't match exactly. Check for extra spaces by using the TRIM function: =VLOOKUP(TRIM(A2), Sheet2!A:D, 4, FALSE). If the formula returns #REF!, the sheet name or range you referenced no longer exists — check that all sheet names are spelled correctly and the ranges are still valid.

When copy-pasting creates duplicate headers, select the duplicate rows and right-click to delete them. If the Consolidate tool isn't combining data the way you expect, make sure all sheets have identical row and column labels — even a difference in capitalization or spacing will cause the tool to treat them as separate categories. Test your merge on a copy of the file first so you can undo mistakes without losing the original data.

Frequently Asked Questions

Can I merge sheets that have different column orders?

Copy-paste won't work reliably because the data will end up in the wrong columns. Use VLOOKUP or INDEX/MATCH instead, which match data based on a key field rather than position. This way, the column order doesn't matter.

What's the difference between merging and consolidating?

Merging combines all rows from multiple sheets into one sheet, keeping all the original data. Consolidating calculates a summary (like a total or average) across multiple sheets. Use merge when you want all the detail; use consolidate when you want a summary.

Will my merged data update automatically if the source sheets change?

Not with copy-paste or the Consolidate tool — those create static copies. Formulas (VLOOKUP, INDEX/MATCH) and Power Query do update automatically. If you use formulas, the merged sheet updates whenever you open the file or press F9 to recalculate.

How do I merge sheets from different Excel files?

Open both files. In the file where you want the merged result, use formulas that reference the other file: =VLOOKUP(A2, '[OtherFile.xlsx]Sheet1'!A:D, 4, FALSE). The file path goes in square brackets, followed by the sheet name in single quotes. Both files must be open for the formula to work, or you can save the reference and it will pull from the closed file when you open it again.

Can I undo a merge if I made a mistake?

Yes — press Ctrl+Z when ready after pasting to undo the paste. If you've already saved the file, you can't undo. This is why it's good practice to test your merge on a copy of the file first, or keep the original sheets intact and create the merge on a new sheet.