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

Reading Excel Files with Pandas

Load Excel workbooks into pandas DataFrames: pick the engine, target sheets and columns, control dtypes, and read multi-sheet files — with runnable examples.

pandas.read_excel() turns a worksheet into a DataFrame, ready for filtering, aggregation, and export. Loading a workbook is the first move in almost every automation covered in Getting Started with Python Excel Automation, and this guide is the map of that one function: how the engine works, the handful of parameters you will use constantly, and how to handle multi-sheet workbooks. Every snippet runs in order against a sample file created below, so you can paste and follow along. For a slower, fully narrated walkthrough, see How to Read Excel with Pandas Step by Step.

The anatomy of pandas.read_excel A workbook sheet passes through the key read_excel parameters — sheet_name, usecols, dtype, and parse_dates — to produce a clean DataFrame. Input workbook .xlsx sheet read_excel() params sheet_name usecols dtype parse_dates Output DataFrame clean & typed pick the sheet, the columns, the types — read only what you need

Install pandas and an engine

pandas does not parse Excel itself — it hands the file to an engine. openpyxl reads modern .xlsx files and is the one you need here. (Legacy .xls requires xlrd==1.2.0; xlrd 2.0 dropped .xls support. Binary .xlsb needs pyxlsb or calamine.)

Bash
pip install pandas openpyxl

Create a sample workbook

So the examples below have something to read, write a two-sheet workbook now:

Python
import pandas as pd

sales = pd.DataFrame({
    "Date": ["2024-01-05", "2024-01-06", "2024-01-07", "2024-01-08"],
    "Transaction_ID": ["T-001", "T-002", "T-003", "T-004"],
    "Amount": [120.50, 89.00, 240.75, 15.25],
    "Category": ["Hardware", "Software", "Hardware", "Services"],
})

returns = pd.DataFrame({
    "Date": ["2024-01-09", "2024-01-10"],
    "Transaction_ID": ["T-005", "T-006"],
    "Amount": [-30.00, -12.50],
    "Category": ["Hardware", "Services"],
})

with pd.ExcelWriter("ledger.xlsx", engine="openpyxl") as writer:
    sales.to_excel(writer, sheet_name="Sales", index=False)
    returns.to_excel(writer, sheet_name="Returns", index=False)

print("Wrote ledger.xlsx")

The simplest read

Call read_excel with just a path. It reads the first sheet and uses row 0 as the header:

Python
df = pd.read_excel("ledger.xlsx")
print(df)

Parameters you will use constantly

A handful of arguments cover most real reads. Here they are together on the Sales sheet:

Python
df = pd.read_excel(
    "ledger.xlsx",
    sheet_name="Sales",                 # name or 0-based index
    usecols=["Date", "Amount", "Category"],  # read only these columns
    parse_dates=["Date"],               # convert to datetime64
    dtype={"Category": "category"},     # pin types you depend on
)
print(df.dtypes)
print(df)
  • sheet_name — a name ("Sales"), a 0-based index (0), a list to read several sheets at once, or None for every sheet.
  • usecols — limit to the columns you need; accepts a label list or an Excel range like "A:C". Smaller reads use less memory. Read specific columns from Excel with pandas covers every form this argument accepts.
  • parse_dates — turn date columns into real datetime64 values so you can resample and compare periods.
  • dtype — pin types explicitly. Use "string" for IDs to preserve leading zeros and avoid scientific notation; "category" for low-cardinality text.
  • skiprows / header — drop title or metadata rows above the real header. The step-by-step guide covers these in depth.

Reading multi-sheet workbooks

Reporting files rarely live on a single tab. You have three ways to navigate them.

Inspect the tabs first

Open the workbook once with pd.ExcelFile to list sheets without loading data, then read only what you need. Reusing the same handle avoids re-parsing the file:

Python
xls = pd.ExcelFile("ledger.xlsx", engine="openpyxl")
print("Sheets:", xls.sheet_names)

df_returns = pd.read_excel(xls, sheet_name="Returns")
print(df_returns)

Read several sheets at once

Pass a list of names (or None for all sheets) and pandas returns a dict of {sheet_name: DataFrame}:

Python
frames = pd.read_excel("ledger.xlsx", sheet_name=["Sales", "Returns"])
print(type(frames), list(frames))

Stack the sheets into one DataFrame

A common goal is one combined table. Concatenate the dict's values, tagging each row with its source sheet:

