The fastest way to merge worksheets in Excel
The quickest method depends on what you're combining. If all your worksheets have the same column structure — the same headers in the same order — you can copy and paste the data rows from each sheet into one master sheet, then remove duplicates if needed. If the worksheets have different structures or you need to match data across them, you'll use formulas or Excel's built-in consolidation tools instead.
For most people, the copy-and-paste approach works because the worksheets usually came from the same source or follow the same template. This guide covers both the manual method for small datasets and the formula-based method for larger ones that update automatically.
Key Takeaways
- Copy all data rows from each worksheet and paste them into a single master sheet when the column headers match exactly.
- Use the Data > Consolidate tool if you need to sum or average values across worksheets with identical layouts.
- Use INDIRECT formulas with sheet references if you want the merged sheet to update automatically when source sheets change.
- Delete the original worksheets only after you've verified the merged sheet contains all the data you need.
Copying and pasting data from multiple worksheets
Start by creating a new worksheet that will hold all your combined data. Right-click the sheet tab at the bottom of Excel, select Insert Sheet, and name it something clear like "Master" or "Combined Data". This keeps your original worksheets intact while you work.
Next, go to the first worksheet you want to merge. Select the header row (the row with column names) and copy it. Paste it into the first row of your new master sheet. Do this only once — you need the headers only in the master sheet, not repeated from each source sheet.
Now go back to the first source worksheet. Select all the data rows below the header (not the header itself), copy them, and paste them into the master sheet starting at row 2. Then move to the second worksheet, select its data rows, and paste them below the rows you just added. Repeat this for each worksheet. The data will stack vertically, with each worksheet's rows appearing one after another.
After pasting all the data, check for duplicates if the same records appear in multiple source sheets. Click any cell in your data, then go to the Data tab and select Remove Duplicates. Excel will show you which columns to check — usually all of them — and will delete any rows that match exactly.
Using Excel's Consolidate tool for calculations across worksheets
If you need to sum, average, or count values that are spread across multiple worksheets with the same layout, the Consolidate tool is faster than copying and pasting. This tool pulls data from each worksheet and combines it using a calculation you choose.
Create a new master worksheet first. Then go to the Data tab and click Consolidate (you may need to look under Data Tools depending on your Excel version). A dialog box will open asking you to specify the data location and the function you want to use.
In the "Function" dropdown, select what you want to do — Sum is most common, but you can also choose Average, Count, Max, or Min. Then click in the "Reference" field and switch to your first source worksheet. Select the entire data range including headers (for example, A1:D50), then click the button next to the Reference field to return to the Consolidate dialog. Click Add to add that range to the list.
Repeat this for each worksheet you want to consolidate. When you've added all of them, make sure the boxes for Use labels in first row and Use labels in first column are checked if your data has headers. Click OK, and Excel will create a summary in your master sheet showing the combined results.
Using formulas to merge worksheets that change
If your source worksheets update regularly and you want the merged sheet to reflect those changes automatically, use formulas instead of copying data. This takes more setup but saves time later because you won't have to manually re-merge the data each time something changes.
In your master worksheet, set up the same column headers as your source sheets. Then, in the first data row, create a formula that pulls data from the first source sheet. For example, if your first source sheet is named "January" and you want the value from cell A2, type =January!A2. The exclamation mark tells Excel to look in a different sheet, and the cell reference tells it which cell to pull from.
Copy this formula across the row for each column, then copy the entire row down as many rows as you have data. Excel will automatically adjust the cell references (A2 becomes A3, A4, and so on). When you're done with the first sheet's data, start a new section below it and repeat the process for the second sheet, using formulas like =February!A2.
This method requires you to know in advance how many rows of data each sheet contains, so it works best when your worksheets have a consistent structure. If the number of rows changes, you'll need to adjust your formulas or add more rows to the master sheet.
Handling worksheets with different column layouts
If your worksheets don't have the same columns in the same order, you can't straightforward stack the data. Instead, you'll need to rearrange the columns first so they match, or use a more complex approach like creating a lookup table.
The simplest fix is to go back to each source worksheet and rearrange its columns to match a standard order. This takes time upfront but makes the merge straightforward. Copy the headers from one worksheet, paste them into a blank area of each other worksheet, and then rearrange the data columns to match that order. Once all worksheets have the same structure, use the copy-and-paste method described earlier.
If you can't rearrange the source worksheets, you can create a master sheet with all possible columns and use formulas to pull the right data from each sheet. This is more complex and usually only necessary if the source worksheets are maintained by other people and you can't ask them to standardize the layout.
Cleaning up after the merge
Once your master sheet contains all the data and you've verified it's correct, you can delete the original worksheets if you no longer need them. Right-click each source sheet tab and select Delete Sheet. Excel will ask you to confirm — click Delete again.
Before you delete, make sure your master sheet is truly complete. Spot-check a few rows from each original worksheet to confirm they appear in the merged data. If you used formulas instead of copying data, test that the formulas still work after you delete the source sheets — they should, but it's worth confirming.
Save your file after deleting the old sheets. Use File > Save or press Ctrl+S (Windows) or Command+S (Mac). If you're nervous about losing the original data, save a copy of the file with the original worksheets intact before you delete anything.
Frequently Asked Questions
What if the worksheets have different numbers of columns?
Rearrange the columns in each worksheet so they match before merging. If that's not possible, create a master sheet with all the columns from all worksheets, then use formulas to pull data from the right column in each source sheet. This requires more work but handles mismatched layouts.
Can I merge worksheets from different Excel files?
Yes. Open all the files you need, then use the copy-and-paste method or the Consolidate tool. When you use Consolidate and need to reference a different file, click the folder icon next to the Reference field, navigate to the other file, and select the range you want. Excel will create a link to that file.
Will the merged data update if I change the source worksheets?
Only if you used formulas. Copy-and-paste creates a static copy that doesn't change when the source updates. If you used the Consolidate tool, you'll need to run it again to refresh the merged data. Formulas update automatically whenever the source worksheet changes.
How do I handle duplicate rows when merging?
After pasting all data into the master sheet, select any cell in your data range, go to the Data tab, and click Remove Duplicates. Choose which columns to check for duplicates — usually all of them — and Excel will delete any rows that match exactly on those columns.
What's the difference between copy-paste and Consolidate?
Copy-paste combines raw data and is best when you just need all the records in one place. Consolidate calculates across worksheets (sum, average, count) and is best when you need a summary. Use copy-paste for detailed data and Consolidate for summary reports.