Extracting Data from Messy Excel Sheets

Most tutorials on reading Excel assume a tidy sheet: one header in row 1, one record per row, nothing else. The workbooks that arrive in a reporting job are rarely like that. They are exports with a company banner of unpredictable height, summary tabs holding three small tables, finance layouts with a quarter label merged above each pair of columns, reports with a subtotal after every group, filtered views mailed "as attached", and trackers where the status is a fill colour. This section of Advanced Data Transformation and Cleaning is about turning those sheets into ordinary DataFrames — reliably, on every run, and with loud failures when the layout changes in a way code should not guess about.

What this section covers

The techniques fall into three groups. The first locates the data: finding the header row by its labels, and splitting a sheet that holds several tables. The second repairs structure: combining multi-row headers into usable column names and removing subtotal rows that would inflate every sum. The third recovers meaning that is not stored as values: which rows a filter hid, and what a colour was supposed to say.

• Find the Header Row in an Excel Sheet with a Shifting Layout — scan for required labels instead of trusting skiprows.
• Read Multiple Tables from One Excel Sheet — split on blank rows and columns, or find blocks by anchor.
• Read Excel with Multi-Row Headers in pandas — header=[0, 1], flattening and stacking.
• Remove Subtotal and Total Rows from an Excel Export — by label, by shape or by SUBTOTAL formula, then reconcile.
• Skip Hidden and Filtered Rows When Reading Excel — read what the user actually saw.
• Read Cell Colours and Font Styles as Data with openpyxl — turn fills and strikethrough into columns.

Each task below builds a small sample workbook first, so every code block can be run as written.

The first rule: read the sheet raw

Every technique in this section starts from the same place. Instead of letting read_excel decide where the header is and what type each column holds, read the whole used range with header=None. You get every cell exactly where it sits, with integer labels that map straight back to the worksheet: row label 0 is Excel row 1, column label 0 is column A.

from openpyxl import Workbook
import pandas as pd

wb = Workbook()
ws = wb.active
ws.title = "Export"
ws.append(["Northwind Trading — monthly export"])
ws.append(["Filter: all regions"])
ws.append([])
ws.append(["Order ID", "Region", "Rep", "Amount"])
for row in [(501, "North", "Ana", 1200), (502, "North", "Ben", 800), (None, "North Total", None, 2000),
            (503, "South", "Cara", 950), (504, "South", "Dev", 650), (None, "South Total", None, 1600)]:
    ws.append(list(row))
wb.save("export.xlsx")

raw = pd.read_excel("export.xlsx", sheet_name="Export", header=None)
print(raw)

Reading raw costs nothing extra — it is the same parse — and it separates two decisions that read_excel otherwise makes together and silently: where the table is and what the data means. Everything that follows is ordinary DataFrame slicing on raw, which you can repeat as many ways as you like without touching the file again.

Finding the header row by its labels

A banner that grows by a line breaks every skiprows value in your code, and it breaks it quietly: the banner becomes the header and the real header becomes the first data row. The robust alternative is to look for the row that contains the labels you need. Normalise each cell — trim, collapse internal whitespace, lower-case — and test whether your required set is a subset of the row.

REQUIRED = {"order id", "region", "amount"}

def norm(value) -> str:
    return " ".join(str(value).split()).lower()

def header_position(frame: pd.DataFrame, required=REQUIRED) -> int:
    for position, row in frame.iterrows():
        if required <= {norm(v) for v in row.dropna()}:
            return position
    raise ValueError(f"no row contains {sorted(required)}")

pos = header_position(raw)
table = raw.iloc[pos + 1:].set_axis(raw.iloc[pos], axis=1).rename_axis(None, axis=1).reset_index(drop=True)
print(pos, table.columns.tolist())   # 3 ['Order ID', 'Region', 'Rep', 'Amount']

Two design choices make this dependable. Using a subset test means an extra column added to the export next quarter does not break detection. Requiring several labels means a banner line such as "Amount by region" cannot be mistaken for the header. When labels drift between sources — Order # in one system, Order ID in another — add an alias table that maps every known spelling to a canonical name, as the full header detection guide shows, together with a preview read that uses nrows so large files are only scanned at the top.

Splitting a sheet that holds several tables

Summary tabs are built for reading, not parsing: a regional table, a gap, a product table, and perhaps a quota list off to the right. In the raw frame, a row where every cell is empty marks a vertical boundary and a column where every cell is empty marks a horizontal one. A cumulative sum over the blank-row mask numbers each block, and groupby hands them back one by one.

