How to Merge Excel Sheets from Different Files into One Workbook
An Excel workbook can hold many sheets, and the sheet you want is not always the first one. That is the central fact of this guide. Merging sheets from different files means deciding, for each file, which sheet holds the data — and then deciding whether the sheets should stay separate or be stacked into one table. Get those two decisions right and the merge is straightforward. Get them wrong and rows go missing without any error message, which is the worst kind of failure: silent.
This guide explains both decisions, how each merge mode treats sheets, what to do with hidden sheets, and how to check that every sheet you meant to merge actually arrived.
Sheets, workbooks and tables
A workbook is a file. A sheet is a tab inside it. A table is the block of rows and columns on a sheet, usually with a header row at the top. When people say "merge sheets", they usually mean one of two things, and the two need different modes.
Collect the sheets. Each sheet from each file becomes a sheet in the new workbook. The sheets are not combined; they are gathered. This is Separate sheets mode, and it is the right choice when the sheets are different tables — January sales, February sales, a price list, a rota — or when you simply want everything in one file.
Stack the sheets. The data from several sheets is combined into one table. This is Append rows mode, and it is the right choice when the sheets are the same kind of table — the same columns, different rows. The catch is that the tool uses the first sheet of each file. If the data is on the second sheet, it will not be merged. So stacking sheets from different files means, in practice, making sure the data is on the first sheet of each file first, or saving the sheet you want as its own file.
| What you want | Mode | What happens to sheets |
|---|---|---|
| All sheets in one workbook, unchanged | Separate sheets | Every sheet copied, original names kept where possible |
| One table from many similar sheets | Append rows | First sheet of each file, stacked by header |
| One row per ID across sheets | Merge by ID | First sheet of each file, rows folded by ID |
How each mode treats sheets
Separate sheets. Every sheet of every file is copied into the merged workbook. A file with three sheets contributes three sheets. Sheet names are kept where possible; if two sheets have the same name, the second one gets a number so nothing is overwritten. Empty sheets are copied as empty sheets and listed in the audit report. Nothing is changed, nothing is lost. This is the mode to use when you are unsure what is inside the files.
Append rows. Only the first sheet of each file is read. The header row of that sheet becomes the table's header, and the data rows are stacked. The other sheets of the file are ignored. This is the right behaviour for a table merge — a table has one header — but it means you must check which sheet is first in each file before you merge. In Excel, the first sheet is the leftmost tab. If the leftmost tab is a cover page or a summary, the merge will read the cover page as a table, and the result will be wrong.
Merge by ID. The same as Append rows: only the first sheet of each file is read, and rows are folded by the ID column you name. The other sheets are ignored.
The rule to remember is simple: if you care about a sheet, make sure it is the first sheet of its file, or merge in Separate sheets mode. There is no third option, and no error message will tell you that a sheet was skipped — the audit report counts rows, not sheets, so a missing sheet shows up as a row count that is lower than you expected.
How to prepare sheets for merging
List the sheets in each file. Open each workbook and look at the tabs at the bottom. Write down how many sheets there are and what they are called. If a file has more sheets than you expected, open each one. The data you want may be on the second tab, and a summary or a chart may be on the first.
Move the data to the first sheet. If you plan to use Append rows or Merge by ID, and the data is not on the first sheet, move it. In Excel, right-click the sheet tab, choose Move or Copy, and put it first. Or copy the data range onto the first sheet. Do this on a copy of the file, not the original, so the original stays as it was.
Check for hidden sheets. A workbook can hide a sheet: right-click a tab and choose Unhide to see if any are hidden. A hidden sheet still holds data, and a merge in Separate sheets mode will copy it — which is correct — but a merge in Append rows mode will never see it, because it is not the first sheet. If a hidden sheet holds data you need, unhide it and move it to the front before you merge.
Check for very hidden sheets. A sheet can also be "very hidden", which means it does not appear in the Unhide list at all. You can only see it in the Visual Basic editor. If you did not create the workbook, you probably do not need to worry about this — but if a workbook's row count does not match what you expect, a very hidden sheet is one possible cause, and the fix is to open the file in Excel and check the Visual Basic project.
Give each sheet a clear name. "Sheet1" tells you nothing. Rename sheets with what they hold: "Sales-Jan", "Sales-Feb", "Prices". Clear names show up in the audit report and in the merged workbook, and they make it obvious when a sheet is missing. They also help when two files have sheets with the same name — the tool will number the duplicates, but a clear name makes the numbering meaningful.
Delete sheets you do not need. A workbook accumulates scratch sheets, old versions and test data. Delete them from a copy of the file before you merge, or they will be copied into the merged workbook in Separate sheets mode, and they will confuse anyone who opens the result.
Merging sheets step by step
Step 1: Decide the mode. Separate sheets if the sheets are different tables or if you are unsure. Append rows if they are the same kind of table. Merge by ID if the same rows appear on several sheets with different details.
Step 2: Prepare the files. Move the data to the first sheet for Append rows and Merge by ID. Unhide any sheets you need. Rename the sheets. Save copies, and merge the copies.
Step 3: Upload to MergeExcel. Drag the files onto the upload area. Check the file list. The free plan merges up to 10 files at a time; for more files, see our guide to merging multiple files at once.
Step 4: Pick the mode and options. For Append rows, turn on the "Source File" column so you can trace each row to its file. For Merge by ID, type the exact header name of the ID column. For Separate sheets, there is nothing to set.
Step 5: Merge and read the report. Check the row counts and the audit notes. In Separate sheets mode, check the sheet count: the merged workbook should have one sheet per input sheet. In Append rows mode, check that the row count matches the sum of the first sheets' rows, minus duplicates and skips.
Step 6: Download and verify. Open the merged workbook. In Separate sheets mode, click through every sheet and confirm the data is there. In Append rows mode, run the five-minute check from our step-by-step combine guide: row count, known rows, edges, one sum, and a filter for blanks.
Common sheet problems
The data is on the second sheet. The most common problem. The merge reads the first sheet, which is a cover page or a summary, and the result is a table of the wrong data — or a table with almost no rows. Fix: move the data sheet to the front, or save it as its own file.
Two sheets with the same name. In Separate sheets mode, the second sheet gets a number — "Sales (2)" — so nothing is overwritten. That is correct, but it is confusing if you expected one sheet called "Sales". Rename the sheets before you merge so each one is distinct.
A sheet with a different header. In Append rows mode, a sheet whose headers differ from the others produces a mismatch, which the audit report lists. That is expected when the sheets are genuinely different. It is a problem when the difference is a spelling mistake. Fix the headers and merge again.
A sheet that is actually a chart. Some sheets hold a chart and no table. In Separate sheets mode, the chart sheet is copied as an empty sheet, because there are no cell values to copy. That is honest — there was no data — but it can be surprising. If you expected data, check that you picked the right sheet.
A sheet with merged cells in the header. A merged header cell hides a column name. Unmerge the header row before you merge, and give every column its own name. Otherwise the merged table will have a column called "Column 4" with no meaning.
A sheet with a title row above the header. "Sales Report — October" sitting above the header row will be treated as a data row. Delete title rows before you merge, or the merged table will contain a row of text where numbers should be.
| Problem | Symptom | Fix |
|---|---|---|
| Data on the second sheet | Row count too low, or wrong data | Move the data sheet to the front |
| Hidden sheet with data | Rows missing, no warning | Unhide the sheet, then merge |
| Same sheet name twice | "Sales (2)" in the result | Rename sheets before merging |
| Title row above the header | A text row in the data | Delete the title row |
| Merged header cells | A column called "Column 4" | Unmerge headers, name every column |
| Chart-only sheet | An empty sheet in the result | Expected — there was no data to copy |
Sheets from Google Sheets and CSV files
A Google Sheets file has tabs, just like Excel. When you export it, each tab becomes a sheet in the .xlsx file — or, if you export as CSV, only the current tab is exported. So if you need several tabs from a Google Sheets file, export as .xlsx, not .csv. Then merge the exported file like any other workbook.
A CSV file has no sheets. It is one table. In Separate sheets mode, a CSV file becomes one sheet. In Append rows mode, it is stacked like any other first sheet. Mixing CSV files and Excel workbooks in one merge is fine — the result is always a .xlsx workbook. See our guide on merging CSV files for the CSV-specific quirks.
Why sheet order matters for the result
In Separate sheets mode, the sheets in the merged workbook appear in the order the files were uploaded, and within each file in the order the sheets appear in that file. Upload the files in the order you want the sheets to appear — January before February, North before South. The tool does not sort sheets; it preserves the order you gave it.
In Append rows mode, order matters differently: the header union is built in first-seen order, so the columns of the merged table appear in the order they first show up across the files. The first file's columns come first. If you want a particular column order in the result, make the first file the one with the columns in the order you want. You can also reorder the columns in the merged workbook afterwards — it is a normal Excel file, and reordering columns there is safe, because the data is already combined.
Frequently asked questions
How do I merge sheets from different Excel files? Upload the files to an online merge tool and pick Separate sheets to collect them into one workbook, or Append rows to stack them into one table. If the data is not on the first sheet of a file, move it to the front first, because Append rows and Merge by ID only read the first sheet.
Can I merge only one sheet from each workbook? Yes. Make the sheet you want the first sheet of its file, then use Append rows or Merge by ID. Or save that sheet as its own file (right-click the tab, Move or Copy, to a new workbook) and merge the saved files.
What happens to hidden sheets when I merge? In Separate sheets mode they are copied, because every sheet is copied. In Append rows and Merge by ID modes they are ignored unless they are the first sheet. Unhide any sheet you need before you merge.
Why is my merged workbook missing a sheet? Usually because the file had more sheets than you realised and you used Append rows, which only reads the first sheet. Check the sheet tabs in the source file, and use Separate sheets mode if you want every sheet copied.
Can I merge sheets that have different columns? Yes, with Append rows. The merged table uses the union of all headers, and rows from sheets that lack a column leave it blank. The audit report lists the missing columns per file. See our guide on combining Excel files for the details.
How do I merge Excel workbooks without losing data? Prepare the files first: matching headers, data on the first sheet, no hidden sheets with data you need. Then merge, read the audit report, and verify the result with the five-minute check. Our guide on merging workbooks without losing data covers the full preparation checklist.
References
- U.S. Census Bureau, data documentation and table formats: https://www.census.gov/
- National Center for Education Statistics, data files and documentation: https://nces.ed.gov/
- U.S. Bureau of Labor Statistics, published datasets: https://www.bls.gov/
- Microsoft Support, Excel help and how-to articles: https://support.microsoft.com/