Read a Cell Range Like B3:F20 into a pandas DataFrame

Real report sheets rarely start at A1. There is a title in the first row, a notes box off to the right, a total row under the data, maybe an appendix further down — and the table you want is the block from B3 to F7. pd.read_excel reads the whole used area of the sheet by default, so all of that comes along with it. This page, part of Reading Excel Files with pandas, shows two precise ways to read exactly one rectangular range, how to translate an A1 address into pandas arguments without off-by-one errors, and a helper that makes it a one-liner.

Prerequisites

pip install pandas openpyxl

The examples read sales_report.xlsx, laid out as in the diagram: a title in A1, a table from B3 to F7, a total row at row 9, an appendix from row 12, and a note in H3.

Step 1 — See the problem with a plain read

import pandas as pd

print(pd.read_excel("sales_report.xlsx").head(3))

The title becomes the column header, the notes cell adds a column, and the total and appendix rows come along at the bottom. Cleaning that up afterwards is possible but fragile; reading only the block you want is cleaner.

Step 2 — Translate the address into read_excel arguments

read_excel has three arguments that together select a rectangle: usecols picks columns, skiprows skips rows above the header, and nrows limits how many data rows are read after it. For a block whose first row is a header:

table = pd.read_excel(
    "sales_report.xlsx",
    sheet_name="Report",
    usecols="B:F",     # columns B to F
    skiprows=2,        # skip rows 1-2, so row 3 is the header
    nrows=4,           # data rows 4-7
)
print(table)

The arithmetic is where mistakes happen, so make it explicit. For a range whose top-left cell is on row top and bottom row is bottom, with the header on the first row:

• skiprows = top - 1 — the number of rows above the header;
• nrows = bottom - top — data rows only, because the header row is not counted.

For B3:F7 that gives skiprows=2 and nrows=4. A common slip is nrows=5, which quietly pulls in row 8 — blank here, but in many sheets the first row of a total or of the next table.

Step 3 — Or read the range with openpyxl

When you have the address as a string, openpyxl can take it directly. Indexing a worksheet with "B3:F7" returns a tuple of rows, each a tuple of cells; turn the first row into the header and the rest into data:

from openpyxl import load_workbook

wb = load_workbook("sales_report.xlsx", data_only=True)
ws = wb["Report"]
cells = ws["B3:F7"]
values = [[c.value for c in row] for row in cells]
table = pd.DataFrame(values[1:], columns=values[0])
print(table)

iter_rows does the same with numbers instead of letters and can return values directly, which avoids building cell objects you do not need:

rows = list(ws.iter_rows(min_row=3, max_row=7, min_col=2, max_col=6, values_only=True))
print(pd.DataFrame(rows[1:], columns=rows[0]).equals(table))

data_only=True matters for any range containing formulas. Without it, openpyxl returns the formula text — '=SUM(D4:D7)' — rather than the number. With it, you get the value that was cached when a spreadsheet application last saved the file. A workbook written by openpyxl itself has no cached values, so its formula cells read as None; Read Formula Results with openpyxl data_only explains when to recalculate first.

Step 4 — Wrap it in a read_range helper

Most scripts that need a range get the address from configuration or from a colleague: "the numbers are in B3:F7 on the Report tab". A helper that accepts exactly that is worth having. openpyxl's range_boundaries does the parsing, and the function supports ranges with or without a header row:

from openpyxl.utils.cell import range_boundaries

