The fastest way to merge sheets depends on what you're combining

If you mean putting all the data from two or more sheets into a single sheet, the method depends on whether the sheets have the same columns, whether you need the data to stay linked to the originals, and how often you'll repeat this task. Excel doesn't have a single "merge sheets" button — instead, you choose from copy-and-paste, formulas that pull data across sheets, or consolidation tools built into the Data menu.

The simplest approach for a one-time job is copying and pasting. The most flexible approach for ongoing work is using formulas. The most powerful approach for combining data with matching row or column labels is the Consolidate tool, which can sum, average, or count values across multiple sheets automatically.

Key Takeaways

  • Copy and paste works fastest when you have two or three sheets with the same column structure and you only need to do it once.
  • Formulas like INDIRECT or INDEX/MATCH let you pull data from other sheets and update automatically if the source data changes.
  • The Consolidate tool (on the Data menu) is built for combining sheets that have matching row labels or column headers, and can sum or average values across them.
  • If sheets have different structures or you're combining many sheets, you may need to clean up the data first so the columns align.

Copy and paste when you have matching columns

If both sheets have the same columns in the same order, copying and pasting is the quickest method. Open the first sheet, select all the data (not the header row if you want to avoid duplicating it), copy it, go to the destination sheet, and paste it below the existing data.

To select all data in a sheet quickly: click the cell in the top-left corner of your data, press Ctrl+Shift+End (on Windows) or Command+Shift+End (on Mac), then copy. If you want to include headers, start from the first cell with data. If you want to skip headers on the second sheet onward, select from the first data row instead.

This method works best when you're combining two or three sheets and don't plan to update the source sheets afterward. If the source data changes later, the pasted data won't update — you'd have to paste again manually.

Use formulas to pull data that updates automatically

If you need the merged sheet to stay in sync with the original sheets, use formulas instead of pasting. The simplest formula is INDIRECT, which lets you reference a cell on another sheet by building the sheet name as text.

For example, if you have sheets named "January", "February", and "March", and you want to pull the value from cell A1 on each sheet into a single summary sheet, you can write:

=INDIRECT("January!A1")

Then copy that formula down or across. If the source data changes, the formula updates automatically. You can also use INDEX and MATCH to pull data based on a lookup value, which is more flexible if the sheets have different structures or if rows are in a different order.

Formulas are slower to set up than copy-and-paste, but they're worth the effort if you update the source sheets regularly or if you need to combine many sheets at once.

The Consolidate tool for sheets with matching labels

Excel's Consolidate feature is designed specifically for combining multiple sheets that have the same row labels or column headers. It can sum, average, count, or perform other calculations across sheets automatically.

To use it: first, create a new sheet where you want the combined data to appear. Then go to the Data menu and click Consolidate (on Windows, it's under Data > Consolidate; on Mac, it's under Data > Consolidate). A dialog box opens. Click Browse next to the "Reference" field, select the first sheet and the range of data you want to combine (for example, Sheet1!A1:D100), then click Add. Repeat for each sheet you want to include.

At the bottom of the dialog, check the boxes next to "Use labels in" if your data has headers in the top row or leftmost column — this tells Excel to match rows and columns by their labels rather than by position. Then click OK. Excel combines the data and performs the calculation (sum, average, etc.) for matching labels.

This tool is powerful but works best when all your sheets have identical row and column structures. If one sheet is missing a row or has columns in a different order, the consolidation may not work as expected.

Clean up data before merging if columns don't match

If your sheets have different column names, different numbers of columns, or data in a different order, you'll need to standardize them first. Open each sheet and rename columns so they match exactly — Excel is case-sensitive, so "Sales" and "sales" are treated as different headers.

Delete any columns you don't need in the merged result. If one sheet has a column the others don't, decide whether to include it in the final sheet (and fill it with blanks for rows from other sheets) or leave it out. Rearrange columns so they're in the same order across all sheets.

Once the structures match, use whichever merge method fits your situation: copy-and-paste for a one-time job, formulas if you need updates, or Consolidate if you're summing or averaging matching rows.

Merging sheets with different structures

If your sheets have completely different structures — for example, one has customer names and one has product names — you're not really merging sheets, you're combining datasets. This usually requires a lookup formula like VLOOKUP or INDEX/MATCH to match rows based on a common identifier (like a customer ID or order number).

Create a new sheet with all the unique identifiers from both sheets, then use formulas to pull the corresponding data from each sheet. This is more complex than simple merging, but it's the right approach when you need to combine information from sources that don't have the same structure.

Frequently Asked Questions

Can I merge sheets without losing the original data?

Yes. Copy-and-paste, formulas, and Consolidate all leave the original sheets unchanged. If you use copy-and-paste, the data is duplicated in the new sheet, so you have two copies. If you use formulas, the original sheets remain the source of truth and the merged sheet pulls from them.

What if I have 10 sheets to merge?

Copy-and-paste becomes tedious with that many sheets. Use formulas with INDIRECT if all sheets have the same structure, or use Consolidate if you're summing or averaging values. Both scale better than manual copying.

Does merging sheets delete the original sheets?

No. Merging combines the data into a new location — it doesn't remove the source sheets. You can delete them afterward if you no longer need them, but the merge process itself doesn't touch them.

Can I merge sheets from different Excel files?

Yes, but it's more work. You can copy and paste between files, or you can use formulas with external references (for example, =[OtherFile.xlsx]Sheet1!A1). External references can break if you move or rename the other file, so copy-and-paste is usually safer for one-time merges.

What's the difference between merging sheets and consolidating?

Merging usually means combining all rows from multiple sheets into one sheet. Consolidating means combining data by matching labels and performing a calculation (sum, average, count) across sheets. Consolidate is a specific Excel tool; merging is a general task you can do several ways.