Every Excel export you receive is broken the same five ways
The file lands at 4:58 PM. The analysis is due at 6.
Nothing is wrong with the data. Everything is wrong with the file.
There is a title row merged across the top. A blank row under it. A header split over two rows, with "Q1" merged across three columns. A totals row baked into the bottom of the data. A footnote sitting where a data row should be.
pandas.read_excel reads all of it exactly as instructed — which is the problem. Pandas does not detect any of this structure. You must already know the mess before you can describe it with header=, skiprows= and usecols=. So the columns come out named Unnamed: 0 through Unnamed: 7, the totals row sits inside your sums quietly doubling them, and the footnote becomes somebody's region.
Every analyst I know has the same drawer of glue for this. I had it too:
-
skiprows=3for the finance export — until March, when someone inserted a row into the title block and the script silently read the wrong header for a week. -
header=[0, 1]plus a hand-written flatten function for the two-row headers, with anUnnamed:filter because Excel stores a merged cell's value only in its top-left corner. - An openpyxl pre-pass that walks
ws.merged_cells.ranges, unmerges everything and fills the top-left value across, becauseffillafter the fact guesses wrong on horizontal merges. - A row filter that hunts the word "Total" so the totals row stops inflating the numbers.
- A prayer for the footnote rows.
Five fixes. Rewritten per file, per sender, forever. pandas recorded 688 million downloads last month and openpyxl 349 million — this hour of glue is being re-lived at industrial scale, and the feature request for pandas to fill merged areas on read (pandas #63684, opened January 2026) is still sitting open, because it does not exist.
So I stopped rewriting the glue and built the detector.
tidyexcel
import tidy_sheet
df, report = tidy_sheet.read_excel("q3-report.xlsx") # or read_csv(...)
df.head() # one header row, typed columns, no totals row
print(report) # every fix, listed — nothing is changed silently
tidyexcel report for q3-report.xlsx
rows: 9 in -> 4 out (4 dropped)
header: row(s) 3
fixes:
- title rows skipped: 1
- blank rows skipped above the header: 1
- totals row dropped (row 8): 'Total'
- footnote row dropped (row 9): 'Note: Revenue excludes tax.'
What it detects in v0.1.0:
- Title / preamble rows above the header — it finds the real header row instead of being told where it is.
-
Merged header cells and multi-row headers — merged labels filled across their columns, then flattened to single names like
Q1 / Sales. - Totals and subtotal rows inside the data — dropped, and named in the report.
-
Footnote / footer rows —
Note:,Source:, sparse trailing rows below the data. - Merged data cells — the value filled across the merged range.
- Numbers stored as text — converted to numeric, except values with leading zeros (zip codes stay zip codes).
The part I care about most is the report. Every fix is listed with the original row numbers. If a file was already clean, report.fixes is empty and the frame comes through untouched — there is a control-file test in the repo that fails the build if a detector ever misfires on a clean file. Deterministic heuristics over the raw cell grid: no LLM, no model calls, no network.
Try it before you install anything: the live demo runs the same detection in your browser on sample files or your own — nothing is uploaded anywhere.
pip install tidyexcel
Repo: https://github.com/Yalakala-Naveen/tidysheet (MIT, Python ≥ 3.10, pandas + openpyxl).
What it deliberately does not do
- No type inference beyond numbers-stored-as-text. Dates stay as the values openpyxl/pandas give you.
- One sheet per read (the active sheet by default; pass
sheet="Name"or an index). -
.xlsmand.csvare supported; legacy.xlsis not. - The header search covers the first 50 rows and at most a 3-row header block. A file whose "data" is all text with repeated values can fool the multi-row-header rule — and when it does, the report shows the decision it made, so a wrong call is visible, never silent.
If you feed it a file shape it gets wrong, open an issue with the file's structure (not the data) — the detectors only get better on real shapes. The merged-cell fill pandas users have been asking for since January is the first thing it does; tell me what your sender's files do that it misses.
Top comments (0)