def read_range(path, sheet, address, header=True):
    min_col, min_row, max_col, max_row = range_boundaries(address)
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        rows = list(wb[sheet].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 header:
        return pd.DataFrame(rows[1:], columns=rows[0])
    return pd.DataFrame(rows)

print(read_range("sales_report.xlsx", "Report", "B3:F7"))
print(read_range("sales_report.xlsx", "Report", "B13:C13", header=False))

Opening in read-only mode keeps memory flat even when the sheet is large, and the try/finally closes the file handle, which read-only workbooks hold open until closed. The second call reads a headerless block — the appendix row — as positional columns 0 and 1.

Step 5 — Handle a range whose size changes

A fixed address is right when the layout is fixed. When the table grows each month, hard-coding F7 either misses new rows or, if you pad it to F200, reads the total row and the appendix. Read from the top-left corner to the first empty row instead:

def read_block(path, sheet, top_left, width):
    min_col, min_row, _, _ = range_boundaries(f"{top_left}:{top_left}")
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        rows = []
        for row in wb[sheet].iter_rows(min_row=min_row, min_col=min_col,
                                       max_col=min_col + width - 1, values_only=True):
            if all(v is None for v in row):
                break
            rows.append(row)
    finally:
        wb.close()
    return pd.DataFrame(rows[1:], columns=rows[0])

print(read_block("sales_report.xlsx", "Report", "B3", width=5))

Stopping at the first completely blank row works for the common layout of a table followed by a gap. If the first column can be blank inside the data, test a key column instead of the whole row. When even the header row moves, search for a known label first; Find the Header Row in an Excel Sheet with a Shifting Layout shows how.

Read several ranges in one pass

Report sheets often hold more than one block you need — the main table plus a headcount appendix, say. Open the workbook once and read every range from it, rather than reopening the file for each:

RANGES = {"sales": ("Report", "B3:F7", True), "headcount": ("Report", "B13:C13", False)}

wb = load_workbook("sales_report.xlsx", read_only=True, data_only=True)
frames = {}
try:
    for key, (sheet, address, has_header) in RANGES.items():
        min_col, min_row, max_col, max_row = range_boundaries(address)
        rows = list(wb[sheet].iter_rows(min_row=min_row, max_row=max_row,
                                        min_col=min_col, max_col=max_col, values_only=True))
        frames[key] = pd.DataFrame(rows[1:], columns=rows[0]) if has_header else pd.DataFrame(rows)
finally:
    wb.close()
print({k: v.shape for k, v in frames.items()})

Keeping the addresses in one dictionary — or in the job's configuration file — also documents the workbook's layout in a single place, so the next change to the sheet means editing one line rather than hunting through the code.

Prefer names over addresses when you can

Addresses break when someone inserts a row above the table. If you have any influence over the workbook, ask for the block to be an Excel table (Insert → Table) or a named range. Both move with the data, and both can be read by name — Read an Excel Table by Name into pandas covers tables, and a defined name's destinations give you the same range address that read_range accepts.

Common pitfalls

• Off-by-one nrows. It counts data rows, not including the header. Compute it as last row minus header row.
• skiprows as a row number. skiprows=3 skips three rows, making row 4 the header. It is a count, not an address.
• Forgetting data_only=True. openpyxl returns formula strings otherwise.
• Reading a range that includes a total row. Totals are data to pandas; either stop above them or drop them explicitly, as shown in Remove Subtotal and Total Rows from an Excel Export.
• Leaving read-only workbooks open. Close them, or the file stays locked on Windows.

Performance notes

read_excel still parses the sheet's rows up to the ones it needs, so on very large sheets nrows saves memory but not all the reading time. openpyxl in read-only mode with iter_rows bounds streams only as far as max_row and then stops, which makes it the faster choice for a small block near the top of a big sheet. For many ranges from the same file, open the workbook once and read each range from the open sheet rather than calling read_excel repeatedly.

Conclusion

To read a block such as B3:F7, either translate the address into usecols="B:F", skiprows=top - 1 and nrows=bottom - top for read_excel, or pass the A1 string to openpyxl and build the DataFrame from its rows with data_only=True. Wrap the openpyxl version in a read_range helper for addresses that come from configuration, read to the first blank row when the block grows, and move to tables or named ranges when the layout keeps changing.

Frequently asked questions

How do I translate B3:F20 into read_excel arguments? usecols="B:F" picks the columns. skiprows=2 skips rows 1 and 2 so row 3 becomes the header. nrows=17 reads rows 4 to 20 — nrows counts data rows after the header, so it is the last row minus the header row.

Is openpyxl or pandas better for reading a range? pandas is shorter when the range starts with a header row. openpyxl is better when you already have the address as an A1 string, need cell values without a header, or want to read several ranges from one open workbook.

Why do I get formulas instead of numbers with openpyxl? openpyxl returns formula text unless the workbook is opened with data_only=True. Even then it returns the value cached when the file was last saved by a spreadsheet application; a file written by openpyxl has no cached values.

What if the range moves from month to month? Do not hard-code the address. Find the header row by searching for a known label, read to the first blank row, or have the owner of the sheet turn the block into an Excel table and read it by name.

Related

• Up one level: Reading Excel Files with pandas — the full set of read_excel options.
• Read Specific Columns From Excel With Pandas — usecols in every form.
• Read Only Some Rows of an Excel File with pandas — skiprows and nrows in depth.
• Read Multiple Tables from One Excel Sheet — when a sheet holds several blocks.