Python Doc & Data Automation

Read Specific Cell Ranges from Excel

The monthly template is a designed document, not a data file: a logo and title in rows 1 to 4, a parameters block in B6:C9, the data table in A12:H120, notes underneath, and a summary block off to the right in J12:L20. pd.read_excel(path) returns a frame with Unnamed: 1 columns, the title in the header row, the parameters mixed into the data and everything below the table appended as rows of NaN.

>>> pd.read_excel("in/march-template.xlsx").shape
(142, 12)          # expected 108 data rows and 8 columns

Root Cause

pandas reads a rectangle: it starts at the first row, treats it as the header, and continues to the last row that contains anything anywhere on the sheet. A template with several blocks on one sheet does not fit that model, so the reader has to be told which rectangle to take. There are four ways to say it, and they differ in how well they survive the sheet changing: fixed row and column offsets, an A1-style range, a named range, and an Excel table. Fixed offsets are the easiest to write and the first to break when someone inserts a row above the table — which is exactly what happens when a template is used by people rather than machines.

Minimal Diagnostic

Map the sheet's occupied blocks, named ranges and tables, so the right addressing method is obvious.

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

SOURCE = Path("in/march-template.xlsx")

def sheet_map(path: Path, sheet: str | None = None, max_probe: int = 200) -> None:
    wb = load_workbook(path, data_only=True)
    try:
        ws = wb[sheet] if sheet else wb.worksheets[0]
        print(f"sheet {ws.title!r}: dimensions {ws.dimensions}, max_row={ws.max_row}, max_col={ws.max_column}")
        runs, current = [], None
        for row in range(1, min(ws.max_row, max_probe) + 1):
            filled = [c for c in range(1, ws.max_column + 1) if ws.cell(row, c).value not in (None, "")]
            if filled:
                span = (min(filled), max(filled))
                if current and current[2] == span:
                    current = (current[0], row, span)
                else:
                    if current:
                        runs.append(current)
                    current = (row, row, span)
            elif current:
                runs.append(current)
                current = None
        if current:
            runs.append(current)
        for start, end, (c0, c1) in runs:
            print(f"  block rows {start}-{end}, columns {get_column_letter(c0)}-{get_column_letter(c1)}"
                  f"  first cell: {ws.cell(start, c0).value!r}")
        print("  named ranges:", {name: str(dn.value) for name, dn in wb.defined_names.items()} or "none")
        print("  tables:", {t.name: t.ref for t in ws.tables.values()} or "none")
    finally:
        wb.close()

if __name__ == "__main__":
    sheet_map(SOURCE)

sheet 'March': dimensions A1:L142, max_row=142, max_col=12
  block rows 1-4, columns A-D  first cell: 'Monthly sales report'
  block rows 6-9, columns B-C  first cell: 'Region'
  block rows 12-120, columns A-H  first cell: 'Order ID'
  block rows 12-20, columns J-L  first cell: 'Summary'
  block rows 124-142, columns A-B  first cell: 'Notes:'
  named ranges: {'SalesData': "March!$A$12:$H$120", 'Params': "March!$B$6:$C$9"}
  tables: {'tblSales': 'A12:H120'}

The workbook already names the block two ways — a named range and an Excel table — either of which moves automatically when rows are inserted.

Blocks on one template sheet The sheet holds a title block in rows 1 to 4, a parameters block in B6 to C9, the data table in A12 to H120 which is also an Excel table named tblSales, a summary block in J12 to L20 beside it, and a notes block from row 124 down. Reading the sheet without a range returns all of it as one rectangle with unnamed columns and blank rows. March sheet, A1:L142 1 1 Title rows 1-4 2 2 Params B6:C9 (named 'Params') 3 3 Data table A12:H120, table tblSales 4 4 Summary J12:L20 5 5 Notes from row 124

Fix: Address the Block by Name, Not by Offset

Read the table through its Excel table definition or named range, so inserted rows do not break the loader. Changed lines carry comments.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import range_boundaries

SOURCE = Path("in/march-template.xlsx")

