How to Merge Multiple Excel Spreadsheets Into One

The fastest way to combine spreadsheets depends on what they contain

Merging spreadsheets means taking data from two or more separate files and putting it into a single file. The method you use depends on whether your spreadsheets have the same columns in the same order, whether you want to stack them vertically (one below the other) or side by side, and how many files you are combining. If all your spreadsheets are identical in structure — same column headers, same data types — the simplest approach is to copy and paste the data rows into one master file. If the spreadsheets have different structures or you need to match data across files, you will need to use a formula or a built-in Excel tool.

The three main routes are manual copying (fastest for two or three small files), the Consolidate tool (best when files have identical layouts), and formulas like VLOOKUP or INDEX/MATCH (necessary when you need to match data across files). This guide walks you through each method so you can pick the one that fits your situation.

Key Takeaways

Copy and paste works fastest when you have two or three spreadsheets with identical column headers and you want to stack them vertically.
The Consolidate tool (Data menu) automatically combines multiple files with the same structure without requiring formulas.
VLOOKUP and INDEX/MATCH formulas let you pull data from one spreadsheet into another based on a matching value, like an ID number.
Before merging, check that column headers match exactly and that data types are consistent — mismatched headers will create duplicate columns.
Save your original files before merging so you have a backup if something goes wrong.

Copying and pasting when spreadsheets have identical columns

If you have two or three spreadsheets with the same column headers in the same order, copying and pasting is the fastest method. Open your first spreadsheet — this will be your master file where all data ends up. Then open your second spreadsheet in a separate window.

In the second spreadsheet, select all the data rows (not the header row, since your master file already has headers). Click on the row number of the first data row, then hold Shift and click on the last row number to select all rows at once. Right-click and choose Copy. Go back to your master file, click on the first empty row below your existing data, right-click, and choose Paste. Repeat this process for each additional spreadsheet. When you are done, save your master file with a new name so you keep the originals intact.

This method works only when all spreadsheets have identical columns. If one file has a column the others do not, or if the columns are in a different order, copying will paste data into the wrong columns. Check your headers before you start.

Using the Consolidate tool for files with matching structures

Excel's Consolidate tool combines data from multiple files automatically, without formulas. This works best when all your spreadsheets have the same layout — same headers, same row labels, same data in the same positions. Open a new blank spreadsheet or a master file where you want the combined data to appear.

Click on the Data menu at the top. Look for Consolidate (in some versions it may be under Data Tools). A dialog box will open. In the Function dropdown, choose the operation you want — Sum is the default, but you can also choose Average, Count, Max, or Min depending on what you need. Then click the button next to "Reference" and navigate to the first spreadsheet file. Select the entire data range including headers, then click the button again to return to the dialog. Click Add. Repeat this process for each additional file. When all files are listed, check the box for "Use labels in first row" and "Use labels in first column" if your data has row labels. Click OK.

The Consolidate tool will combine all the data according to the function you chose. If you chose Sum, it will add up all matching cells across files. This is useful for combining sales data, budget numbers, or any data where you want totals across multiple sources. If your files do not have identical structures, this tool will not work correctly.

Using VLOOKUP to pull data from one spreadsheet into another

VLOOKUP is a formula that searches for a value in one spreadsheet and returns a matching value from another column in the same spreadsheet or a different file. This is useful when you have two spreadsheets with a common column — like employee ID or product code — and you want to combine information from both.

Open your master spreadsheet. In the column where you want the pulled data to appear, click on the first empty cell. Type the formula: =VLOOKUP(A2,[Other File.xlsx]Sheet1!A:D,3,FALSE). Replace A2 with the cell containing the value you want to search for, replace [Other File.xlsx]Sheet1!A:D with the range in the other file where the data lives, and replace 3 with the column number you want to return (1 is the first column, 2 is the second, and so on). FALSE means you want an exact match. Press Enter.

If the formula returns an error, check that the file path is correct and that the value you are searching for actually exists in the other spreadsheet. VLOOKUP searches only in the first column of the range you specify, so if your matching value is not in the leftmost column, you will need to rearrange your data or use INDEX/MATCH instead. Copy the formula down to all rows that need it by clicking the cell and dragging the small square at the bottom right corner down to the last row.