Stacking a dict of sheets into one DataFrame with pd.concat The frames dict — Sales and Returns DataFrames — flows through pd.concat with ignore_index and a Source tag, producing one combined table whose new Source column records which sheet each row came from. frames · dict Sales 4 rows Returns 2 rows pd.concat ignore_index=True + assign(Source=name) combined row data Source Sales rows ×4 Sales Returns rows ×2 Returns one table, and a Source column that remembers each row's tab
Python
combined = pd.concat(
    [frame.assign(Source=name) for name, frame in frames.items()],
    ignore_index=True,
)
print(combined)

For combining data spread across separate files rather than tabs, see Working with Multiple Excel Sheets in Python and Combine Multiple Excel Files into One.

Verify what you loaded

Right after a read, check the shape, columns, and types. This catches template drift — a renamed column or a number that arrived as text — before it corrupts a downstream calculation. The messy values a read surfaces are exactly what cleaning Excel data with pandas exists to fix:

Python
print("Rows, cols:", combined.shape)
print(combined.dtypes)
print(combined.isna().sum())

Common errors and fixes

ErrorCauseFix
ModuleNotFoundError: No module named 'openpyxl'No Excel engine installedpip install openpyxl
ValueError: Excel file format cannot be determinedWrong extension, or a renamed CSV/HTML fileConfirm the real format; pass engine="openpyxl"
ValueError: Worksheet named 'X' not foundSheet name typo or template changeList pd.ExcelFile(path).sheet_names first
IDs lose leading zeros / show as 1.0e+05pandas inferred a numeric dtypeRead those columns as dtype={"col": "string"}
PermissionError on readFile is open in ExcelClose it, or copy the file before reading

Read only what you need

The default read_excel call parses every cell of every column, then hands you a frame you probably wanted three columns of. Narrowing the read is both the simplest optimisation and the best defence against a source file that grows a column you never asked for:

Python
import pandas as pd

# By name — safest, because a reordered export cannot silently shift the meaning
orders = pd.read_excel(
    "orders.xlsx",
    sheet_name="Orders",
    usecols=["Order_ID", "Order_Date", "Region", "Quantity", "Unit_Price"],
)

# By Excel letters — useful when the export has no usable headers
letters = pd.read_excel("orders.xlsx", sheet_name="Orders", usecols="A:C,F")

# By predicate — everything except the free-text columns
def wanted(name: str) -> bool:
    return not str(name).lower().startswith(("notes", "comment"))

lean = pd.read_excel("orders.xlsx", sheet_name="Orders", usecols=wanted)

print(orders.shape, letters.shape, lean.shape)

usecols is applied while parsing, so skipped cells are never converted into Python objects at all — which is why it usually beats loading everything and dropping columns afterwards. On a wide export the saving is large, because the columns you do not need tend to be the wide free-text ones.

Two more arguments earn their place on a first read. nrows=200 gives you a fast look at the shape of an unfamiliar file without waiting for the whole sheet, and dtype=object stops pandas guessing types before you have decided what they should be — which is what keeps leading zeros on an account code and keeps a stray "n/a" visible rather than silently turning a numeric column into text.

Reading several sheets at once

sheet_name accepts more than a single name, and the return type changes with it. That difference catches people out often enough to be worth stating plainly:

What each sheet_name argument returns A string or an integer returns a single DataFrame. A list of names returns a dictionary keyed by sheet name. None returns a dictionary of every sheet in the workbook. sheet_name="Orders" one DataFrame sheet_name=["Q1", "Q2"] dict: {"Q1": df, "Q2": df} sheet_name=None dict of every sheet
Python
import pandas as pd

book = pd.read_excel("quarters.xlsx", sheet_name=None)      # every sheet
combined = pd.concat(
    [frame.assign(Quarter=name) for name, frame in book.items()],
    ignore_index=True,
)
print(sorted(book), combined.shape)

Adding the sheet name as a column during the concatenation is what keeps the combined frame usable — without it the rows lose the one piece of context the workbook's structure was carrying. It is the same idea as combining multiple Excel files, applied within a single workbook.

Opening the file once with pd.ExcelFile and calling .parse() per sheet is worth doing when you need several sheets from a large workbook: the archive is parsed once rather than per call, which on a big file is the difference between seconds and minutes.

Check what you read before using it