def read_table(path: Path, table_name: str) -> pd.DataFrame:
    wb = load_workbook(path, data_only=True)                       # changed: values, not formulas
    try:
        for ws in wb.worksheets:
            if table_name in ws.tables:
                ref = ws.tables[table_name].ref                     # changed: table ref follows insertions
                min_col, min_row, max_col, max_row = range_boundaries(ref)
                rows = ws.iter_rows(min_row=min_row, max_row=max_row,
                                    min_col=min_col, max_col=max_col, values_only=True)
                header = [str(h).strip() if h is not None else f"col_{i}"
                          for i, h in enumerate(next(rows))]
                return pd.DataFrame(rows, columns=header)
        raise KeyError(f"table {table_name!r} not found in {path.name}")
    finally:
        wb.close()

def read_named_range(path: Path, name: str) -> pd.DataFrame:
    wb = load_workbook(path, data_only=True)
    try:
        if name not in wb.defined_names:
            raise KeyError(f"named range {name!r} not found")
        destinations = list(wb.defined_names[name].destinations)    # changed: (sheet, ref) pairs
        frames = []
        for sheet_name, ref in destinations:
            ws = wb[sheet_name]
            min_col, min_row, max_col, max_row = range_boundaries(ref.replace("$", ""))
            rows = ws.iter_rows(min_row=min_row, max_row=max_row,
                                min_col=min_col, max_col=max_col, values_only=True)
            header = [str(h).strip() if h is not None else f"col_{i}" for i, h in enumerate(next(rows))]
            frames.append(pd.DataFrame(rows, columns=header))
        return pd.concat(frames, ignore_index=True) if len(frames) > 1 else frames[0]
    finally:
        wb.close()

if __name__ == "__main__":
    sales = read_table(SOURCE, "tblSales")
    print(sales.shape, list(sales.columns))
    print(read_named_range(SOURCE, "Params").to_string(index=False))

(108, 8) ['Order ID', 'Order Date', 'Customer', 'Region', 'Qty', 'Unit Price', 'Amount', 'Status']
 Region  Value
   EMEA  March 2026
Currency        EUR

Reading through the table definition is the version that survives real templates: Excel updates tblSales's reference whenever rows are inserted, deleted or appended, so the loader picks up the new bounds without a code change. data_only=True returns the values Excel last calculated rather than formula text — which is right for reading a report, and comes with the caveat covered in fix openpyxl data_only formula returns None.

Four ways to address a block Fixed skiprows and nrows offsets are quick to write but break when a row is inserted above the block. An A1 range string such as A12 to H120 is explicit but equally fragile. A named range is maintained by Excel and moves with insertions, and its definition is visible in the workbook's name manager. An Excel table also moves with insertions, grows when rows are appended at the bottom, and exposes column names. Method Survives inserted rows Grows with data Defined in skiprows / nrows no no code usecols='A:H' columns only no code Named range yes fixed size workbook Excel table yes yes workbook

Variant Fix 1: No Named Range or Table — Use Offsets Safely

Many templates have neither. pandas can take offsets directly, and the safest version derives them from a marker rather than hard-coding numbers:

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

def find_header_row(path: Path, sheet: str, first_header: str = "Order ID", probe: int = 40) -> int:
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        ws = wb[sheet]
        for row_number, row in enumerate(ws.iter_rows(min_row=1, max_row=probe, values_only=True), start=1):
            if row and str(row[0]).strip() == first_header:
                return row_number                              # 1-based row of the header
        raise ValueError(f"header cell {first_header!r} not found in the first {probe} rows")
    finally:
        wb.close()

def read_block(path: Path, sheet: str, first_header: str = "Order ID", columns: str = "A:H") -> pd.DataFrame:
    header_row = find_header_row(path, sheet, first_header)
    frame = pd.read_excel(path, sheet_name=sheet, header=header_row - 1,   # pandas is 0-based
                          usecols=columns, dtype={"Order ID": "string"})
    return frame[frame["Order ID"].notna()]                     # stop at the first blank row below the table

Anchoring on the header text rather than a row number means a new logo row or an extra parameter line does not shift the loader. Truncating at the first blank Order ID keeps the notes block out of the data. Both rules should be stated in the template's documentation, so whoever edits it knows what the automation depends on.

Variant Fix 2: Several Blocks, Including Ones Beside the Table

The summary block in J12:L20 sits on the same rows as the data. Read each block separately and keep them separate — merging them into one frame would be wrong:

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import range_boundaries

