Top 10 ways merging and splitting Excel files goes wrong (each one reproduced)

Most advice about merging Excel files is a list of tools. The failures people actually hit are not about the tool, they are about what a merge silently does to formulas, IDs, dates and formatting. So here is a top 10 of those failures. Each one comes from a small test I ran (openpyxl 3.1.5, pandas 3.0.5, LibreOffice for recalculation), and the full script is in the last section.

1. Rewriting through pandas keeps the values and nothing else

The most common "merge" script is pd.read_excel(...) then to_excel(...). I built a sheet with conditional formatting, a data-validation list, a comment, an image, a defined name, a custom column width and frozen panes, then copied it two ways:

openpyxl's load_workbook then save kept all seven. A pandas read-then-write kept none of them, and renamed the sheet to Sheet1. A chart also survived the openpyxl round trip in a separate test (I did not test the pandas path for charts). Things I did not test: pivot tables, slicers, shapes, form controls. Test your own file types before trusting a round trip.

2. A file nobody has opened in Excel has no formula results

I wrote a file with openpyxl containing =B2+C2, then read it:

pd.read_excel('f1.xlsx', sheet_name='Sales')['Total'].tolist()   # [nan, nan]
openpyxl.load_workbook('f1.xlsx', data_only=True)['Sales']['D2'].value  # None

openpyxl stores the formula but no result, so any reader that wants values gets nothing. After I opened and re-saved the file through LibreOffice headless (soffice --headless --convert-to xlsx), the same reads returned [30, 70] and 30. Files saved by Excel carry cached values; files generated by code often do not.

3. data_only=True plus save() deletes your formulas for good

wb = openpyxl.load_workbook('recalculated.xlsx', data_only=True)
wb.save('out.xlsx')
openpyxl.load_workbook('out.xlsx')['Sales']['D2'].value   # 30, not "=B2+C2"

data_only=True is a read mode. If you save that workbook, the formulas are replaced by their cached values. That is exactly what you want for a "values only" export and exactly what you do not want for a working file.

4. Formulas that point at a sheet the new workbook does not have

Merging by copying cells from one workbook into another carries the formula text along:

dst['A1'] = src['Summary']['A1'].value   # '=SUM(Sales!B2:C3)'
dst.sheetnames                           # ['Sheet'] — there is no 'Sales'

The text still names Sales, which no longer exists in the destination. The same thing happens when you split a workbook and one sheet's formulas refer to another. Also, copy_worksheet only works inside one workbook; across workbooks it raises ValueError: Cannot copy between worksheets from different workbooks. Decide per formula: keep the references (and keep the sheets together) or freeze to values.

5. Sheet names have rules, and some are enforced quietly

• Duplicate names: creating a second sheet called Sales quietly gives you Sales1. When you merge ten files that each have Sheet1, you get Sheet11, Sheet12… unless you rename them yourself.
• A / in a name raises ValueError: Invalid character / found in sheet title.
• A 32-character title is accepted by openpyxl, with a warning that "some applications may not be able to read the file". Excel's own limit is 31 characters, so prefix names with the source file name and truncate.

Bonus: openpyxl will save a workbook under an .xlsm file name without the macro parts, and the file's workbook content type stays the plain .xlsx one. I did not open it in Excel; macros need keep_vba=True on load, and I did not test that path.

6. IDs with leading zeros become numbers

A text cell 00123 next to a numeric 456:

pd.read_excel('f5.xlsx')['id'].tolist()              # [123, 456]
pd.read_excel('f5.xlsx', dtype=str)['id'].tolist()   # ['00123', '456']

If the default read happens first and you then write a CSV, the zeros are gone for good (my round trip printed 123). Read ID columns as str.

7. Merged cells only keep one value

A vertical merge A2:A4 holding "Ops" reads like this:

pd.read_excel('f6.xlsx')['Dept'].tolist()          # ['Ops', nan, nan]
pd.read_excel('f6.xlsx')['Dept'].ffill().tolist()  # ['Ops', 'Ops', 'Ops']

Excel stores the value only in the top-left cell. ffill() is the fix for stacking, but it also fills genuine blanks, so apply it only to the merged column.

8. Stacking rows needs normalized headers

pd.concat([a, b]).columns   # ['Region', 'Sales', 'region ']

Region and region (lowercase, trailing space) are two different columns, so you get a third, half-empty one. Strip and lowercase headers before concat, and print the resulting columns before you trust the merge.

9. The sheet has a hard row limit

Writing row 1,048,577 raises ValueError: Row numbers must be between 1 and 1048576. Stacking ten large exports can cross that line, so check the total row count first and split the output into several sheets or files.

10. Two workbooks can use different date systems

The .xlsx format stores dates as serial numbers with a base date that can be 1900 or 1904 (a workbook setting). I wrote 2026-11-06 into a 1904-based workbook. The file holds the serial 44870; openpyxl reads it back correctly as 2026-11-06 because it honors the workbook's epoch. Read the same serial as a 1900-system date and you get 2022-11-05, which is 1,462 days off. Copy raw serials between workbooks that use different systems and every date moves by four years and a day. Compare wb.epoch before merging.

If you would rather not code

For one-off merges, a tool saves the script. The questions to ask a tool are the ten above: does it keep formatting, what does it do with formulas, how does it name sheets, and where does your file go? What I could check from the vendors' own pages in Nov 2026:

Option	Merge style	Formulas	Unsupported content	Where it runs
openpyxl script (above)	whatever you write	you choose (items 2–4)	pivots/shapes/slicers untested here	your machine
ToolTiny Merge & Split Excel	each source sheet becomes a tab (rows are not appended); split gives one XLSX per sheet in a ZIP	choice of "use last saved values" or "keep formulas"; the keep option does not support named ranges, structured table references, INDIRECT, OFFSET or external references	charts, images, shapes, pivots, external links: the file is refused, not silently dropped; XLSM unsupported	its server; up to 10 XLSX files and 20 MB per merge, 100 sheets and 250,000 cells per job; temp files removed after the download or a failure
Excel itself (Move or Copy sheet, Power Query "Combine")	tabs, or stacked rows with Power Query	native	native	your machine

I could not open Microsoft's documentation pages with my tooling, so I make no claims about Excel's own limits beyond what you can try in the app yourself. I did not re-verify other online mergers for this post.

The reproduction script

test_pitfalls.py builds every fixture above and prints the results shown here. It needs openpyxl, pandas and Pillow; item 2's cached-value check uses LibreOffice's soffice.

Limits

• Versions: openpyxl 3.1.5, pandas 3.0.5, Python 3.13. Other versions may behave differently.
• I did not open the outputs in desktop Excel, so claims about what Excel shows stay at the file level (what is stored), not what the Excel UI displays.
• The fixtures are small and synthetic; real workbooks have more ways to break.
• Vendor statements are as read in Nov 2026.

What is the ugliest merge failure you have seen? I would add it as number 11.