The fastest way to combine workbooks depends on what you're merging

Merging two Excel workbooks means bringing data from one file into another. The method you use depends on whether you want to copy entire sheets, link the files so they update together, or combine data from the same columns in different files. If you're just moving one sheet from File B into File A, you can drag it. If you're combining sales data from two regions where the columns match, you can paste and sort. If you need File A to always show the latest numbers from File B without manual copying, you'll use a formula link instead.

The three most common routes are: copying sheets by dragging them between open files, pasting data and organizing it, or creating formulas that pull data from one workbook into another. Each takes a different amount of time and works best for different situations.

Key Takeaways

  • Dragging a sheet tab between two open Excel windows copies the entire sheet in seconds, and works when you want to move a complete sheet from one file to another.
  • Copying and pasting data works best when both files have the same column structure, and you can sort the combined data afterward to keep it organized.
  • Formulas that reference another workbook let you pull specific data from File B into File A, and the data updates automatically when File B changes.
  • Before you start, save both files and make sure they're not read-only, or Excel will not let you make changes.

Dragging sheets between open workbooks

This is the fastest method when you want to move an entire sheet from one file to another. Open both Excel files on your screen at the same time. You can do this by opening the first file, then opening the second file, then using the Windows taskbar or the View menu to arrange them side by side. Right-click on the sheet tab at the bottom of the source file (the one you're copying from) and select "Move or Copy". A dialog box will appear asking where you want to place the sheet. Choose the destination file and the position within that file, then click OK.

If you want to keep the sheet in both files instead of moving it, check the box that says "Create a copy" before you click OK. This leaves the original sheet in File B and puts a copy in File A. The two sheets are now separate — changes to one will not affect the other.

Copying and pasting data when columns match

When both files have the same structure — for example, both have columns for Date, Product, and Sales Amount — you can select all the data in one file, copy it, and paste it into the other. Open the source file and select all the data you want to move. Click the first cell with data, then press Ctrl+Shift+End to select everything from that cell to the last cell with content. Copy it with Ctrl+C.

Switch to the destination file and click the cell where you want the data to start. Paste with Ctrl+V. If the data has headers (column names), make sure the headers in both files match, or the combined data will be confusing. After pasting, you may want to sort the data by date or another column so the combined information is in order. Select all the data including headers, go to the Data menu, and click Sort to arrange it the way you need.

Using formulas to pull data from another workbook

If you want File A to automatically show data from File B without copying and pasting every time File B changes, use a formula that references the other file. This works best when you need specific cells or ranges, not entire sheets. In the destination file, click the cell where you want the data to appear. Type an equals sign to start a formula, then switch to the source file and click the cell you want to reference. Excel will write the formula for you, showing something like =[SourceFile.xlsx]Sheet1!A1. Press Enter, and that cell's value now appears in your destination file.

When you use this method, the data updates automatically whenever the source file changes. If you close the source file and reopen the destination file, Excel will ask whether you want to update the links. Click "Update" to see the latest numbers. This method is useful for dashboards or summary sheets that pull data from multiple department files, but it requires both files to stay in the same folder or the links will break.

Combining data from multiple sheets in one file

Sometimes you have one workbook with multiple sheets that you want to combine into a single sheet for analysis. Open the workbook and create a new blank sheet where the combined data will live. Name it something clear like "Combined Data". Go to the first sheet you want to combine, select all the data including headers, and copy it. Switch to the Combined Data sheet and paste it in the first row.

Go to the second sheet, select all its data (but not the headers if the columns are the same), and copy it. In the Combined Data sheet, click the first empty row below the data you just pasted and paste again. Repeat for each sheet. When all data is in one place, you can sort it, filter it, or create pivot tables to analyze it. This approach works well when you have regional sales sheets or monthly reports that need to be analyzed together.

Handling mismatched columns and data types

If the two files have different column structures, you need to decide which columns to keep and how to organize them before you combine the data. Open both files and look at the column names and what data each one contains. If File A has columns for Date, Product, and Sales, but File B has Date, Product, Region, and Sales, you'll need to add a Region column to File A or decide not to include it.

The safest approach is to create a new sheet in your destination file and manually set up the columns you want to keep. Then copy data from each source file and paste it into the matching columns. This takes longer than a straightforward paste, but it prevents errors where data ends up in the wrong column. If the files have dates in different formats (like "1/15/2024" versus "January 15, 2024"), Excel may not recognize them as the same type of data, so check that dates are formatted the same way before you combine them.

Saving and backing up before you merge

Before you start any merge, save both files with clear names that show what they contain. If you're combining Regional Sales File A and Regional Sales File B, save them as "RegionalSales_North.xlsx" and "RegionalSales_South.xlsx" so you can tell them apart. Make a copy of each file and work with the copies, so you still have the originals if something goes wrong.

After you've merged the data, open both original files and the new combined file to make sure all the data moved correctly and nothing was lost. Check that numbers add up, dates are in order, and no rows were skipped. If you used formulas to link the files, test them by changing a value in the source file and confirming that the destination file updates. Save the merged file with a name that shows it's the final version, like "RegionalSales_Combined_Final.xlsx".

Frequently Asked Questions

Can I merge workbooks if one of them is read-only?

No. If a file is read-only, you cannot paste data into it or create links from it. Right-click the file in Windows Explorer, select Properties, and uncheck "Read-only" if you own the file. If someone else owns it, ask them to send you an editable copy or to remove the read-only protection.

What happens to formulas when I copy a sheet from one workbook to another?

Formulas copy with the sheet, but they may break if they reference cells in the original workbook that don't exist in the new one. After you copy a sheet, check the formulas to make sure they still work. If they reference the old file, you may need to update them to reference the new location or delete them and rebuild them.

Can I merge workbooks if they're stored in OneDrive or SharePoint?

Yes, the same methods work. read both files to your computer, merge them, and upload the result. Alternatively, you can open both files directly from OneDrive and use formulas to link them, which keeps the link active even after you close the files, as long as both files stay in OneDrive.

How do I undo a merge if I made a mistake?

If you just pasted data, press Ctrl+Z when ready to undo. If you've already saved the file, close it without saving and reopen the original. This is why making a copy before you merge is important — you can always go back to the original if the merge didn't work the way you wanted.

What's the difference between moving a sheet and copying it?

Moving removes the sheet from the original file and puts it in the new one. Copying leaves it in both files. Use Move if you want to consolidate everything into one file and don't need the original anymore. Use Copy if you want to keep the original file intact and just need a version in the new location.