def read_range(path: Path, sheet: str, ref: str, header: bool = True) -> pd.DataFrame:
    wb = load_workbook(path, data_only=True, read_only=True)
    try:
        ws = wb[sheet]
        min_col, min_row, max_col, max_row = range_boundaries(ref)
        rows = list(ws.iter_rows(min_row=min_row, max_row=max_row,
                                 min_col=min_col, max_col=max_col, values_only=True))
    finally:
        wb.close()
    if not rows:
        return pd.DataFrame()
    if header:
        head, *body = rows
        columns = [str(h).strip() if h is not None else f"col_{i}" for i, h in enumerate(head)]
        return pd.DataFrame(body, columns=columns)
    return pd.DataFrame(rows)

BLOCKS = {"data": "A12:H120", "summary": "J12:L20", "params": "B6:C9"}

if __name__ == "__main__":
    blocks = {name: read_range(Path("in/march-template.xlsx"), "March", ref) for name, ref in BLOCKS.items()}
    for name, frame in blocks.items():
        print(name, frame.shape)

Using read_only=True here matters for large templates: it streams rows instead of building the whole sheet in memory, at the cost of losing formatting and some metadata, which a value reader does not need anyway.

Choosing how to address the block First check whether the sheet defines an Excel table covering the block, because its reference follows insertions and appended rows. If not, check for a named range, which also moves with insertions. If neither exists, anchor on the header cell's text and read from there, stopping at the first blank key cell. Only when nothing else is available use fixed skiprows and nrows, and add a check that the expected header appears where predicted. Excel table ws.tables[name].ref — follows inserts and appends Named range wb.defined_names[name].destinations Header anchor find the row whose first cell is 'Order ID' Fixed offsets skiprows and nrows, plus a header assertion

Asking Template Owners for a Table

The most durable fix is a five-minute change in the workbook rather than code: select the block in Excel, press Ctrl+T to make it a table, and give it a name. Everything downstream then addresses the data by name. When requesting that change, a short script that shows what the automation currently guesses is persuasive:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

def template_readiness(path: Path) -> dict:
    wb = load_workbook(path, read_only=True)
    try:
        tables = {t for ws in wb.worksheets for t in ws.tables}
        names = set(wb.defined_names)
    finally:
        wb.close()
    return {"file": path.name, "tables": sorted(tables), "named_ranges": sorted(names),
            "addressable": bool(tables or names)}

if __name__ == "__main__":
    for path in sorted(Path("in/templates").glob("*.xlsx")):
        print(template_readiness(path))

Run it across the template folder and share the list of files with no table and no named range. Those are the files whose loaders will break the next time somebody inserts a row — a concrete, checkable request rather than a general plea for tidier spreadsheets.

Verification

Assert the block's shape and headers, and that nothing outside the intended range leaked in.

# pip install "pandas>=2.2"
import pandas as pd

EXPECTED_COLUMNS = ["Order ID", "Order Date", "Customer", "Region", "Qty", "Unit Price", "Amount", "Status"]

def verify_block(frame: pd.DataFrame, min_rows: int = 1, max_rows: int = 5000) -> None:
    assert list(frame.columns) == EXPECTED_COLUMNS, f"columns differ: {list(frame.columns)}"
    assert min_rows <= len(frame) <= max_rows, f"{len(frame)} rows outside the expected range"
    assert frame["Order ID"].notna().all(), "blank key rows leaked in (range too tall?)"
    assert not frame.columns.duplicated().any(), "duplicate headers: the header row may be wrong"
    notes_leak = frame.apply(lambda col: col.astype("string").str.contains("Notes:", na=False)).any().any()
    assert not notes_leak, "text from the notes block leaked into the data"
    print(f"block verified: {len(frame)} rows x {len(frame.columns)} columns")

Checking for content from the neighbouring blocks — the notes text here — is what catches a range that is one block too tall after a template edit, which row-count bounds alone would let through.

FAQ

Can pandas read a named range directly? No. Resolve the range with openpyxl, then build the frame, as above.

What does usecols="A:H" do about the header? It selects columns by letter regardless of header names, which is useful when headers are missing or duplicated. Combine it with header= to point at the right row.

Why is max_row larger than my data? Excel remembers formatting and previously used cells. Anchor on content rather than trusting max_row.

Do Excel tables survive round-tripping through pandas? Only if you write with openpyxl into the original workbook. Writing a new file with pandas loses the table definition unless you recreate it, as in add Excel tables and named ranges with openpyxl.

Part of Reading Excel Files with Python.