import numpy as np

wb = Workbook()
ws = wb.active
ws.append(["Region", "Revenue"])
ws.append(["North", 2000])
ws.append(["South", 1600])
ws.append([])
ws.append(["Product", "Units"])
ws.append(["Widget", 420])
wb.save("summary.xlsx")

sheet = pd.read_excel("summary.xlsx", header=None)
sheet = sheet.replace(r"^\s*$", np.nan, regex=True)       # whitespace is not content

blank = sheet.isna().all(axis=1)
blocks = []
for _, block in sheet[~blank].groupby(blank.cumsum()[~blank]):
    block = block.dropna(axis=1, how="all")
    blocks.append(block.iloc[1:].set_axis(block.iloc[0], axis=1).rename_axis(None, axis=1).reset_index(drop=True))

for b in blocks:
    print(b, end="\n\n")

The replace line is the one that prevents most failures. Excel keeps a cell alive once it has been formatted or once somebody typed a space into it, and a "blank" row that holds a single space glues two tables together. When the layout is too irregular for blank-line splitting — a note typed into the gap column, for instance — find each table by its header label instead. Both approaches, plus reading named Excel tables that record their own ranges, are in Read Multiple Tables from One Excel Sheet.

Repairing headers that span two rows

Finance and planning sheets put a merged label such as Q1 above a pair of sub-columns. Pass both header rows to read_excel and pandas builds a two-level column index, forward-filling the empty cells a merge leaves behind. Put the label column in the index so it does not end up called Unnamed: 0_level_1.

wb = Workbook()
ws = wb.active
ws.append(["Region", "Q1", None, "Q2", None])
ws.append([None, "Revenue", "Units", "Revenue", "Units"])
ws.append(["North", 1200, 30, 1500, 34])
ws.append(["South", 900, 22, 1100, 25])
ws.merge_cells("B1:C1")
ws.merge_cells("D1:E1")
wb.save("quarterly.xlsx")

wide = pd.read_excel("quarterly.xlsx", header=[0, 1], index_col=0).rename_axis("Region")
revenue = wide.xs("Revenue", axis=1, level=1)            # one measure, every quarter
long = wide.stack(level=0, future_stack=True).rename_axis(["Region", "Quarter"]).reset_index()
print(revenue)
print(long)

Which shape to keep depends on where the data goes next. The MultiIndex is convenient for interactive selection; flattened names such as Q1 Revenue suit CSV files and SQL tables; and the long format produced by stack — one row per region and quarter — is what grouping, charting and database loads prefer, because a new quarter adds rows rather than columns. The details, including building the header by hand when a title block sits above it and validating that each quarter carries the expected measures, are in Read Excel with Multi-Row Headers in pandas.

Removing subtotal and total rows

Rollup rows are the most expensive mess to miss, because nothing fails: every sum is just wrong. The export at the top of this page has a subtotal after each region, so summing Amount without removing them doubles the result. Detect rollups by their label, by an empty record identifier, or by both.

TOTAL = r"\b(?:sub-?)?total\b"

label_hit = table["Region"].astype(str).str.contains(TOTAL, case=False, regex=True)
shape_hit = table["Order ID"].isna() & table["Amount"].notna()
rollups = table[label_hit | shape_hit]
detail = table[~(label_hit | shape_hit)].reset_index(drop=True)

computed = detail.groupby("Region")["Amount"].sum()
reported = rollups.assign(Region=rollups["Region"].str.replace(r"\s*total$", "", case=False, regex=True)) \
                  .set_index("Region")["Amount"]
assert (computed == reported.reindex(computed.index)).all(), "subtotals do not reconcile"
print(detail)
print(computed.to_dict())

The last three statements are the part worth copying. The totals you remove were computed by the system that produced the export, so comparing them with your own group sums is a free test that the right rows were dropped and nothing went missing in the read. When the rollups are SUBTOTAL formulas — the output of Excel's Data → Subtotal command — openpyxl can identify them exactly from the formula text, as shown in Remove Subtotal and Total Rows from an Excel Export.

Reading only what the user saw

read_excel returns every stored row, including rows hidden by an autofilter, rows hidden by hand and rows inside collapsed outline groups. For a report somebody prepared and filtered on purpose, those rows are not part of the answer. openpyxl exposes each row's visibility through row_dimensions, and every one of those three mechanisms sets the same hidden flag.

