How to Combine Data from Multiple Excel Sheets Into One

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 and paste all the data into one sheet in a few minutes. If the columns are in different orders or some sheets are missing columns, you'll need to use a formula or manually rearrange first. Excel doesn't have a single "merge sheets" button — the method you choose depends on whether you're combining data once or setting up something that updates automatically when the source sheets change.

The simplest approach for a one-time merge is to open each sheet, select all the data below the header row, copy it, and paste it into your main sheet starting below the existing data. If you have many sheets or the data changes often, a formula approach using INDIRECT or INDEX/MATCH will pull data from multiple sheets without manual copying.

Key Takeaways

Copy and paste works fastest for a one-time merge when all sheets have identical column headers in the same order.
If sheets have different column orders or missing columns, sort them to match before pasting, or use a formula to pull specific columns.
INDIRECT formulas let you reference data from multiple sheets at once, and the merged data updates automatically if the source sheets change.
Always keep the original sheets intact until you've verified the merged data is complete and correct.

Copying and pasting data from multiple sheets

Start by opening the sheet where you want the combined data to live. This is your destination sheet. Leave the header row in place — you'll paste new data below it.

Open the first source sheet. Select all the data except the header row: click the first cell below the header, then press Ctrl+Shift+End (on Windows) or Cmd+Shift+End (on Mac) to select to the last cell with data. Copy this selection with Ctrl+C or Cmd+C.

Go back to your destination sheet and click the first empty cell below your existing data. Paste with Ctrl+V or Cmd+V. Repeat this process for each additional sheet. When you're done, scroll through the combined data to make sure there are no gaps or duplicates.

Handling sheets with different column orders

If your sheets don't have columns in the same order, pasting directly will put data in the wrong columns. Before you paste, check whether the column headers match across all sheets — even if they're in a different sequence.

The safest approach is to rearrange the columns in each source sheet to match your destination sheet before pasting. Click and drag the column headers to reorder them. Once all sheets have the same column order, use the copy-and-paste method described above.

Alternatively, if you have only a few columns to move, you can paste the data into your destination sheet and then use cut and paste to move individual columns into the right position. This takes longer but avoids changing the original sheets.

Using formulas to pull data from multiple sheets automatically

If you need the merged data to update whenever the source sheets change, formulas are more reliable than manual copying. The INDIRECT function lets you reference cells in other sheets by name.

Create a formula like =INDIRECT("Sheet1!A1") to pull a single cell from another sheet. To pull an entire column of data, use =IFERROR(INDIRECT("Sheet1!A"&ROW()),""). This formula looks at the current row number and pulls the corresponding cell from Sheet1, column A. If the cell is empty, it shows nothing instead of an error. Copy this formula down as far as you need.

For more control, use INDEX and MATCH together. =INDEX(Sheet1!A:A,MATCH(lookup_value,Sheet1!B:B,0)) finds a value in one column and returns the corresponding value from another column. This works when you need to match data across sheets before combining it.

Combining sheets with different numbers of columns

When sheets have different columns — for example, one sheet has Name, Email, and Phone, while another has Name, Email, and Address — you need to decide what to do with the missing columns.

One option is to add the missing columns to each sheet before merging, leaving them blank where data doesn't exist. This keeps your final sheet consistent. Another option is to paste each sheet's data into separate areas of your destination sheet, side by side, if you don't need a single unified table.

If you're using formulas, you can pull only the columns you want from each sheet. Create separate formula columns for each source sheet, then delete the ones you don't need once you've verified the data is correct.

Checking for duplicates and gaps after merging

After you've combined all the data, scroll through the merged sheet to spot obvious problems: missing rows, data that appears twice, or columns that are blank when they shouldn't be.

To find exact duplicates, select all your data and use the Data menu's Remove Duplicates feature (on Windows) or Data > Filter > Standard Filter (on Mac). This will flag or remove rows where every column matches another row exactly. If you only want to find duplicates in one column — for example, duplicate email addresses — select just that column first.

Count the rows in your merged sheet and compare them to the total rows from all source sheets. If the numbers don't match, check whether any sheets had blank rows in the middle of the data, which can cause copy-paste to stop early.

Keeping your original sheets safe while merging

Before you start copying data, save a backup of your file. Use File > Save As and give it a new name with today's date, like "Spreadsheet_Backup_Jan15.xlsx". This way, if something goes wrong during the merge, you still have the original data.

Don't delete the source sheets until you've finished merging and verified that all the data made it into the destination sheet correctly. Once you're confident the merge is complete, you can hide the source sheets (right-click the sheet tab and select Hide) or delete them if you no longer need them.

Frequently Asked Questions

What if one of my sheets has a different number of rows than the others?

That's fine — sheets don't need to have the same number of rows to merge. Just make sure the columns are in the same order, then copy and paste each sheet's data below the previous one. The merged sheet will have as many rows as all the source sheets combined.

Can I merge sheets from different Excel files?

Yes. Open both files, then copy data from the sheet in one file and paste it into the sheet in the other file. You can also use INDIRECT formulas that reference another file by including the file path in the formula, like =INDIRECT("[OtherFile.xlsx]Sheet1!A1"), though this only works if both files are open.

How do I merge sheets without losing the original data?

Create a new sheet in your workbook to hold the merged data, then copy from your source sheets into this new sheet. This way, the original sheets stay unchanged. You can hide them or keep them visible for reference.

What's the difference between merging sheets and consolidating data?

Merging means combining all rows from multiple sheets into one sheet. Consolidating usually means summarizing data — for example, adding up sales totals from each sheet. Use copy-and-paste for merging; use the Data > Consolidate feature for summarizing.

Can I set up a merge that updates automatically?

Yes, if you use formulas instead of copy-and-paste. INDIRECT or INDEX/MATCH formulas will pull fresh data from the source sheets every time you open the file or press F9 to recalculate. Manual copy-and-paste won't update unless you repeat the process.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.