A read that succeeds is not a read that worked. Three lines after every read_excel catch most surprises while they are still cheap:

Python
import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype=object)

print(df.shape)                       # did the row count look like last month's?
print(df.dtypes)                      # any column unexpectedly object?
print(df.isna().sum())                # which columns arrived mostly empty?

The middle line is the one that repays the habit. A numeric column reported as object means at least one cell is not a number, and finding that here — rather than when a sum silently produces the wrong answer — is the difference between a two-minute fix and an afternoon of reconciliation.

Headers that are not on row one

Exports routinely put a title, a timestamp and a blank row above the real header. skiprows handles it, but hardcoding the count means one extra line upstream breaks the import. Find the header instead:

Python
import pandas as pd

def find_header_row(path, sheet, required, max_scan=10):
    probe = pd.read_excel(path, sheet_name=sheet, header=None, nrows=max_scan, dtype=object)
    for index, row in probe.iterrows():
        values = {str(v).strip().lower() for v in row if pd.notna(v)}
        if {r.lower() for r in required} <= values:
            return index
    raise ValueError(f"no header row containing {required} in the first {max_scan} rows")

header_row = find_header_row("export.xlsx", "Export", ["Order_ID", "Quantity"])
df = pd.read_excel("export.xlsx", sheet_name="Export", header=header_row, dtype=object)
print("header found on sheet row", header_row + 1, "-", list(df.columns))

The same problem appears at the bottom of a sheet. Many exports finish with a total row, a blank line and a footnote, all of which arrive as data. skipfooter removes a fixed number of trailing rows, but the robust version drops rows where the key column is empty:

Python
clean = df[df["Order_ID"].notna()].copy()
print(f"{len(df) - len(clean)} trailing row(s) dropped")

Dates, decimals and the values that arrive wrong

Three conversions cause most of the confusion between what a sheet shows and what pandas reports.

Dates may arrive as real datetimes, as text in any format, or as Excel serial numbers counted from 1899-12-30. Reading raw and converting explicitly handles all three, and keeps the failures visible:

Python
import pandas as pd

EXCEL_EPOCH = pd.Timestamp("1899-12-30")

raw = pd.Series(["2026-01-14", "15/01/2026", 45678, None, "not a date"])
numeric = pd.to_numeric(raw, errors="coerce")
from_serial = EXCEL_EPOCH + pd.to_timedelta(numeric, unit="D")
from_text = pd.to_datetime(raw.where(numeric.isna()), errors="coerce",
                           format="mixed", dayfirst=True)
parsed = from_serial.fillna(from_text)
print(parsed.tolist())

Numbers typed with thousands separators or currency symbols arrive as text, and one such cell turns the whole column into object. Strip before converting, and keep the failures:

Python
def to_number(series):
    text = series.astype(str).str.replace(r"[£$€,\s]", "", regex=True)
    return pd.to_numeric(text.replace({"": None, "nan": None, "n/a": None}), errors="coerce")

prices = pd.Series(["£1,234.50", "99", "n/a", ""])
converted = to_number(prices)
print(converted.tolist(), "failed:", int((converted.isna() & prices.ne("")).sum()) - 1)

Identifiers are the third case, and the most damaging. An account code of 00412 read as a number becomes 412, and the join against a reference table then matches nothing. Read identifier columns as text from the start — dtype={"Code": str} — or read everything as object and convert deliberately, which is the approach checking Excel data types with pandas develops in full.

Merged cells and the gaps they leave

Merged header cells are common in hand-built reports, and pandas sees them as one value followed by blanks. The result is a frame with NaN where a reader sees a repeated label:

Python
import pandas as pd

df = pd.read_excel("regional.xlsx", sheet_name="Report", dtype=object)
df["Region"] = df["Region"].ffill()          # carry the merged label down its block
print(df.head(8))

ffill is the right fix when the merge genuinely means "same as above", which is nearly always the case for a grouping column. It is the wrong fix when a blank means "no value recorded", so apply it per column rather than across the frame — a blanket df.ffill() invents data in every numeric column it touches.

Where the merge is in the header itself — two rows of headers, the first merged across groups — the tidiest approach is to read with header=[0, 1] and then flatten the resulting MultiIndex:

Python
df = pd.read_excel("regional.xlsx", sheet_name="Report", header=[0, 1])
df.columns = [
    " ".join(str(part) for part in col if not str(part).startswith("Unnamed")).strip()
    for col in df.columns
]
print(list(df.columns))

