How to Merge Excel Spreadsheets Into One File

The fastest way to combine spreadsheets depends on what's in them

If your data is in separate Excel files or separate sheets within the same file, the method you choose depends on whether the sheets have the same columns, whether you need to keep the original files, and how much data you're working with. The simplest approach — copying and pasting — works for small datasets where the columns match. For larger files or data that needs to stay linked to the source, you'll use formulas or built-in merge tools instead.

Excel doesn't have a single "merge spreadsheets" button, but the task usually takes minutes once you know which route fits your situation. This guide covers the methods you'll actually use, in order from simplest to most complex.

Key Takeaways

Copy and paste works fastest for small datasets where both spreadsheets have identical column headers in the same order.
If you're combining sheets within the same file, you can select multiple sheets and copy them all at once, then paste into a new sheet.
For data that changes in the source files, use formulas like INDIRECT or Power Query to pull data automatically instead of pasting static copies.
When combining files from different people or systems, check that column names match exactly before you start — misaligned columns are the most common mistake.
Save your merged file with a new name so you don't overwrite the originals by accident.

Merging sheets within the same Excel file

If all your data is already in one Excel file but spread across multiple sheets, the fastest method is to create a new sheet and copy the data in. Start by creating a blank sheet — right-click the sheet tab at the bottom, select "Insert Sheet", and give it a name like "Combined" or "Master".

Go to the first sheet you want to combine. Select all the data including headers (click the top-left corner to select the entire sheet, or use Ctrl+A). Copy it with Ctrl+C. Switch to your new blank sheet, click cell A1, and paste with Ctrl+V. Now go to the second sheet, select all its data, copy it, then click the cell in your combined sheet where you want the new data to start — usually the row right after the last row of the first dataset — and paste.

Repeat this for each sheet. If the sheets have different column orders, you'll need to rearrange columns in your combined sheet before pasting the next one, or manually move the data after pasting. This is why checking column names first saves time.

Combining data from separate Excel files

When your data is in different files, you have two choices: move everything into one file, or keep the files separate and pull the data together. The first option is simpler if the files won't change. Open the first file, create a new sheet called "Combined", and copy all the data from the first file into it. Then open the second file, copy all its data, and paste it into the combined sheet below the first dataset.

If you're combining more than two files, open them all at once so you can switch between them without closing and reopening. In Windows, use Alt+Tab to move between open Excel windows. On a Mac, use Command+Tab or click the Excel icon in the dock to see all open files.

After pasting all the data, check that the columns line up correctly. If one file has columns in a different order, you'll see misaligned data. Rearrange the columns in the source data before pasting, or move them in the combined sheet after pasting.

Using formulas to pull data from multiple files automatically

If the data in your source files changes regularly and you need the combined sheet to update automatically, use formulas instead of pasting static data. The INDIRECT function lets you reference cells in another sheet or file by building the cell address as text.

In your combined sheet, type a formula like =INDIRECT("'[SourceFile.xlsx]Sheet1'!A1") to pull the value from cell A1 in Sheet1 of SourceFile.xlsx. The file path goes in square brackets, the sheet name in single quotes, and the cell address after the exclamation mark. Copy this formula down and across to pull all the data you need. When the source file updates, your combined sheet updates automatically.

This method requires that the source files stay in the same folder and keep the same names. If you move or rename a file, the formulas break. For more complex scenarios — like combining data from files that move around or combining data that needs filtering — use Power Query instead, which is built into Excel 2016 and later.

Using Power Query to merge files from a folder

Power Query is Excel's tool for combining multiple files at once without copying and pasting. It's especially useful when you have many files in one folder that all have the same structure. Open Excel, go to the Data tab, and click "Get Data" (or "New Query" in older versions). Select "From File" and then "From Folder".

Browse to the folder containing your files and click OK. Power Query shows you a preview of all the files in that folder. Click the "Combine" button and select "Combine and Load". Power Query merges all the files into a single table, automatically stacking the data from each file below the previous one.

If the files have different column names or orders, Power Query can handle that — it aligns columns by name, not by position. After combining, you can filter, sort, or clean the data right in Power Query before loading it into a new sheet. This method is much faster than manual copying when you're combining 10 or more files.

Checking your merged data for errors

After merging, scan the combined sheet for common problems. The most frequent issue is duplicate headers — if you pasted data from multiple sheets, you might have column names appearing in the middle of your data. Select the row and delete it. Another common problem is misaligned columns, where data from one source file ended up in the wrong column because the column order was different. Rearrange the columns or re-paste the data in the correct order.

Check that the row count makes sense. If you combined two sheets with 1,000 rows each, you should have roughly 2,000 rows in the combined sheet (plus one for headers). If the count is much lower, you may have accidentally overwritten data instead of pasting below it. Use Ctrl+Z to undo and try again, making sure you click the correct starting cell before pasting.

Finally, verify that numeric columns are actually formatted as numbers, not text. If a column looks like numbers but won't calculate in a SUM formula, right-click the column, select "Format Cells", and change the format to "Number". This often happens when data comes from different sources or was typed in different ways.

Keeping your original files safe while merging

Before you start combining data, save your original files with a backup name or in a separate folder. The safest approach is to create a new file for the merged data instead of pasting into one of the originals. Open a blank Excel file, save it with a name like "Combined_Data_2024", and do all your merging there. This way, if something goes wrong, your source files are untouched.

If you're combining files from other people, ask them to send you copies rather than working with the originals. This protects both you and them from accidental changes. After you've verified that the merged data looks correct, you can delete the backup copies of the source files if you need the space.

Frequently Asked Questions

What if the two spreadsheets have different column names?

You'll need to rename the columns so they match before merging, or manually rearrange the data after pasting. The safest approach is to edit the source files first — open each one and change the column headers to match exactly, including capitalization and spacing. Then copy and paste as normal.

Can I merge spreadsheets without losing formatting like colors or bold text?

Yes. When you copy and paste, Excel preserves formatting by default. If you want only the data without formatting, use Paste Special (Ctrl+Shift+V) and select "Values Only". This removes colors, bold, and other formatting but keeps the data intact.

How do I combine data from Google Sheets and Excel?

read the Google Sheet as an Excel file (.xlsx), then follow the steps for merging separate Excel files. Open both files in Excel, copy the data from the downloaded Google Sheet, and paste it into your combined file. Alternatively, copy the data from Google Sheets, paste it into Excel, and proceed from there.

What's the difference between merging and consolidating data?

Merging stacks data from multiple sources into one list. Consolidating combines data by summing or averaging values across multiple sheets — for example, adding up sales totals from each region's spreadsheet into one summary. Use the Data tab's "Consolidate" tool if you need to sum or average data instead of just combining it.

Will merging slow down my Excel file if it's very large?

Very large files (over 100,000 rows) can slow down Excel, especially if you're using formulas that recalculate constantly. If you're combining huge datasets, consider using Power Query, which handles large files more efficiently, or splitting the data into multiple sheets instead of one massive sheet.

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.