Reading & writing workbooks
Messy spreadsheets
Shifting headers, stacked tables, merged cells and subtotal rows in sheets people built by hand.
Start with the full guides
The long-form treatment of messy spreadsheets, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toCombine Sheets with Different Layouts into One TableStack sheets that hold the same kind of data in different layouts — headers on different rows, columns in a different order and under different names, extra columns, total rows — into one clean table with pandas: find each sheet's header row, map column synonyms to standard names, drop totals and notes, record the source, and report columns that could not be mapped instead of losing them.
- How-toExtract Hyperlink URLs from Excel Cells with openpyxlpandas reads the text a link displays, never the address behind it. Get the URLs out of Excel cells with openpyxl — inserted links, mailto links, links to other sheets and HYPERLINK formulas — and handle workbooks too large for normal mode.
- How-toExtract Report Header Fields from an Excel Sheet with PythonMany exported reports put the branch, period, currency and run date in label-value pairs above the table. Read those fields with openpyxl wherever they sit, find where the table starts, parse the period into real dates and attach the fields to every row before combining files.
- How-toFind the Header Row in an Excel Sheet with a Shifting LayoutExports that start their table on row 3 one month and row 6 the next break skiprows. Scan for the row that holds your required labels, then read from there — with aliases and column offsets handled.
- How-toParse Numbers with Units in Excel CellsTurn cells like "12 kg", "1,200 g", "2.5t", "500 ml", "3.5k" and "1,5 kg" into numbers in one unit with pandas: a regular expression that splits value from unit, a conversion table for the units you expect, suffixes such as k and M, decimal commas, the pint library for broader unit support, and a report of every value that could not be parsed.
- How-toParse Records That Span Multiple Rows in Excel with pandasBank statements wrap long descriptions onto extra rows, and contact exports stack one field per row. Number each record from the row that starts it, join the continuation lines, pivot stacked fields into columns, and catch orphan lines and repeated fields instead of silently mixing records.
- How-toRead a Calendar-Style Sheet into a TableRota planners, booking sheets and leave trackers are often laid out as calendars: a month title, weekday columns, a row of day numbers and a row of entries under each. Turn such grids into one row per date with openpyxl and pandas: find each month block, pair day numbers with their entries, build real dates, skip the padding days from neighbouring months, and validate that every day of every month was read.
- How-toRead an Excel Sheet Where Records Run Across ColumnsSome sheets are laid out sideways: field names down column A and one record per column — supplier comparisons, product spec sheets, quotes side by side. Read them with header=None and index_col=0, transpose, fix the column types that transposing mixes up, handle several blocks on one sheet, and write the tidy table back.
- How-toRead Cell Colours and Font Styles as Data with openpyxlWhen a spreadsheet uses green for done and red for blocked, the colour is the data. Read fill colours, bold and strikethrough with openpyxl and turn them into real columns.
- How-toRead Dropdown Lists and Validation Rules from an Existing WorkbookFind out what a template's dropdowns allow — the regions, statuses and priority codes behind each data validation rule — and check the data people entered against them. Read validation rules with openpyxl, resolve inline lists, range references and named ranges to real values, and report every cell that breaks its rule.
- How-toRead Excel with Multi-Row Headers in pandasLoad a sheet whose column headers span two rows — a merged quarter above Revenue and Units — into a MultiIndex, then select, flatten or reshape it into a tidy table.
- How-toRead Multiple Tables from One Excel SheetPull several stacked or side-by-side tables out of a single worksheet with pandas: split on blank rows and columns, find blocks by their header label, or read named Excel tables.
- How-toRemove Subtotal and Total Rows from an Excel ExportSubtotal and grand-total rows double every sum you compute. Find them by label, by missing detail fields or by SUBTOTAL formulas, remove them, and use the totals to check your own numbers.
- How-toSkip Hidden and Filtered Rows When Reading Excelpandas reads every row, including the ones hidden by a filter or collapsed in a group. Use openpyxl to find hidden rows, columns and sheets, and read only what the user can see.
- How-toStop Reading at Notes and Footers Below the DataMany Excel reports put a total row, a blank line and footnotes such as "Source:" or "* provisional" under the table, and pandas reads them all as data. Find where the table really ends — the first blank row, the first row whose key does not look like a key, or a footer marker — read exactly that many rows, keep the footnotes as metadata, and turn footnote markers inside cells into a flag column.
- How-toTurn a Pivot Table Export Back into Tidy DataSomeone sends a copy-pasted pivot table instead of the data behind it. Rebuild one row per observation with pandas: combine the two header rows into year and quarter, forward-fill the row labels that pivot layouts leave blank, drop subtotal and grand-total rows, unpivot the value columns, convert the numbers, and prove the result by recomputing the totals the export showed.
- How-toFreeze the Header Row in Excel with openpyxlFreeze the header row in Excel from Python with openpyxl using ws.freeze_panes. Freeze rows, columns, or both, understand the anchor cell, and unfreeze — runnable.
- How-toHandle Merged Cells When Reading Excel with pandasMerged cells read as one value and a run of blanks. Fix them in pandas with forward fill, detect merges with openpyxl, and unmerge a workbook before importing it.
- How-toUnmerge Cells and Fill the Values with openpyxlTurn merged cells into a proper table — find every merged range, unmerge it, copy the top-left value into each cell, keep the formatting, and handle the read-only and pandas cases where merge information is missing.
- How-toForward-Fill Blank Cells in Grouped Excel Reports with pandasGrouped Excel reports show each region or category once and leave the rows below it blank, often in merged cells. Fill those labels down with pandas without leaking a label into the next group, filling data columns by mistake, or carrying labels into total rows.
- How-toExtract Tables from a PDF into Excel with PythonGet tables out of PDF statements and reports and into a workbook with pdfplumber and pandas: ruled tables that span several pages, repeated header rows, amounts with thousands separators, day-first dates, borderless tables rebuilt from word positions, a control-total check and a formatted .xlsx.
- How-toTranspose a Range of Cells with openpyxlSwap rows and columns in a worksheet from Python: transpose values with zip, carry fonts, fills, borders and number formats across, handle merged cells and column widths, transpose with pandas when styles do not matter, and write a live TRANSPOSE array formula instead of a copy.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
Follow it through a track
Prefer an ordered route? These tracks cover messy spreadsheets along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Messy spreadsheets is part of Reading & writing workbooks.