Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Skip Rows and Set the Header When Reading Excel with pandas

Read Excel files with title blocks, banners and multi-row headers using pandas — skiprows, header, nrows, usecols, and finding the header row automatically when it moves.

Excel files made by people rarely start with the data. There is a company banner, a title, an "as at" date, a blank row, then the actual column headings — and pandas, reading from row zero, dutifully names your columns Unnamed: 0 through Unnamed: 7. The fix is two arguments, but knowing which one to reach for, and what to do when the preamble changes height every month, is what makes an import robust. This guide covers both. It builds on the basics in Reading Excel Files with pandas.

Where the preamble ends and the header begins A sheet layout with six rows. Rows zero through two hold a company banner, a report title and an as-at date. Row three is blank. Row four holds the real column names Region, Units and Revenue. Row five onwards holds data. Passing header equals four tells pandas to take row four as the column names and start data at row five, discarding everything above. row what pandas does with it 0 ACME Corporation 1 Regional revenue report 2 As at 15 August 2026 3 (blank) 4 Region · Units · Revenue 5+ North · 412 · 5150.00 discarded — preamble read from row 0 and these become your column names instead header=4 → the column names data starts here automatically

Prerequisites

Bash
pip install pandas openpyxl

A file shaped like the ones that cause the problem:

Python
import pandas as pd

with pd.ExcelWriter("export.xlsx", engine="xlsxwriter") as writer:
    frame = pd.DataFrame({
        "Region": ["North", "South", "West"],
        "Units": [412, 388, 265],
        "Revenue": [5150.00, 4268.00, 3511.25],
    })
    frame.to_excel(writer, sheet_name="Report", index=False, startrow=4)

    sheet = writer.sheets["Report"]
    sheet.write(0, 0, "ACME Corporation")
    sheet.write(1, 0, "Regional revenue report")
    sheet.write(2, 0, "As at 15 August 2026")

Step 1 — Look before you parse

Never guess the layout. Read the top of the sheet with no header at all and print it:

Python
import pandas as pd

peek = pd.read_excel("export.xlsx", header=None, nrows=8)
print(peek.to_string())
#          0        1        2
# 0  ACME Corporation  NaN  NaN
# 1  Regional revenue report  NaN  NaN
# 2  As at 15 August 2026  NaN  NaN
# 3  NaN  NaN  NaN
# 4  Region  Units  Revenue
# 5  North  412  5150.0

header=None stops pandas promoting anything to column names, and nrows=8 keeps it cheap on a large file. The header is clearly row 4.

Step 2 — Set the header row

With the index known, one argument does the job:

Python
df = pd.read_excel("export.xlsx", header=4)
print(df.columns.tolist())     # ['Region', 'Units', 'Revenue']
print(len(df))                 # 3

Notice what you did not need: skiprows. Setting header=4 already tells pandas to ignore rows 0 through 3 and start data at row 5. Combining both is the most common source of confusion, because header is interpreted relative to what remains after skipping:

Python
# Equivalent to header=4 — the counting restarts after the skip.
df = pd.read_excel("export.xlsx", skiprows=4, header=0)

# NOT equivalent: skips 4 rows, then takes the 5th remaining row as header,
# which is the first data row. Almost never what you want.
df = pd.read_excel("export.xlsx", skiprows=4, header=4)

The rule to remember: use header= alone when the preamble is simply above the header. Reach for skiprows only when you need to discard rows that are not contiguous with the top, which the callable form handles.

GoalArgument
Header is on row 4, preamble aboveheader=4
Two stacked header rowsheader=[0, 1]
No header at all; supply namesheader=None, names=[...]
Drop scattered rows anywhereskiprows=lambda i: ...
Drop trailing total rowsskipfooter=2
Read only the first 500 data rowsnrows=500

Step 3 — Handle stacked headers

