Read Multiple Tables from One Excel Sheet

A summary sheet that a person built by hand rarely holds one table. It holds a title, a regional breakdown, a gap, a product table underneath, and perhaps a quota list off to the right — all on the same worksheet. pd.read_excel() has no idea where one ends and the next begins; it treats the whole used range as a single grid and hands back a DataFrame full of NaN and misplaced headers. This guide, part of Extracting Data from Messy Excel Sheets, shows three reliable ways to get each table out on its own.

Prerequisites

pip install pandas openpyxl

The examples read a workbook called summary.xlsx shaped like the diagram above. This snippet builds it, so every block on the page can be run as written:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Summary"
ws.append(["Regional sales — March"])
ws.append([])
ws.append(["Region", "Revenue", "Deals"])
for row in (["North", 120500, 14], ["South", 98200, 11], ["West", 143900, 17]):
    ws.append(row)
ws.append([])
ws.append([])
ws.append(["Product", "Units"])
ws.append(["Widget", 420])
ws.append(["Gadget", 310])
for ref, value in {"F3": "Rep", "G3": "Quota", "F4": "Ana", "G4": 50000,
                   "F5": "Ben", "G5": 45000}.items():
    ws[ref] = value
wb.save("summary.xlsx")

Step 1 — Read the sheet without a header

The first move is always the same: stop pandas guessing. header=None returns every cell exactly where it sits, with integer row and column labels that map straight back to the worksheet (row label 0 is Excel row 1, column label 0 is column A).

import pandas as pd

raw = pd.read_excel("summary.xlsx", sheet_name="Summary", header=None)
print(raw.shape)          # (11, 7)
print(raw.iloc[:6, :3])

Everything that follows is plain DataFrame slicing on raw. Nothing is re-read from disk, so you can try the split several ways without paying for the parse again. If the worksheet is large, add dtype=object so pandas does not coerce a column that mixes a header string with numbers below it — though for small summary sheets the default is fine because each block is re-typed at the end.

Step 2 — Split stacked tables on blank rows

When tables are stacked vertically, a row where every cell is empty marks the gap. A cumulative sum over that boolean gives each block its own number, and groupby hands the blocks back one at a time.

def split_blocks(raw: pd.DataFrame, min_rows: int = 2) -> list[pd.DataFrame]:
    """Split a header=None frame into the tables separated by blank rows."""
    blank = raw.isna().all(axis=1)
    block_id = blank.cumsum()
    tables = []
    for _, block in raw[~blank].groupby(block_id[~blank]):
        block = block.dropna(axis=1, how="all")
        if len(block) < min_rows:
            continue                      # a title line, not a table
        header, body = block.iloc[0], block.iloc[1:]
        table = (
            body.set_axis(header.astype(str).str.strip(), axis=1)
                .rename_axis(None, axis=1)
                .reset_index(drop=True)
        )
        tables.append(table)
    return tables

left = raw.iloc[:, :3]
for table in split_blocks(left):
    print(table, end="\n\n")

Three details carry the weight here. dropna(axis=1, how="all") trims the empty columns that belong to a different table, so the product block comes back two columns wide rather than three. min_rows=2 throws away single-line blocks such as the title in row 1, which would otherwise become a table with a header and no body. And rename_axis(None, axis=1) removes the leftover row number that set_axis would otherwise print above the column names.

Step 3 — Handle tables that sit side by side

The rep quota table in columns F:G shares rows with the region table, so a blank-row split alone merges them. Split on blank columns first, then split each vertical strip on blank rows. The logic is the same cumulative-sum trick turned ninety degrees.

def split_columns(raw: pd.DataFrame) -> list[pd.DataFrame]:
    blank_col = raw.isna().all(axis=0)
    col_id = blank_col.cumsum()
    return [raw.loc[:, ~blank_col & (col_id == g)] for g in col_id[~blank_col].unique()]

tables = []
for strip in split_columns(raw):
    tables.extend(split_blocks(strip))

for table in tables:
    print(table.columns.tolist(), len(table))
# ['Region', 'Revenue', 'Deals'] 3
# ['Product', 'Units'] 2
# ['Rep', 'Quota'] 2

This breaks down in exactly one situation: a gap column that is not fully empty, because a note or a stray total was typed into it further down the sheet. When that happens the strips never separate, and the anchor approach in the next step is the more robust choice.

Step 4 — Find a table by its header label instead

Blank-line splitting depends on the layout staying tidy. An anchor search depends only on a header label existing somewhere on the sheet, which is usually the one thing the person maintaining the file never changes. Find the cell, then extend right and down until the first empty cell.

import numpy as np