from openpyxl import load_workbook

wb = Workbook()
ws = wb.active
ws.append(["Order", "Region", "Amount"])
for r in [(1, "North", 250), (2, "South", 120), (3, "West", 400)]:
    ws.append(list(r))
ws.row_dimensions[3].hidden = True                    # the South row
wb.save("filtered.xlsx")

sheet = load_workbook("filtered.xlsx").active
hidden = {i for i, dim in sheet.row_dimensions.items() if dim.hidden}
frame = pd.read_excel("filtered.xlsx")
visible = frame[~(pd.RangeIndex(len(frame)) + 2).isin(hidden)]
print(sorted(hidden), visible["Amount"].sum())       # [3] 650

Whether visible-only is correct is a judgement, not a technical question. A filtered report a person sends is usually meant to be read as filtered; an extract a system produced should be read in full even if someone left a filter on. Make the choice an explicit parameter and log how many hidden rows were skipped or included. Grouped columns, hidden sheets and the zero-height-row trap are covered in Skip Hidden and Filtered Rows When Reading Excel.

Recovering meaning from colours and fonts

The last kind of mess is information that was never typed at all: a task shaded green when done, a name struck through when the person left. pandas discards formatting, so read it with openpyxl. A solid fill's colour lives in fill.fgColor, and its type tells you whether it is an explicit RGB value, a theme slot with a tint, or a legacy indexed colour.

from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active
ws.append(["Task"])
for task, colour in [("Invoice run", "C6EFCE"), ("Audit prep", "FFC7CE")]:
    ws.append([task])
    ws.cell(ws.max_row, 1).fill = PatternFill("solid", start_color=colour)
wb.save("tracker.xlsx")

STATUS = {"C6EFCE": "done", "FFC7CE": "blocked"}
sheet = load_workbook("tracker.xlsx").active
rows = []
for (cell,) in sheet.iter_rows(min_row=2, max_col=1):
    color = cell.fill.fgColor
    key = color.rgb[-6:] if cell.fill.fill_type and color.type == "rgb" else None
    rows.append({"task": cell.value, "status": STATUS.get(key, key or "not started")})
print(pd.DataFrame(rows))

Before trusting any colour you read, check ws.conditional_formatting. A cell painted by a conditional format rule has no fill of its own — the colour is computed by Excel at display time — so openpyxl reports it as unfilled. In that case, re-implement the rule's condition in pandas instead. Theme colours, font flags and writing a real Status column back are covered in Read Cell Colours and Font Styles as Data with openpyxl.

Putting it together: a defensive loader

In production these steps belong in one function per source, run in a fixed order, with each decision logged. The order matters: locate the table before repairing it, remove rollups before typing columns (a Total label in a numeric column blocks conversion), and validate last.

def load_export(path: str) -> pd.DataFrame:
    raw = pd.read_excel(path, header=None).replace(r"^\s*$", np.nan, regex=True)
    pos = header_position(raw)
    frame = raw.iloc[pos + 1:].set_axis(raw.iloc[pos], axis=1).rename_axis(None, axis=1)
    frame = frame.dropna(axis=1, how="all").dropna(axis=0, how="all")

    is_rollup = (frame["Region"].astype(str).str.contains(TOTAL, case=False, regex=True)
                 | frame["Order ID"].isna())
    detail = frame[~is_rollup].reset_index(drop=True)
    detail = detail.astype({"Order ID": "int64", "Amount": "float64"})

    missing = REQUIRED - {norm(c) for c in detail.columns}
    if missing:
        raise ValueError(f"{path}: missing columns {sorted(missing)}")
    print(f"{path}: header on row {pos + 1}, {len(detail)} records, {int(is_rollup.sum())} rollups removed")
    return detail

print(load_export("export.xlsx"))

The log line is not decoration. When a total looks wrong next month, "header on row 6" instead of "row 4", or "3 rollups removed" instead of "2", points directly at what changed in the export. For formal schema checks on the result — types, ranges, allowed values — hand the frame to the tools in Validating Excel Data with Python.

When to push back on the file instead

Every technique here is a workaround for a workbook that was not designed to be read by code. Some are permanent — exports from systems you do not control will always have banners — but some are worth fixing at the source. If a team shades rows to record status, a Status column with a conditional format looks identical to its readers and removes the colour-decoding step entirely. If a summary tab stacks tables, each table can be an Excel table object with a name, which openpyxl reads exactly. And if people keep filtering the file before sending it, ask for the unfiltered extract and apply the filter in code, where it is visible and repeatable. The goal of a parser for messy sheets is to keep the job running today, not to make the mess permanent.