How a merged group header becomes Unnamed columns The top header row holds Q1 spanning three columns as a merged cell, so only the leftmost of the three carries the text and the other two are blank. Read as a MultiIndex, those blanks become Unnamed placeholders. Forward-filling the upper level spreads Q1 rightwards, after which joining the two levels produces Q1_Units, Q1_Revenue and Q1_Margin. row 0: the merged group header Q1 — merged across three columns Q2 — merged across three columns row 1: the detail header Units Revenue Margin Units Revenue Margin Q1 Unnamed Unnamed Q2 Unnamed Unnamed fill the upper level rightwards, then join → Q1_Units, Q1_Revenue, Q1_Margin, Q2_Units …

Exports from reporting tools often stack a group row above a detail row — Q1 spanning three columns, then Units, Revenue, Margin beneath. Pass a list and pandas builds a MultiIndex:

Python
import pandas as pd

df = pd.read_excel("quarterly.xlsx", header=[0, 1])
print(df.columns[:3].tolist())
# [('Q1', 'Units'), ('Q1', 'Revenue'), ('Q1', 'Margin')]

A MultiIndex is awkward to work with downstream, so flatten it immediately. Merged group cells leave Unnamed: fragments in the upper level, which need dropping as you join:

Python
def flatten(columns):
    """Join MultiIndex levels, ignoring the Unnamed fragments merges leave."""
    flat = []
    for parts in columns:
        keep = [
            str(p).strip() for p in parts
            if p is not None and not str(p).startswith("Unnamed:")
        ]
        flat.append("_".join(keep) if keep else "unnamed")
    return flat

df.columns = flatten(df.columns)
print(df.columns.tolist())
# ['Q1_Units', 'Q1_Revenue', 'Q1_Margin', 'Q2_Units', ...]

The Unnamed: filter is essential because Excel stores a merged cell's value only in its top-left cell — the rest read as blank, so a header spanning three columns produces one real name and two Unnamed: placeholders. The wider treatment of that behaviour is in handling merged cells when reading Excel with pandas.

Step 4 — Find the header row automatically

Hard-coding header=4 works until the month somebody adds a line to the title block. The durable answer is to search for the header by its content:

Detecting the header row instead of hard-coding it A two-pass read. The first pass reads the top twenty rows with header set to None, and scans each row's cell values for a required set of known column names such as Region and Revenue. The index of the first matching row becomes the header argument for a second, full read. This survives a title block that gains or loses a line between months, which a hard-coded index does not. pass 1 header=None nrows=20 — cheap scan each row does it contain Region and Revenue? pass 2: header=<that index> survives a title block that gains or loses a line raise a clear error when no row matches — a silent fallback hides a changed export
Python
import pandas as pd

def find_header_row(path, required, sheet_name=0, search=20):
    """Return the index of the first row containing all required column names."""
    wanted = {str(name).strip().lower() for name in required}

    preview = pd.read_excel(path, sheet_name=sheet_name,
                            header=None, nrows=search)

    for index, row in preview.iterrows():
        values = {str(v).strip().lower() for v in row if pd.notna(v)}
        if wanted <= values:
            return int(index)

    raise ValueError(
        f"No header row in the first {search} rows of {path} contains "
        f"{sorted(required)} — has the export format changed?"
    )

def read_report(path, required, sheet_name=0, **kwargs):
    header = find_header_row(path, required, sheet_name=sheet_name)
    return pd.read_excel(path, sheet_name=sheet_name, header=header, **kwargs)

df = read_report("export.xlsx", ["Region", "Units", "Revenue"])
print(df.head())

Raising when nothing matches is deliberate. A silent fallback to header=0 produces a frame full of Unnamed: columns that fails confusingly three steps later, whereas this error names the file and the columns it expected. That is the same principle behind validating Excel columns before import.

Step 5 — Drop trailing rows and pick columns

Exports often end with a blank line and a grand total. skipfooter removes them:

Python
df = pd.read_excel("export.xlsx", header=4, skipfooter=2)

Be aware of the cost: skipfooter forces pandas down a slower Python-level path, because it cannot know where the end is until it has read everything. On a large sheet it is faster to read normally and slice:

Python
df = pd.read_excel("export.xlsx", header=4)
df = df.iloc[:-2]                                   # drop the last two rows

