Read Only Some Rows of an Excel File with pandas

Sometimes you do not want the whole sheet. You want a preview of the first twenty rows to check the layout, the current week out of a year of history, a sample to test a pipeline against, or just the last batch appended to a log workbook. pandas.read_excel has three parameters for this — nrows, skiprows and skipfooter — and they combine to cut almost any horizontal slice out of a sheet. This guide, part of Reading Excel Files with pandas, shows each slice, the counting rules that trip people up, and which options genuinely make a big file faster to read rather than just smaller once read.

Prerequisites

pip install pandas openpyxl python-calamine

The examples build their own workbook so every block runs as written. It has 60,000 orders — big enough for the timing differences to show:

import numpy as np
import pandas as pd

rng = np.random.default_rng(7)
n = 60_000
orders = pd.DataFrame({
    "order_id": np.arange(1, n + 1),
    "region": rng.choice(["North", "South", "East", "West"], n),
    "amount": np.round(rng.random(n) * 1000, 2),
})
orders.to_excel("orders.xlsx", index=False)

Step 1 — The first rows: nrows

nrows is the one you will use most. It limits the number of data rows returned; the header is read in addition, so nrows=5 gives five rows and correct column names.

head = pd.read_excel("orders.xlsx", nrows=5)
print(head)
print(len(head))  # 5

That is the right way to preview an unfamiliar workbook: confirm the header landed where you expected, check the dtypes pandas inferred, and only then read the whole thing. A preview also tells you quickly when the real header is not on the first row — in which case combine nrows with header=, covered in Skip Rows and Set the Header When Reading Excel with pandas.

Step 2 — A window in the middle: skiprows plus nrows

To read rows 1,001 to 1,500, skip everything between the header and your window, then take the window with nrows. skiprows accepts a list or range of zero-based file positions, and position 0 is the header — which is why the range below starts at 1: the header stays, the data rows before the window go.

start, size = 1_000, 500               # data rows 1,001 to 1,500
window = pd.read_excel(
    "orders.xlsx",
    skiprows=range(1, start + 1),      # keep file row 0, the header
    nrows=size,
)
print(window["order_id"].iloc[[0, -1]].tolist())  # [1001, 1500]

If you pass skiprows=1000 as a plain integer instead, pandas skips the first thousand file rows including the header, and the first surviving data row becomes the column names. That is correct when the sheet has a title block above the header and wrong here, so reach for the integer form only when you mean "the table starts lower down".

Step 3 — Every nth row or a sample: a callable

skiprows also takes a function. pandas calls it with each file row index and skips the row when it returns True. Keep row 0 so the header survives, and you can express any pattern:

every_tenth = pd.read_excel(
    "orders.xlsx",
    skiprows=lambda i: i > 0 and i % 10 != 0,
)
print(len(every_tenth))                 # 6000
print(every_tenth["order_id"].head(3).tolist())  # [10, 20, 30]

A random sample works the same way. Decide the kept positions first, so the choice is reproducible, and let the callable test membership:

keep = set(rng.choice(np.arange(1, n + 1), size=1_000, replace=False).tolist())
sample = pd.read_excel("orders.xlsx", skiprows=lambda i: i > 0 and i not in keep)
print(len(sample))  # 1000

Sampling this way is useful for developing a transformation against realistic data without waiting for the full read on every run. It does not make the read itself faster, though: pandas still walks every row to ask the callable about it.

Step 4 — Rows at the bottom: skipfooter and the tail

Exports from accounting and ERP systems often end with a trailer — "Report generated 06:00", a grand-total row, a disclaimer. skipfooter drops a fixed number of rows from the bottom:

with_trailer = pd.concat(
    [orders.head(8), pd.DataFrame({"order_id": ["Total"], "amount": [orders["amount"].head(8).sum()]})],
    ignore_index=True,
)
with_trailer.to_excel("with_trailer.xlsx", index=False)

clean = pd.read_excel("with_trailer.xlsx", skipfooter=1)
print(clean["order_id"].tolist())  # 1 … 8, no "Total"

Reading the last N rows is different, because an xlsx file stores rows top to bottom and there is no way to jump to the end. Count the rows cheaply first with openpyxl's read-only mode, then skip everything before the tail:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx", read_only=True)
total_rows = wb.active.max_row          # includes the header row
wb.close()

tail_size = 100
tail = pd.read_excel("orders.xlsx", skiprows=range(1, total_rows - tail_size))
print(tail["order_id"].iloc[[0, -1]].tolist())  # [59901, 60000]

