Stop Reading at Notes and Footers Below the Data

Reports prepared for people often end with more than data. Under the table there is a total row, a blank line, then a few notes: "Source: finance system, extracted 30 Sep", "Figures marked * are provisional", "Excludes intercompany sales". pd.read_excel reads all of it as rows of the table. The notes land in the first column with blanks everywhere else, the total row doubles every sum, and the numeric columns turn into text because one cell says "Source: …". Some values carry the footnote marker themselves — 1,240* — which makes them text too. This page finds where the table actually ends, reads only the data, keeps the notes as metadata instead of throwing them away, and turns footnote markers in cells into a clean number plus a flag. It belongs to Extracting Data from Messy Excel Sheets.

Prerequisites

pip install pandas openpyxl

branch_sales.xlsx holds six branches with orders and revenue, followed by a bold total row, a blank row, and four lines of notes in column A. Two revenue values carry an asterisk footnote marker and were typed as text with thousands separators, as happens when figures are copied from another document.

Step 1 — See what a plain read does

import pandas as pd

naive = pd.read_excel("branch_sales.xlsx")
print(naive.tail(6).to_string())
print(naive.dtypes.to_dict())
print("sum of Orders as read:", pd.to_numeric(naive["Orders"], errors="coerce").sum())

The DataFrame has twelve rows instead of six: the total row, a row of NaN for the blank line and four "rows" of notes. Revenue is an object column because of the text cells, so it cannot be summed at all, and summing Orders gives 3,664 — exactly double the real 1,832, because the total row is counted as a branch. Nothing errors; every downstream figure is simply wrong. The fix is to stop reading where the table ends.

Step 2 — Find where the table ends

There are three reliable signals, and it is worth combining them: the first completely blank row after the header, a row whose key column does not look like a key, and a known footer word such as "Total" or "Notes". Scan column A with openpyxl to find the first row that fails:

import re
from openpyxl import load_workbook

KEY = re.compile(r"^[A-Z]{3}-\d{2}$")          # branch codes look like LDS-01
STOP_WORDS = ("total", "notes", "source")

def data_end(path, sheet=None, header_row=1):
    ws = load_workbook(path, read_only=True)[sheet] if sheet else load_workbook(path, read_only=True).active
    for r, row in enumerate(ws.iter_rows(min_row=header_row + 1, values_only=True), start=header_row + 1):
        first = row[0]
        if all(v in (None, "") for v in row):
            return r - 1, "blank row"
        text = str(first).strip()
        if text.lower().startswith(STOP_WORDS) or not KEY.match(text):
            return r - 1, f"stop at {text[:20]!r}"
    return r, "end of sheet"

last_row, reason = data_end("branch_sales.xlsx")
print("data ends at Excel row", last_row, "—", reason)

The scan stops at row 8, the total row, because "Total" is neither a branch code nor allowed through the stop words; the last data row is row 7. Checking the key's shape with a regular expression is the strongest of the three signals: it also catches a footer the stop-word list did not anticipate, such as "Excludes intercompany sales", and a total row with no blank line before it. Where keys have no fixed format, fall back on the blank row and the stop words. Reading in read-only mode keeps the scan fast even for large sheets.

The order of the checks does not matter much in practice, but having all three makes the scan robust to the variations reports go through over time: a footer added without a blank line, a blank line inserted before the total, a renamed "Grand total". When the scan stops somewhere unexpected — after one row, or at the very end of a long sheet — log the reason it gives; that message is usually enough to see what changed in the report layout.

Step 3 — Read exactly the data rows

With the last row known, nrows tells pandas how many data rows to read after the header:

df = pd.read_excel("branch_sales.xlsx", nrows=last_row - 1)
print(df.to_string(index=False))
print("rows:", len(df), "| Orders total:", df["Orders"].sum())

nrows counts data rows, excluding the header, so the header on row 1 and data ending on row 7 give six rows. Orders now sums to 1,832, matching the report's own total row — a useful check in itself. Revenue is still text because of the two marked values; Step 5 handles that. If the header is not on the first row, combine skiprows and nrows, as described in Skip Rows and Set the Header When Reading Excel with pandas.