# Better still, drop by content rather than position.
df = df[df["Region"].notna() & (df["Region"] != "Total")]

Dropping by content survives a month where the export has one trailing row instead of two, which position-based slicing does not.

Finally, read only the columns you need. It is faster and it removes a whole class of surprise from columns you never look at:

Python
df = pd.read_excel("export.xlsx", header=4, usecols=["Region", "Revenue"])
df = pd.read_excel("export.xlsx", header=4, usecols="A:C")      # by letter
df = pd.read_excel("export.xlsx", header=4, usecols=lambda c: not c.startswith("_"))

Common pitfalls and fixes

SymptomCauseFix
Columns named Unnamed: 0, Unnamed: 1Header index points at a blank rowPeek with header=None and set the right index.
Data missing its first rowskiprows and header both setUse header= alone; header counts after the skip.
Columns are tuplesMulti-row header read as a MultiIndexFlatten with a join, dropping Unnamed: parts.
Region column holds a Total rowFooter not removedFilter by content, not just skipfooter.
Works one month, breaks the nextPreamble height changedDetect the header row by its content.
Read is very slowskipfooter forces a Python parseRead fully and slice the frame instead.
ValueError: Passed header=4 but only 3 linesSheet shorter than expected, or wrong sheetCheck sheet_name and peek first.
Numbers read as textHeader row absorbed into the dataFix the header index; the dtype follows.

Performance and scale notes

skiprows and header do not save any reading. pandas still parses every row of the sheet — the arguments only decide what is kept. The argument that genuinely reduces work is usecols, which avoids materialising columns entirely, and nrows, which stops early.

For a wide export where you use six of sixty columns, the difference is substantial:

Python
import time
import pandas as pd

for label, kwargs in [
    ("everything", {}),
    ("six columns", {"usecols": ["Region", "Units", "Revenue",
                                 "Owner", "Status", "Updated"]}),
]:
    start = time.perf_counter()
    frame = pd.read_excel("wide_export.xlsx", header=4, **kwargs)
    print(f"{label:<14} {time.perf_counter() - start:6.2f}s  {frame.shape}")

Three habits follow. Detect the header once per file, not per sheet — the two-pass read costs an extra parse of twenty rows, which is negligible, but running it inside a loop over forty sheets is not. Cache the index when the sheets share a layout.

Avoid skipfooter on large files. It disables the fast path entirely. Filtering by content after a normal read is both faster and more robust.

Combine detection with chunked reading for very large sheets. Find the header from a cheap preview, then stream the body with the approach in reading large Excel files in chunks with pandas, so peak memory stays flat regardless of row count. And where the file arrives as a legacy format, convert it first — the parse cost dominates everything above, and converting .xls to .xlsx removes it permanently.

Conclusion

Reading an Excel export with a title block comes down to knowing that header= counts rows in the original file and does the skipping for you, while skiprows renumbers everything after it. Peek at the top with header=None before writing the real read. Flatten multi-row headers immediately and drop the Unnamed: fragments that merged cells leave. And when the preamble height is not stable — which, over enough months, it never is — detect the header row by looking for the column names you expect, and raise a clear error when they are not there.

Frequently asked questions

What is the difference between skiprows and header?skiprows discards rows before pandas looks at the file; header names which of the remaining rows holds the column names. Passing header=3 alone is usually enough, because pandas then treats rows 0 to 2 as ignorable preamble and starts data at row 4.

How do I read a file with two stacked header rows? Pass a list, for example header=[0, 1]. pandas builds a MultiIndex from both rows, which you can then flatten into single names by joining the levels with an underscore.

My export has a total row at the bottom — how do I drop it? Use skipfooter with the number of trailing rows to ignore. It requires a Python-level parse, so on very large files it is faster to read everything and slice the frame instead.

The number of preamble rows changes every month. What then? Do not hard-code it. Read the first twenty rows with header=None, find the row containing your known column names, and pass that index as header.

Why are my columns named Unnamed: 0, Unnamed: 1? pandas took a blank row as the header. Either the header index is wrong, or the real header sits below merged title cells. Read with header=None first and print the top rows to see where the names actually are.