In read-only mode max_row comes from the dimension record the writing program stored in the file. Excel and pandas write it correctly; a few third-party exporters write a wrong or missing one. If the count looks implausible, call ws.reset_dimensions() and iterate to count, or read the whole sheet once — for a logging workbook that grows by appending, a trailing slice is usually cheaper to keep in a separate "latest" sheet anyway. For pulling out rows based on hidden or filtered state rather than position, see Skip Hidden and Filtered Rows When Reading Excel.

Which slices are actually faster

A slice is always smaller once loaded. Whether it is faster depends on the engine and on where the slice sits. With the default openpyxl engine, pandas stops parsing once nrows rows have been collected, so reading from the top is close to free. Skipping rows still parses them — the parser has to read a row to know it is a row — so a window near the end costs almost as much as the whole sheet.

You can reproduce the comparison on your own machine:

import time

def timed(**kwargs):
    t0 = time.perf_counter()
    frame = pd.read_excel("orders.xlsx", **kwargs)
    return round(time.perf_counter() - t0, 3), len(frame)

print("full      ", timed())
print("first 100 ", timed(nrows=100))
print("last 100  ", timed(skiprows=range(1, total_rows - 100)))
print("calamine  ", timed(engine="calamine"))

The calamine engine, a Rust reader, loads the entire sheet before pandas sees it, so nrows saves memory but little time — yet its full read is several times faster than openpyxl's anyway. For previews of the top, openpyxl with nrows wins; for anything that needs the whole sheet or its tail, calamine wins. Both are covered in Speed Up pandas Excel Reads with the calamine Engine.

Common pitfalls

• Off-by-one windows. skiprows counts file rows from zero with the header at 0; Excel counts from one. Row 1,001 in Excel is file position 1,000. Print the first and last id of every window while you develop, as the examples above do.
• Integer skiprows eats the header. skiprows=5 removes file rows 0–4, header included. Use range(1, 6) to keep the header and drop five data rows.
• skipfooter with trailing blank rows. Blank rows at the bottom are usually trimmed by the reader before skipfooter applies, but formatted empty cells can count as rows. If the wrong rows disappear, read without it and drop the trailer by value instead.
• Slicing before deduplicating. A window of rows is not a window of records when a sheet has one record spread over several rows. Fix the structure first — Parse Records That Span Multiple Rows in an Excel Sheet shows how.

Performance and scale notes

For workbooks in the hundreds of thousands of rows, combine a row slice with usecols — fewer columns cut parsing time as well as memory, and the two options compose. When you repeatedly need different slices of the same large file, convert it once to Parquet and slice that instead: Parquet reads a row range or a column subset without parsing the rest, which no Excel reader can do. The trade-offs are covered in Working with Large Excel Files in Python.

Conclusion

nrows takes rows from the top and, with openpyxl, stops reading early. skiprows as a range starting at 1 plus nrows takes a window; as a callable it takes any pattern; as a plain integer it moves the header down. skipfooter trims trailers, and a quick read-only row count gives you the tail. Only the top slice saves parsing time, so when the rows you need live at the end of a very large sheet, change the file format or the engine rather than the parameters.

Frequently asked questions

Does nrows make read_excel faster? With the openpyxl engine, yes — it stops reading the sheet once it has the rows you asked for, so the first hundred rows of a huge sheet load almost instantly. The calamine engine loads the whole sheet first, so nrows mainly saves memory there.

Is the header row counted by nrows? No. nrows counts data rows after the header. nrows=10 returns ten rows plus the column names taken from the header row.

Are skiprows numbers zero-based? Yes, and they are positions in the file, header included. Row 1 in Excel is index 0, so skiprows=1, 2 drops the first two data rows under a header on Excel row 1.

How do I read the last 100 rows without loading the whole sheet? You cannot avoid touching the whole sheet, because xlsx stores rows in order. Get the row count cheaply with openpyxl in read-only mode, then pass skiprows=range(1, total - 100) to read_excel.

Can I filter rows by value while reading? No. read_excel has no row filter. Read the columns you need with usecols, then filter the DataFrame — or stream rows with openpyxl and keep only the ones that match.

Related

• Up one level: Reading Excel Files with pandas — every read_excel option in context.
• Read Specific Columns From Excel With Pandas — the column-wise counterpart to this guide.
• Read a Large Excel File in Chunks with pandas — process a whole sheet slice by slice.
• Skip Rows and Set the Header When Reading Excel with pandas — when the table starts lower down.