Using INDEX and MATCH for more complex lookups

INDEX/MATCH is more flexible than VLOOKUP because it can search in any column, not just the first one. The formula is longer but works the same way: it finds a matching value and returns data from a column you specify. Open your master spreadsheet and click on the cell where you want the pulled data.

Type the formula: =INDEX([Other File.xlsx]Sheet1!A:D,MATCH(A2,[Other File.xlsx]Sheet1!A:A,0),3). Replace A2 with the cell containing your search value, replace the first range (A:D) with the full data range in the other file, replace the second range (A:A) with the column you want to search in, and replace 3 with the column number you want to return. The 0 means exact match. Press Enter and copy the formula down to all rows that need it.

INDEX/MATCH is slower than VLOOKUP on very large files, but it is more reliable when your data structure changes or when you need to search in columns other than the first one. Both formulas require that the files remain open or saved in the same folder for the links to work.

Checking for duplicate rows and cleaning up after merging

After you merge spreadsheets, check for duplicate rows — data that appears more than once. This happens when the same record exists in multiple source files or when you accidentally paste the same data twice. Click on any cell in your data, then go to the Data menu and look for Remove Duplicates. Select the columns that should be unique (usually all of them), and Excel will delete rows where those columns match exactly.

Also check that all columns are the same width and that no data is cut off. Click on a column header and drag the right edge to widen it if needed. Sort your data by a key column — like date or ID — to make sure everything is in the order you expect. If you used formulas to pull data, convert them to values before sharing the file. Select all cells with formulas, copy them, right-click, choose Paste Special, and select Values Only. This breaks the link to the original files and makes the spreadsheet smaller and faster.

Keeping your original files safe while merging

Always save your original spreadsheets before you start merging. Create a new file for the merged data instead of overwriting an original. If something goes wrong — a formula breaks, data pastes into the wrong columns, or you realize you need to redo the merge — you still have your original files to start over with.

Use a naming convention that makes it clear which file is the merged version. For example, if you are combining January, February, and March sales data, name the merged file "Sales_Jan-Mar_2024_Combined" instead of just "Sales". Keep the original files in a separate folder or mark them clearly so you do not accidentally edit them thinking they are the merged version. If you are merging files regularly — like monthly reports — consider setting up a template or a master file that you reuse each time, rather than creating a new merge from scratch each month.

Frequently Asked Questions

What if my spreadsheets have different column headers?

You will need to standardize the headers before merging. Open each file and rename the columns to match exactly — including capitalization and spacing. If one file has a column the others do not, decide whether to add that column to all files (with blank cells where data does not exist) or delete it from the file that has it. Mismatched headers will create duplicate columns in your merged file.

Can I merge spreadsheets from different Excel versions or file formats?

Yes. Excel can open and merge .xls, .xlsx, .csv, and other common formats. However, some formatting — like colors, fonts, or complex formulas — may not transfer correctly when you copy between different formats. Save your final merged file in the format you plan to use (.xlsx is the standard for modern Excel).

How do I merge spreadsheets if they have different numbers of rows?

Copy and paste works fine with different row counts — just paste each file's data below the previous one. The Consolidate tool also handles different row counts as long as the column structure is identical. If you are using VLOOKUP or INDEX/MATCH, the number of rows does not matter because the formula searches for matches regardless of how many rows exist.

What if I need to merge more than 10 spreadsheets?

For large numbers of files, copy and paste becomes tedious. Use the Consolidate tool if all files have identical structures. If they do not, consider using a formula approach or writing a straightforward macro (a recorded set of steps that Excel repeats automatically). For very large merges, some people use a database program like Microsoft Access, which is designed to combine data from many sources more efficiently than Excel.

Will merging spreadsheets change the original data?

No, as long as you save your merged data to a new file. Copying, pasting, and using formulas do not alter the original spreadsheets. The Consolidate tool also does not change your source files — it only creates a new combined result. Always save with a new filename to keep your originals safe.

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.