How to Merge Excel Workbooks Without Losing Data
A workbook is a file. A worksheet is a sheet inside it. People use the two words as if they were the same, and that is the first source of trouble when merging: a workbook with three sheets is three tables, not one, and a merge tool has to know which sheet you mean. This guide explains how to merge Excel workbooks — whole files, sheets and all — without losing data, and what to check when the result comes back.
The risk is real but manageable. Data is lost in merges for a small number of reasons, and all of them are visible before you merge if you know where to look: a sheet you did not know existed, a header spelled two ways, a column stored as text, a row that appears in two files with different values. Every one of those has a fix, and the audit report tells you when it happened. Losing data in a merge is almost always a failure to check, not a failure of the tool.
Workbook, worksheet, table: what you are actually merging
An Excel workbook (.xlsx) is a file that can hold many worksheets. Each worksheet is a grid of rows and columns. Most workbooks people merge have one sheet each, but not all of them do — and that matters.
When you merge in Separate sheets mode, every sheet of every file is copied into the new workbook. Nothing is lost, because nothing is combined. A five-sheet workbook contributes five sheets. This is the safest mode, and the right first step when you are not sure what is inside the files.
When you merge in Append rows or Merge by ID mode, only the first sheet of each file is used. That is a design choice, not an accident: those modes build one table, and a table has one header row. If the data you want is on the second sheet of a file, it will not be merged — and the audit report will not warn you, because from the tool's point of view the first sheet simply had fewer rows than you expected. So before you use either mode, open each file and confirm that the data is on the first sheet. If it is not, move it there, or save that sheet as its own file and merge that instead.
A useful habit: before any merge, list the sheets in each file. In Excel, the sheet tabs are at the bottom. If a file has more sheets than you expected, look at each one. Hidden sheets are the classic surprise — a workbook can hide a sheet that still holds data, and a merge that only reads the first sheet will never see it.
The four ways data gets lost
Data loss in a merge is rarely dramatic. Nobody deletes a file. Instead, rows quietly end up in the wrong place, or under the wrong heading, or not at all. Here are the four ways it happens.
1. A header spelled two ways. "Customer ID" in one file and "Cust ID" in another are two columns to a merge tool. The merged table has both columns, each half full. No row is lost, but the data is split, and any total you compute on either column will be wrong. The fix is to make the headers match before you merge. The audit report lists header mismatches per file, so read it — it is the early warning for this exact problem.
2. A column stored as text in one file and as a number in another. A merge tool copies values as they are. If one file stores amounts as numbers and another stores them as text, the merged column will contain both, and a sum in Excel will ignore the text cells. Your total will be too low, and nothing will look wrong. The fix is to convert the text cells to numbers in the source file before you merge. After merging, sum one numeric column and compare it with the sum of the source files — our step-by-step combine guide shows this check in detail.
3. A row that appears twice with different values. Two files list the same customer with different phone numbers. In Append rows mode, both rows are kept, and the customer appears twice — which is correct, but it will double-count in a total unless you deduplicate. In Merge by ID mode, the two rows are folded into one, and both phone numbers are kept, joined with " | ". Neither mode loses the value. What loses the value is a tool that silently overwrites the first value with the second. MergeExcel never does that: conflicting values are always kept, and the audit report counts the cells where it happened.
4. A sheet that was never read. As above: data on the second sheet of a workbook, or on a hidden sheet, is not merged in Append rows or Merge by ID mode. The fix is to check the sheets before you merge, and to use Separate sheets mode when you are unsure — it copies everything.
| Cause of loss | How it shows up | How to prevent it | How to detect it after |
|---|---|---|---|
| Header spelled two ways | Two half-empty columns | Make headers match first | Audit report: header mismatches |
| Numbers stored as text | Totals too low | Convert to numbers first | Sum a column and compare |
| Same row, different values | One value overwrites the other | Use Merge by ID, which keeps both | Audit report: merged-ID count |
| Data on a later or hidden sheet | Rows missing entirely | Check sheets; use Separate sheets | Row count lower than expected |
How to prepare workbooks so nothing is lost
Preparation is the whole job. A merge of clean files is boring, and boring is good. Do these six things before you upload.
Make one header row, at the top. The first row of each sheet should be the header, with a name in every column. Delete blank rows above it. If a file has a title row ("Sales Report — October") above the header, delete it or move it — a title row will be treated as a data row, and it will pollute the merged table.
Give every column the same name in every file. "Customer ID" everywhere, not "Cust ID" in one file and "CustomerID" in another. Spelling, spaces and capitalisation all matter to the tool, even though they do not matter to a human reader.
Make the types match. A column should be numbers in every file, or dates in every file, or text in every file. Mixed types are the quietest source of wrong totals. In Excel, you can check a column's type by selecting it and looking at the status bar: if the sum is blank for cells that look like numbers, those cells are text.
Remove empty rows and empty columns. They do no harm — the tool ignores blank rows — but they make the files harder to read, and they hide real problems. A row that looks blank but contains a space is not blank to a tool, and it will be merged as a row of empty values.
Check for merged cells. A merged cell in a header row can hide a column name. Unmerge header cells before you merge, and give every column its own name.
Save a copy of the originals. Merging never changes your files, but keep a backup anyway. Put the originals in a folder called "source" and the merged result in a folder called "output", and never mix the two. Merging a result file together with its own sources is the easiest way to double every row.
Merging workbooks step by step
Here is the process with MergeExcel. The same steps work with any online merge tool.
Step 1: Upload. Drag the workbooks onto the upload area, or click to choose them. Check the list: file names and sizes are shown so you can confirm the right files arrived. The free plan merges up to 10 files at a time; Starter merges 50, Pro merges 200.
Step 2: Choose the mode. Separate sheets if you want everything copied unchanged. Append rows if the workbooks are the same kind of table. Merge by ID if the same rows appear in several workbooks with different values. If you are not sure, start with Separate sheets — you can inspect the result and then run a second merge in another mode.
Step 3: Set the options. For Append rows, add a "Source File" column so you can trace each row back to its file, and turn on deduplication if you expect duplicate rows. For Merge by ID, type the exact header name of the ID column. Our guide to merging multiple files at once covers batch sizes and what to do when you have more files than the plan allows.
Step 4: Merge and read the report. When the job is ready, read the summary first: rows in, rows out, duplicates removed, rows folded by ID, rows skipped. Then read the audit notes. A header mismatch note is not an error — it is information. Decide whether the mismatch is expected (a column that genuinely exists in only some files) or a spelling mistake you need to fix and re-merge.
Step 5: Download and verify. Download the merged workbook and open it. Count the rows. Spot-check rows you know. Sum one numeric column and compare it with the source files. Filter for blanks in a column that should never be blank. These checks take five minutes and they catch almost everything.
Why the audit report matters more than the merge
A merge tool that gives you a file and no report is asking you to trust it. A merge tool that gives you a file and a report is giving you evidence. The difference matters when the numbers are used for something real — a budget, a report to a manager, a filing.
The audit report in MergeExcel lists, for every file, the header mismatches, the empty sheets, the repeated header rows that were skipped, the duplicates that were removed, and the cells where one ID held more than one value. On Starter and Pro plans you can download it as a CSV and keep it with the merged file. That CSV is the record of the merge: what went in, what came out, and what was left out, row by row.
This is not an unusual standard. Public data publishers keep the same kind of record. The U.S. Census Bureau documents the changes it makes between data releases, and the Library of Congress preserves the provenance of its digital collections, because a dataset is only as trustworthy as the record of how it was made. Your merged workbook deserves the same treatment, even if the only reader is you in three months' time.
Handling special cases
Workbooks with formulas. Formulas are read as their stored values. A cell that says "=SUM(A1:A5)" arrives in the merged workbook as the number it computed to, not as the formula. That is usually what you want for a report, but it means the merged file will not recalculate if you change a value. If you need live formulas, add them to the merged file afterwards.
Workbooks with charts or pivot tables. Charts and pivot tables are not carried over. The merged workbook is a clean data table. Build charts and pivot tables from the merged table — they will be correct, because they are built from the final data rather than from a chart that belonged to one file.
Very large workbooks. A file with 200,000 rows merges fine on the server, but it takes a while — the tool reports the time taken, and our own tests put a 200,000-row merge at a few seconds. If a file is over the plan's size limit (20 MB free, 100 MB Starter, 200 MB Pro), split it into smaller files first, or upgrade. Do not try to merge a file that Excel itself struggles to open; fix the file first.
Password-protected workbooks. A protected workbook cannot be read by the tool, and the merge will fail with an unreadable-file error. Remove the password from a copy of the file and merge the copy.
Files with the same name. Two files called "report.xlsx" are stored under different internal names, so the merge is still correct, and the report shows the original names. But rename them anyway — "report-north.xlsx" and "report-south.xlsx" — because your own records will be clearer, and the "Source File" column will be useful instead of confusing.
Frequently asked questions
Can I merge workbooks without losing any data? Yes, if the files are prepared: matching headers, matching types, data on the first sheet, no hidden sheets. Use Separate sheets mode for a zero-change merge, and read the audit report after every merge. A failed merge never counts against your allowance, so a bad first try costs nothing.
What happens to the second sheet of a workbook? In Separate sheets mode it is copied into the merged workbook. In Append rows and Merge by ID modes only the first sheet is used. If your data is on the second sheet, move it to the first or save it as its own file. See our guide on merging sheets from different files for the details.
Why does my merged file have blank columns? Usually because a header was spelled differently in one file, so the tool created two columns: one for each spelling. Fix the headers so they match, and merge again. The audit report lists the mismatch, file by file.
Can I merge .xls files from old Excel versions? Yes. The tool reads .xls as well as .xlsx and .csv, and the result is always a modern .xlsx workbook. Very old files sometimes have quirks — a date stored as text, or a column with mixed types — so check the result with the five-minute checklist in this guide.
How do I merge workbooks without Power Query? Upload them to an online tool, pick a mode, and download the result. No Excel install and no query to build. Our guide on merging without Power Query compares the two approaches and says when Power Query is still the better choice.
Is there a limit on how many workbooks I can merge? The free plan merges up to 10 files per merge, 10 merges a month. Starter merges 50 files per merge, Pro merges 200. If you have more files than that, merge in batches and then merge the batch results. The pricing page has the full comparison.
References
- U.S. Census Bureau, data documentation and change notes: https://www.census.gov/
- Library of Congress, digital collections and provenance: https://www.loc.gov/
- U.S. National Archives, guidance on managing electronic records: https://www.archives.gov/records-mgmt
- Microsoft Support, Excel help and how-to articles: https://support.microsoft.com/