def table_at_anchor(raw: pd.DataFrame, label: str) -> pd.DataFrame:
    hits = np.argwhere(raw.eq(label).to_numpy())
    if not len(hits):
        raise LookupError(f"no cell contains {label!r}")
    r, c = hits[0]
    region = raw.iloc[r:, c:]
    first_col = region.iloc[:, 0].isna().to_numpy()
    height = first_col.argmax() if first_col.any() else len(region)
    header_row = region.iloc[0].isna().to_numpy()
    width = header_row.argmax() if header_row.any() else region.shape[1]
    block = region.iloc[:height, :width]
    return block.iloc[1:].set_axis(block.iloc[0], axis=1).rename_axis(None, axis=1).reset_index(drop=True)

sales = table_at_anchor(raw, "Region")
quota = table_at_anchor(raw, "Rep")
products = table_at_anchor(raw, "Product")
print(sales, quota, products, sep="\n\n")

The height comes from the first column only, which is deliberate: a key column like Region is never blank inside a table, while a numeric column might legitimately have gaps. If your anchor column can be empty mid-table, pass a different column index to measure the height from.

Step 5 — Read named Excel tables directly

If whoever built the workbook used Insert → Table, the ranges are already recorded in the file and there is nothing to infer. openpyxl exposes them on each worksheet through ws.tables, which maps the table name to its A1:C4-style reference. Note that this requires a normal (not read_only) load, because read-only worksheets do not parse table parts.

from openpyxl import load_workbook

def read_named_tables(path: str) -> dict[str, pd.DataFrame]:
    wb = load_workbook(path, data_only=True)
    out = {}
    for ws in wb.worksheets:
        for name, ref in ws.tables.items():
            rows = [[cell.value for cell in row] for row in ws[ref]]
            out[name] = pd.DataFrame(rows[1:], columns=rows[0])
    return out

tables = read_named_tables("summary.xlsx")
print(list(tables))       # [] for this file — no Insert → Table was used

data_only=True returns the cached results of any formulas inside the table rather than the formula strings. The full treatment of this route, including totals rows and header options, is in Read an Excel Table by Name into pandas.

Step 6 — Restore the types

Because the sheet was read with header=None, each column initially mixed a header string with the values under it, and pandas typed it accordingly. Once the header has been promoted the column holds only data, so a single conversion puts the numbers back.

sales = sales.astype({"Revenue": "int64", "Deals": "int64"})
quota = quota.assign(Quota=pd.to_numeric(quota["Quota"], errors="coerce"))
print(sales.dtypes)

Use astype when you are sure the column is clean and want a loud failure if it is not, and pd.to_numeric(..., errors="coerce") when a stray "n/a" should become NaN instead. The second is the safer default for a hand-maintained sheet; Convert Excel Text Columns to Numbers with pandas walks through the cases where it matters.

Common pitfalls

A cell that looks empty but is not is the most common reason a split fails. Excel keeps a cell alive once it has been formatted, and a cell containing a single space is indistinguishable on screen from an empty one. Normalising whitespace to NaN before you compute blank removes the whole class of problem.

Performance and scale notes

Everything here happens in memory after a single read, so the cost is the parse, not the splitting. A summary sheet of a few hundred cells takes milliseconds either way. For a large worksheet, pass usecols="A:G" and nrows to read_excel so pandas never parses the thousands of unrelated rows below the area you care about, or switch the engine to calamine as described in Speed Up pandas Excel Reads with the calamine Engine.

If the same workbook is processed on every run, read the sheet once, split it, and write each table to its own Parquet or CSV file. Downstream steps then load a clean, typed table directly rather than repeating the inference.

Conclusion

Read the sheet with header=None, then decide how to find the edges. Blank rows and columns work when the layout is tidy; an anchor label works when the blocks move around but their headers stay put; and a named Excel table, when one exists, removes the guesswork entirely. Whichever route you take, re-type each table after its header is promoted, and normalise whitespace first so that invisible content never glues two tables together.

Frequently asked questions

Can read_excel read a specific range like B3:D6 directly? Not by cell reference, but usecols="B:D" with skiprows=2 and nrows=4 selects the same rectangle. That works for a fixed layout; the splitting approaches on this page are for layouts that shift.

How do I know how many tables a sheet contains? Run the blank-row and blank-column split and count the blocks it returns. If the count changes between files, log it — a new or missing block usually means the workbook layout has changed.

What if the tables have no blank row between them? Then the only boundary is the header itself. Use the anchor search, giving the second table's header label explicitly, and cut the first table at the row where that label appears.

Does this work with .xls and .ods files? Yes. Everything after the read is pandas slicing, so only the engine changes: xlrd for .xls and odf for .ods. The named-table route needs openpyxl and therefore .xlsx or .xlsm.

Related

• Up one level: Extracting Data from Messy Excel Sheets — the full set of techniques for human-shaped worksheets.
• Find the Header Row in an Excel Sheet with a Shifting Layout — the single-table version of the anchor search.
• Read Excel with Multi-Row Headers in pandas — when each block's header spans two rows.
• Skip Rows and Set the Header When Reading Excel with pandas — the fixed-layout alternative.
• Remove Blank Rows From Excel With Pandas — defining blank when whitespace is involved.