Find the Header Row in an Excel Sheet with a Shifting Layout

skiprows=2 is a promise that the table always starts on row 3. Exports from finance systems, CRMs and shared drives break that promise constantly: one month the banner has a company name and a date, the next it gains a filter description and two blank lines, and the header drifts down to row 6. The script does not crash. It reads the banner as the header, produces columns called Unnamed: 1, and fails three steps later with a KeyError that says nothing about the real cause. This guide from Extracting Data from Messy Excel Sheets finds the header by what it says, not where it was.

Prerequisites

pip install pandas openpyxl

This creates two exports with different banner heights, an unused column A, a blank line and a total row at the bottom — the usual features of a system-generated sheet:

from openpyxl import Workbook

for name, header_row in (("march.xlsx", 3), ("april.xlsx", 6)):
    wb = Workbook()
    ws = wb.active
    ws.title = "Export"
    ws.append(["ACME Corp — Sales export"])
    ws.append([f"Generated {name[:-5]} run"])
    for _ in range(header_row - 3):
        ws.append([])
    ws.append([None, "Order ID", "Customer", "Amount"])
    ws.append([None, 1, "Ana", 10.5])
    ws.append([None, 2, "Ben", 20])
    ws.append([])
    ws.append([None, "Total", None, 30.5])
    wb.save(name)

Step 1 — Preview the top of the sheet without a header

Read only the first few dozen rows with header=None. This is cheap even for a large file because nrows stops the parse early, and the result's integer index maps directly onto the value that read_excel(header=...) expects in the next step.

import pandas as pd

preview = pd.read_excel("april.xlsx", header=None, nrows=30)
print(preview.head(8))

Blank rows in the banner are kept as rows of NaN, so position 5 in the preview really is the sixth row of the sheet. That one-to-one mapping is what makes the scan safe.

Step 2 — Score each row against the labels you require

Normalise every non-empty cell in a row — strip it, lower-case it — and check whether the set of labels you need is a subset of what the row contains. Subset, not equality, so an extra column added to the export next quarter does not break detection.

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

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

def find_header_row(path, sheet_name=0, required=REQUIRED, scan_rows=30) -> int:
    preview = pd.read_excel(path, sheet_name=sheet_name, header=None, nrows=scan_rows)
    for position, row in preview.iterrows():
        labels = {normalise(v) for v in row.dropna()}
        if required <= labels:
            return position
    raise ValueError(f"no row in the first {scan_rows} contains all of {sorted(required)}")

for path in ("march.xlsx", "april.xlsx"):
    print(path, find_header_row(path))
# march.xlsx 2
# april.xlsx 5

" ".join(str(value).split()) collapses runs of whitespace, including line breaks and the double spaces people type by accident, before comparing. The error message lists the labels that were expected, which is precisely what somebody needs to know when a column has been renamed upstream.

Step 3 — Accept known aliases

Label drift is the other half of the problem: Order ID becomes Order #, Amount becomes Amount (EUR). Map every accepted spelling to a canonical name, match on canonical names, and rename the columns once the table is read. The mapping doubles as documentation of every variant you have met.

ALIASES = {
    "order id": "order_id", "order #": "order_id", "order no": "order_id",
    "customer": "customer", "client": "customer",
    "amount": "amount", "amount (eur)": "amount", "net amount": "amount",
}
CANONICAL = set(ALIASES.values())

def canonical(value) -> str | None:
    return ALIASES.get(normalise(value))

def find_header_row_aliased(path, sheet_name=0, scan_rows=30) -> int:
    preview = pd.read_excel(path, sheet_name=sheet_name, header=None, nrows=scan_rows)
    for position, row in preview.iterrows():
        if CANONICAL <= {canonical(v) for v in row.dropna()}:
            return position
    raise ValueError(f"no header row with columns {sorted(CANONICAL)}")

print(find_header_row_aliased("april.xlsx"))   # 5

Keep the alias table in configuration rather than code if the files come from several sources, as in Keep Excel Report Settings in a Config File.

Step 4 — Read the table and trim what surrounds it

With the header position known, read the sheet again from that row. Then remove the three things that typically surround an exported table: an empty leading column, the blank row before the footer, and the footer itself.

def read_export(path, sheet_name=0) -> pd.DataFrame:
    header = find_header_row_aliased(path, sheet_name)
    frame = pd.read_excel(path, sheet_name=sheet_name, header=header)

    frame = frame.dropna(axis=1, how="all")                    # unused column A
    frame = frame.rename(columns=lambda c: canonical(c) or c)

    blank = frame.isna().all(axis=1).to_numpy()
    if blank.any():
        frame = frame.iloc[: blank.argmax()]                   # stop at the first blank row
    return frame.reset_index(drop=True)

for path in ("march.xlsx", "april.xlsx"):
    table = read_export(path)
    print(path, table.columns.tolist(), len(table), table["amount"].sum())