Matching the symptom to the technique

Messy sheets rarely announce what is wrong with them. The symptom you see in pandas is usually indirect — a column called Unnamed: 3, a sum that is too high, a KeyError three functions away from the read — so it helps to work backwards from what the DataFrame looks like to the layout problem that caused it.

Several of these usually appear together. An export with a banner almost always has a footer; a report with subtotals often prints its group label once. Work through them in the loader order shown above rather than fixing whichever one you notice first.

Testing a loader against real files

A messy-sheet loader is a set of rules about files you do not control, so the most valuable test data is the files themselves. Keep a small folder of real examples — anonymised if necessary — including every layout variant that has ever broken the job, and assert the facts that must hold for each one: the columns found, the number of records, and a control total.

import pytest

CASES = [
    ("export.xlsx", 4, 3600.0),
]

@pytest.mark.parametrize("path, records, total", CASES)
def test_load_export(path, records, total):
    frame = load_export(path)
    assert {"order id", "region", "amount"} <= {norm(c) for c in frame.columns}
    assert len(frame) == records
    assert frame["Amount"].sum() == pytest.approx(total)

test_load_export("export.xlsx", 4, 3600.0)
print("loader test passed")

Every time a new file breaks the loader, add it to the folder before fixing the rule. The fix then has a failing test to turn green, and the old layouts stay covered so a rule tightened for April cannot silently break March. The broader pattern for testing spreadsheet output is described in Test Excel Output with pytest.

Key takeaways

• Read the sheet with header=None first; decide where the table is before deciding what it means.
• Find headers by required labels with a subset test, never by fixed skiprows.
• Normalise whitespace to NaN before treating any row or column as blank.
• Use header=[0, 1] with index_col for two-row headers, and stack the top level for analysis.
• Remove subtotal and total rows before summing or typing, then reconcile against them.
• Hidden, filtered and grouped rows are all visible to pandas; openpyxl's row_dimensions tells them apart.
• Colours are readable through fill.fgColor, except when conditional formatting paints them.
• Log every decision a loader makes, so layout changes show up in the log before they show up in a report.

Frequently asked questions

Is there a library that parses messy Excel sheets automatically? Not reliably. Layout inference tools exist, but they guess, and a wrong guess produces plausible-looking data. Explicit rules — required labels, blank boundaries, rollup patterns — are short to write and fail loudly when the file changes.

Should I use pandas or openpyxl for messy sheets? Both. pandas with header=None is the fastest way to get values into a frame for locating and slicing. openpyxl is needed for anything stored outside values: hidden rows, fills, fonts, merged ranges, named tables and formula text.

How do I handle a sheet that changes layout every month? Replace every positional assumption with a content rule — the header row by its labels, table edges by blank rows, totals by label or missing identifier — and log what each rule found so that a change is visible.

Why do my sums come out too high? Almost always because the sheet contains subtotal or grand total rows. Remove them before aggregating and compare your group sums with the removed totals to confirm.

Can these techniques handle .xls and .ods files? Everything that runs on the pandas frame works for any engine pandas supports. The openpyxl techniques — visibility, colours, formulas, named tables — need .xlsx or .xlsm, so convert older formats first.

Related

• Up one level: Advanced Data Transformation and Cleaning — the full section on reshaping, cleaning and validating spreadsheet data.
• Find the Header Row in an Excel Sheet with a Shifting Layout
• Read Multiple Tables from One Excel Sheet
• Read Excel with Multi-Row Headers in pandas
• Remove Subtotal and Total Rows from an Excel Export
• Skip Hidden and Filtered Rows When Reading Excel
• Read Cell Colours and Font Styles as Data with openpyxl
• Cleaning Excel Data With Pandas — the next step once the table is out.
• Reading Excel Files with Pandas — the read_excel options these techniques build on.
• Validating Excel Data with Python — checking the extracted table before it is trusted.

More in this section:

• Combine Sheets with Different Layouts into One Table
• Parse Numbers with Units in Excel Cells
• Read a Calendar-Style Sheet into a Table
• Read an Excel Sheet Where Records Run Across Columns
• Read Dropdown Lists and Validation Rules from an Existing Workbook
• Stop Reading at Notes and Footers Below the Data
• Turn a Pivot Table Export Back into Tidy Data