A read function worth reusing

Everything above collapses into one helper that most projects end up wanting:

Python
import pandas as pd

def read_sheet(path, sheet, required, text_columns=(), date_columns=()):
    book = pd.ExcelFile(path)
    if sheet not in book.sheet_names:
        raise ValueError(f"sheet {sheet!r} not in {book.sheet_names}")

    df = book.parse(sheet, dtype=object)
    df.columns = [str(c).strip() for c in df.columns]

    missing = [c for c in required if c not in df.columns]
    if missing:
        raise ValueError(f"{path}: missing column(s) {missing}")

    for column in text_columns:
        df[column] = df[column].astype("string").str.strip()
    for column in date_columns:
        df[column] = pd.to_datetime(df[column], errors="coerce", format="mixed")

    return df

orders = read_sheet(
    "orders.xlsx", "Orders",
    required=["Order_ID", "Region", "Quantity"],
    text_columns=["Region"],
    date_columns=["Order_Date"],
)
print(orders.dtypes)

Four arguments, one predictable frame, and a clear error whenever the file is not what the job expects. Wrapping the read like this is the single most useful refactor in a growing spreadsheet pipeline, because every later stage can then assume a known shape.

Engines, and the files pandas cannot open

read_excel picks an engine from the file extension: openpyxl for .xlsx and .xlsm, calamine or xlrd for legacy .xls, and odf for OpenDocument spreadsheets. Naming the engine explicitly makes failures legible rather than mysterious:

Python
import pandas as pd

modern = pd.read_excel("orders.xlsx", engine="openpyxl")

try:
    legacy = pd.read_excel("archive.xls")
except Exception as exc:
    print(f"{type(exc).__name__}: install a legacy engine, or convert the file first")

A .xls file is a completely different binary format, and modern pandas cannot read it without an extra dependency. Converting once — in Excel, or headlessly with LibreOffice — is usually better than carrying the dependency, because everything downstream then works with one format.

The other common surprise is a file whose extension lies: a CSV named .xlsx, or an HTML table saved by a reporting system with a spreadsheet extension. Both raise a zip or parse error, and the fix is read_csv or read_html rather than anything to do with Excel.

Read once, reuse many times

When the same workbook feeds several steps, parsing it repeatedly is pure waste — the archive is decompressed and the XML walked every time. Open it once with pd.ExcelFile and parse from that handle, or convert the sheet to Parquet on first read and let every later step read the fast format instead. On a large monthly export that single change often halves a pipeline's runtime, and it costs two lines.

Frequently asked questions

Which engine does read_excel use, and do I have to install one? pandas does not parse Excel itself — it delegates to an engine. openpyxl handles modern .xlsx files; legacy .xls needs xlrd==1.2.0 (xlrd 2.0 dropped .xls support), and binary .xlsb needs pyxlsb or calamine.

How do I read every sheet in a workbook at once? Pass sheet_name=None (or a list of names) and read_excel returns a dict of {sheet_name: DataFrame}. To list tabs first without loading data, open the file with pd.ExcelFile and read its sheet_names.

Why do my ID columns lose leading zeros or show as 1.0e+05? pandas inferred a numeric dtype for those columns. Read them as text with dtype={"col": "string"} to preserve leading zeros and avoid scientific notation.

How can I read only some columns to save memory? Pass usecols with either a list of column labels or an Excel range like "A:C". Combined with sheet_name, it avoids materializing columns and tabs you won't use.

How do I combine sheets into a single DataFrame? Read them into a dict, then pd.concat the values with ignore_index=True. Tag each row with its source using frame.assign(Source=name) inside the comprehension so you can tell the tabs apart.

Key takeaways

  • pd.read_excel() is three lines at its simplest; the difficulty is defensive practice, not syntax.
  • Install an engine explicitly — openpyxl for .xlsx, xlrd==1.2.0 for legacy .xls, pyxlsb or calamine for .xlsb.
  • Name the sheet, name the columns with usecols, and pin the types with dtype so IDs keep leading zeros and dates arrive as datetime64.
  • For multi-sheet files, list tabs with pd.ExcelFile, read a dict with sheet_name=None, and pd.concat the values with a Source tag when you want one table.
  • Verify shape, dtypes, and nulls straight after the read — that habit turns schema drift and silent coercions into caught errors rather than corrupted reports.