# march.xlsx ['order_id', 'customer', 'amount'] 2 30.5
# april.xlsx ['order_id', 'customer', 'amount'] 2 30.5

Stopping at the first fully blank row removes the footer without having to recognise it. When a footer sits directly under the data with no gap, filter it by content instead, which is the subject of Remove Subtotal and Total Rows from an Excel Export.

Step 5 — Make detection failures obvious

The value of this approach is that the failure mode changes from "wrong data" to "clear error". Push that further by reporting what the scan actually saw, so the person reading the log can tell a renamed column from a completely different file.

def describe_top(path, sheet_name=0, rows=10) -> str:
    preview = pd.read_excel(path, sheet_name=sheet_name, header=None, nrows=rows)
    lines = []
    for position, row in preview.iterrows():
        cells = [str(v) for v in row.dropna()]
        if cells:
            lines.append(f"  row {position + 1}: {' | '.join(cells)[:90]}")
    return "\n".join(lines)

try:
    find_header_row("april.xlsx", required={"order id", "customer", "region"})
except ValueError as exc:
    print(exc)
    print(describe_top("april.xlsx"))

That printout shows Order ID | Customer | Amount on row 6 and no Region anywhere — enough for a human to see at a glance that the export no longer contains a column the report depends on.

Step 6 — Process a folder of exports with mixed layouts

Header detection earns its keep when a job consumes many files at once: twelve monthly exports, or one file per branch, each saved by a different person with a slightly different banner. Run the reader over the whole folder, record the detected header position and row count per file, and keep going when one file fails so a single broken export does not block the other eleven.

from pathlib import Path

def load_folder(folder: str) -> tuple[pd.DataFrame, pd.DataFrame]:
    frames, audit = [], []
    for path in sorted(Path(folder).glob("*.xlsx")):
        try:
            header = find_header_row_aliased(path)
            table = read_export(path).assign(source=path.name)
        except ValueError as exc:
            audit.append({"file": path.name, "header_row": None, "rows": 0, "error": str(exc)})
            continue
        frames.append(table)
        audit.append({"file": path.name, "header_row": header + 1, "rows": len(table), "error": ""})
    combined = pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()
    return combined, pd.DataFrame(audit)

combined, audit = load_folder(".")
print(audit)
print(combined.groupby("source")["amount"].sum())

The audit frame is the part to keep. Writing it to a second sheet of the output workbook, or to the job log, records which row each file's table started on. When a total looks wrong next month, a header position that suddenly changed from 3 to 9 is often the first clue that the export itself changed — a new filter block in the banner, or a different report template chosen by mistake. header + 1 converts the zero-based pandas position back into the row number a person sees in Excel, which avoids a confusing off-by-one conversation. Combining the files afterwards follows the patterns in Combine Multiple Excel Files into One in Python.

Common pitfalls

Performance and scale notes

The preview is the only extra cost, and with nrows=30 it is a small fraction of the full read. The second read parses the table as normal. If a file is large enough that two reads hurt, read once with header=None, find the header position in memory, and promote that row with set_axis — the same technique used in Read Multiple Tables from One Excel Sheet.

Conclusion

Find the header by its content. Preview the top of the sheet with header=None and nrows, normalise each row's labels, and pick the first row that contains every label you need — through an alias table when the names drift. Read from that position, drop empty columns, stop at the first blank row, and make detection failures print what the scan saw. The script then tolerates banners that grow and columns that are added, and fails loudly on the one change that genuinely needs a human.

Frequently asked questions

Why not just use skiprows with a larger number? Because a fixed number is only right for one layout. Too small and the banner becomes the header; too large and you skip real rows. Detecting the header adapts to each file.

How many rows should the preview scan? Enough to cover the tallest banner you have seen, with room to spare. Thirty is a good default; a report with a long parameter block may need fifty. The cost of scanning more is small.

What if the header spans two rows? Find the lower row by its labels, then pass header=position - 1, position to read_excel. The approach in the multi-row headers guide explains how the two levels combine.

Can I detect the header column as well as the row? Yes. Once the row is found, the first non-empty cell in it gives the starting column, and usecols can restrict the read to the table's width.

Does this work with CSV exports too? Yes. Replace read_excel with read_csv in the preview and the final read; header=None and nrows behave the same way. CSV banners are often shorter, but they drift just as much.

Related

• Up one level: Extracting Data from Messy Excel Sheets — techniques for sheets built for people.
• Skip Rows and Set the Header When Reading Excel with pandas — the fixed-layout options this page replaces.
• Read Excel with Multi-Row Headers in pandas — when the detected header has a second storey.
• Validate Excel Columns Before Import with pandas — checking the columns after they are found.
• Remove Subtotal and Total Rows from an Excel Export — cleaning up the bottom of the same export.