Step 4 — Keep the footer as metadata

The notes matter — they say where the figures came from and which ones are provisional — so capture them rather than discard them. Read everything below the data that has text in column A:

wb = load_workbook("branch_sales.xlsx", read_only=True)
below = [row[0] for row in wb.active.iter_rows(min_row=last_row + 1, values_only=True) if row and row[0]]
wb.close()
total_row = below[0] if below and str(below[0]).lower().startswith("total") else None
notes = [str(n) for n in below if str(n).lower() not in ("total", "notes:")]
meta = {
    "source": next((n.split(":", 1)[1].strip() for n in notes if n.lower().startswith("source")), None),
    "provisional_note": next((n for n in notes if n.startswith("*")), None),
    "other_notes": [n for n in notes if not n.lower().startswith(("source", "*"))],
}
print(meta)

The source line, the meaning of the asterisk and the remaining caveat end up in a small dictionary that can travel with the data — written to a "Notes" sheet in the output, logged with the run, or attached to a database load. A report built from this data should carry the same caveats; losing "Excludes intercompany sales" on the way through a pipeline is how wrong conclusions get made. Header fields above a table are handled the same way in Extract Report Header Fields from an Excel Sheet.

Step 5 — Turn footnote markers into a flag

Values like 38,920* mean "38,920, provisional". Split each into a clean number and a boolean flag:

def split_marker(value, marker="*"):
    if isinstance(value, (int, float)):
        return float(value), False
    text = str(value).strip()
    flagged = text.endswith(marker)
    number = float(text.rstrip(marker).replace(",", "").strip())
    return number, flagged

df[["Revenue", "Revenue provisional"]] = df["Revenue"].apply(lambda v: pd.Series(split_marker(v)))
print(df.to_string(index=False))
print("revenue total:", df["Revenue"].sum(), "| provisional share:",
      round(df.loc[df["Revenue provisional"], "Revenue"].sum() / df["Revenue"].sum() * 100, 1), "%")

Revenue becomes a numeric column again and a new boolean column records which values were provisional. The total, 232,325, now matches the report's total row exactly — so the data, the cleaned values and the footer agree. The provisional share — about 30% of revenue here — is the kind of figure a reader of any summary built on this data deserves to know. The same pattern handles other markers: (e) for estimates, † for restated figures, or a trailing p.

Common pitfalls

• Reading to the end of the sheet. Totals and notes become rows; find the real end first.
• Relying only on a blank row. Total rows often follow the data directly; check the key's format too.
• Throwing the notes away. Keep source and caveats as metadata.
• Leaving marked values as text. Split the marker into a flag and convert the number.
• Not using the report's own totals. They are a free check on the extraction.

Conclusion

To read only the data from a report sheet with totals and notes underneath, find the last data row first — the first blank row, the first row whose key does not match the expected pattern, or the first footer word — and pass nrows to read_excel. Capture the lines below as metadata, because sources and caveats belong with the data. Split footnote markers such as a trailing asterisk into a clean number and a flag column, and confirm the extraction by comparing your totals with the report's own total row.

Frequently asked questions

How do I stop pandas reading the notes below an Excel table? Find the last data row, for example the row before the first blank row or the first row whose key column does not match the expected format, and pass nrows to read_excel.

How do I exclude the total row when reading an Excel report? Stop reading before it by detecting a label such as Total, or a key that does not match the data's pattern, then read with nrows. Keep the total for checking your sums.

How do I keep the footnotes from an Excel sheet? Read the cells below the last data row with openpyxl and store them, for example as a dictionary of source and notes, alongside the data.

How do I clean numbers that have footnote markers like an asterisk? Strip the marker, remove thousands separators and convert to a number, and record whether the marker was present in a separate boolean column.

Related

• Up one level: Extracting Data from Messy Excel Sheets — getting data out of awkward layouts.
• Find the Header Row in an Excel Sheet with a Shifting Layout — the top edge of the same problem.
• Read Multiple Tables from One Excel Sheet — when the next block is another table.
• Convert Currency Strings to Numbers with pandas — more number cleaning.