Pandas read_excel: How to read Excel files in Python

Pandas read_excel is the standard way to pull data out of an Excel workbook and into a DataFrame for analysis in Python. It handles xls, xlsx, xlsm, xlsb and even OpenDocument spreadsheets, and it comes with enough parameters to handle almost any layout a real spreadsheet throws at it. This guide covers the full range of those parameters, from picking a sheet to setting data types, so a workbook can be loaded correctly on the first try.

What pandas read_excel does

At its simplest, read_excel takes a file path and returns a DataFrame built from the first sheet in that workbook. Under the hood it depends on a separate library, called an engine, to actually parse the spreadsheet file format. Pandas picks a sensible engine automatically based on the file extension, though that choice can be overridden.

The full function signature looks like this:

pandas.read_excel(
    io,
    sheet_name=0,
    header=0,
    names=None,
    index_col=None,
    usecols=None,
    dtype=None,
    engine=None,
    converters=None,
    skiprows=None,
    nrows=None,
    na_values=None,
    parse_dates=False,
    thousands=None,
    decimal='.',
    comment=None,
    skipfooter=0,
    storage_options=None
)

Most of the time only a handful of these get used. The rest exist for edge cases like European decimal separators or footer rows that need trimming. Once the file is loaded, the result behaves like any other pandas DataFrame, so filtering, grouping and column work all apply the same way they would to data read from a CSV.

Installing pandas and an Excel engine

Pandas does not read Excel files on its own. It needs a small helper library to actually decode the file format, since xlsx and xls are structured very differently under the hood. For a typical setup, installing pandas and openpyxl covers the common case:

pip install pandas openpyxl

openpyxl handles modern xlsx and xlsm files and is the default engine pandas reaches for automatically. Older xls files need a separate library called xlrd, since openpyxl cannot read that legacy binary format:

pip install xlrd

Binary xlsb files need pyxlsb, and OpenDocument spreadsheets (.ods) need odfpy. Installing all of them up front avoids hunting down ImportError: Missing optional dependency messages mid-project.

Reading a basic Excel file

With pandas and openpyxl installed, loading a workbook takes one line:

import pandas as pd

df = pd.read_excel("students.xlsx")
print(df.head())

This reads the first sheet in students.xlsx, treats the first row as column headers and returns a DataFrame. The head() method then prints the first five rows so the shape of the data can be checked right away. Checking the row and column count right after a read is a quick habit worth building, since a wrong sheet or a shifted header row usually shows up immediately in those numbers.

Reading a single sheet with sheet_name

Workbooks rarely have just one sheet. The sheet_name parameter picks which one to load, either by its position (starting at 0) or by its exact name:

# By position
teachers = pd.read_excel("students.xlsx", sheet_name=1)

# By name
teachers = pd.read_excel("students.xlsx", sheet_name="teacher")

Using the sheet name is safer than the index in practice, because a coworker rearranging tabs in Excel will silently break code that depends on sheet position. A list of names or indices also works and returns a dictionary of DataFrames keyed by whatever was passed in:

sheets = pd.read_excel("students.xlsx", sheet_name=["student", "teacher"])
student_df = sheets["student"]
teacher_df = sheets["teacher"]

Reading every sheet at once

Passing sheet_name=None tells pandas to load the entire workbook in a single call, returning a dictionary with one DataFrame per sheet:

all_sheets = pd.read_excel("students.xlsx", sheet_name=None)
print(all_sheets.keys())

for name, sheet_df in all_sheets.items():
    print(name, sheet_df.shape)

This is the fastest way to check what’s actually inside a workbook without opening Excel first, and it works even when the sheet count or naming isn’t known ahead of time. If the sheets share the same columns, such as monthly sales tabs that all follow one layout, concatenating them into one DataFrame is usually the next step, using pd.concat(all_sheets.values()). When the sheets have different structures instead, like one tab per department with its own columns, it’s often simpler to keep the dictionary as is and work with each sheet on its own.

Previewing sheet names with the ExcelFile object

Calling read_excel repeatedly on the same file re-opens and re-parses it every time, which wastes time on large workbooks. The ExcelFile object opens the file once and exposes its sheet names, so a script can decide which sheet to load before committing to a full read:

with pd.ExcelFile("students.xlsx") as xls:
    print(xls.sheet_names)
    student_df = pd.read_excel(xls, sheet_name="student")
    teacher_df = pd.read_excel(xls, sheet_name="teacher")

Passing the ExcelFile object into read_excel instead of the file path means pandas reuses the already-open file for each sheet, rather than reopening students.xlsx from disk three separate times. For a workbook with many sheets, this pattern is noticeably faster than calling read_excel directly with a path each time.

Handling headers, custom names and an index column

By default, row 0 becomes the column headers. That assumption breaks the moment a spreadsheet has a title row, a blank row or no header row at all. The header parameter points pandas at the correct row:

# Real header lives on row 2 (zero-indexed), rows above it are ignored
df = pd.read_excel("report.xlsx", header=2)

# No header row exists in the file
df = pd.read_excel("report.xlsx", header=None)

When there’s no header row, the names parameter supplies column labels directly:

df = pd.read_excel(
    "report.xlsx",
    header=None,
    names=["id", "product", "revenue", "region"]
)

index_col promotes one of the columns to the DataFrame’s row index instead of leaving it as a regular column, which is handy for a column like an ID or a date that identifies each row without repeats:

df = pd.read_excel("report.xlsx", index_col=0)

Once the header and index are set correctly, checking the column names is a quick way to confirm the sheet loaded the way it was meant to.

Selecting certain columns with usecols

Wide spreadsheets often carry columns that never get used in analysis. usecols narrows the read down to just what’s needed, which also speeds up loading on a large file since pandas skips parsing the rest:

# By Excel-style column letters
df = pd.read_excel("report.xlsx", usecols="A:C")

# By column name
df = pd.read_excel("report.xlsx", usecols=["product", "revenue"])

# By integer position
df = pd.read_excel("report.xlsx", usecols=[0, 2])

A callable also works, which is useful when the columns to keep follow a pattern rather than a fixed list:

df = pd.read_excel("report.xlsx", usecols=lambda col: "2024" in col)

Skipping rows with skiprows

Spreadsheets built for humans often start with a company logo row, a title or some notes before the actual table begins. skiprows removes those rows before pandas even tries to detect a header:

# Skip the first 3 rows entirely
df = pd.read_excel("report.xlsx", skiprows=3)

# Skip individual row numbers instead of a fixed count
df = pd.read_excel("report.xlsx", skiprows=[0, 2])

skiprows also accepts a callable, which is the cleanest way to skip rows based on a pattern rather than fixed positions, such as every other row:

df = pd.read_excel("report.xlsx", skiprows=lambda row: row % 2 == 1)

skipfooter works the same way but trims rows from the bottom, which comes in handy for spreadsheets that end with a totals row that shouldn’t be treated as data.

Controlling data types with dtype

Pandas guesses each column’s data type by sampling the values in it, and the guess is wrong often enough to cause real bugs. A product code like “00123” silently loses its leading zero once pandas reads it as a number. The dtype parameter forces the right types up front:

df = pd.read_excel(
    "report.xlsx",
    dtype={"product_code": str, "revenue": float, "units": "Int64"}
)

Passing a single value applies it to every column, while a dictionary targets individual ones by name. Locking in the correct types at read time avoids a whole class of bugs where a numeric mean() or a string comparison quietly fails later because a column ended up as the wrong type. Using object for a column keeps its values exactly as Excel stored them, without any type conversion at all.

Handling missing values with na_values

Pandas already recognizes common missing-value markers like empty cells, “NA” and “NULL” automatically. Real spreadsheets, though, tend to use their own placeholders, like “N/A”, “-” or “TBD”. na_values tells pandas to treat those as missing too:

df = pd.read_excel("report.xlsx", na_values=["N/A", "TBD", "-"])

A dictionary applies different missing markers to different columns, which matters when one column uses “Unknown” for a missing entry and another uses “999”:

df = pd.read_excel(
    "report.xlsx",
    na_values={"region": ["Unknown"], "revenue": [999, -1]}
)

Passing keep_default_na=False alongside na_values turns off pandas’s built-in list entirely, so only the values explicitly listed count as missing. This matters for a column where a real value happens to be the string “NA”, such as a two-letter US state code.

Parsing dates with parse_dates

Excel stores dates as serial numbers internally, and depending on formatting, pandas sometimes reads a date column as plain text instead of an actual datetime. parse_dates fixes this by telling pandas which columns to convert:

df = pd.read_excel("orders.xlsx", parse_dates=["order_date", "ship_date"])
print(df.dtypes)

With the columns parsed correctly, date arithmetic and .dt accessor methods work right away, instead of requiring a separate pd.to_datetime() call after the fact. For dates in an unusual format, pairing parse_dates with the date_format parameter (added in pandas 2.0) avoids ambiguous parsing, especially for day-first date formats common outside the US.

Reading only some rows with nrows

For a workbook with hundreds of thousands of rows, loading the whole thing just to test a cleaning script wastes time. nrows caps how many rows come back:

sample = pd.read_excel("big_report.xlsx", nrows=1000)

This is a common pattern while writing and testing a script: work against a 1,000-row sample with nrows, confirm the logic behaves as expected, then drop the limit for the production run against the full file. It also helps when a workbook is large enough that a single failed read wastes several minutes, since a quick sample catches a wrong column name or a bad dtype setting long before the full run gets anywhere near that point.

Choosing the right engine for xls, xlsb and ods files

The engine parameter controls which library actually parses the file, and pandas usually picks correctly on its own based on the file extension. Forcing it manually helps when the automatic guess is wrong or when a faster alternative is available:

df = pd.read_excel("legacy_report.xls", engine="xlrd")
df = pd.read_excel("report.xlsx", engine="calamine")

calamine is a newer, Rust-based engine that reads xlsx, xls, xlsb and ods files noticeably faster than openpyxl on large workbooks. Testing it against the actual file in question is a good idea before switching a production job over to it, since gains vary by file structure.

Reading an Excel file from a URL

read_excel accepts a direct link to a workbook the same way it accepts a local path, as long as the URL points straight at the file rather than an HTML page that happens to contain a download link:

url = "https://example.com/datasets/sales-report.xlsx"
df = pd.read_excel(url)

http, ftp and s3 URLs all work this way, which is useful for pulling a government dataset or a shared report straight into a script without a separate download step. For a file behind authentication, the storage_options parameter passes through headers or credentials to whichever backend handles that URL scheme, so a private S3 bucket or an internal reporting link can be read the same way as a public file.

Pandas read_excel vs read_csv for large files

read_excel is noticeably slower than reading the same data from a CSV file, since parsing Excel’s XML-based format takes real work that a plain-text CSV skips entirely. For a workbook in the tens of thousands of rows, the difference stays small enough to ignore. Past a few hundred thousand rows, converting the file to CSV once and reading it with pd.read_csv() afterward can cut load time by an order of magnitude, particularly for a script that runs the same import repeatedly, like a nightly job.

A practical middle ground: use nrows to prototype against read_excel directly, then convert to CSV or Parquet once the cleaning logic is settled and the same file needs to be loaded many times.

Common errors and how to fix them

A handful of errors come up often enough to be worth knowing on sight:

• ImportError: Missing optional dependency ‘openpyxl’ means the engine library isn’t installed. Running pip install openpyxl resolves it.
• XLRDError: Excel xlsx file not supported happens when xlrd 2.x tries to open an xlsx file, since newer xlrd versions only handle legacy xls. Passing engine="openpyxl" fixes it without downgrading xlrd.
• ValueError: Worksheet named ‘X’ not found usually means a typo in sheet_name. Printing pd.ExcelFile("file.xlsx").sheet_names first shows the exact names available.
• Unexpected object dtype on a numeric-looking column typically means the column has mixed types, like text mixed with numbers. Setting dtype explicitly, or cleaning the source column in Excel, resolves it.

Key Takeaways

• read_excel loads the first sheet, use sheet_name to pick another or read them all
• ExcelFile previews sheet names without a full read, useful for workbooks with many sheets
• dtype and na_values stop silent corruption like dropped leading zeros or unrecognized missing markers
• skiprows and header handle spreadsheets with title rows or no header row at all
• usecols and nrows cut load time on wide or long workbooks
• openpyxl covers xlsx, xlrd covers legacy xls and calamine is a faster option to test
• Converting to CSV or Parquet pays off once row counts reach the hundreds of thousands

Frequently asked questions

How do I read a single sheet in pandas read_excel?

Pass the sheet_name parameter with either the sheet’s index, starting at 0, or its exact name as a string, like pd.read_excel("file.xlsx", sheet_name="Sheet2").

How do I read all sheets in an Excel file with pandas?

Set sheet_name=None. Pandas returns a dictionary with every sheet loaded as its own DataFrame, keyed by sheet name, ready to loop through or combine.

Why does pandas read_excel drop leading zeros from a column?

Pandas infers that column as numeric by default, which strips leading zeros. Setting dtype=str for that column during the read preserves the original text.

What’s the difference between openpyxl and xlrd?

openpyxl reads modern xlsx and xlsm files. xlrd 2.x only reads legacy xls files, since it dropped xlsx support in that version.

Can pandas read_excel read a file directly from a URL?

Yes. Passing an http, ftp or s3 URL as the file path works the same as passing a local path, as long as the link points directly at the file.

Is pandas read_excel slower than read_csv?

Yes, noticeably so on large files, since Excel’s format takes more work to parse than plain text. Converting large workbooks to CSV before repeated processing saves real time.

Conclusion

Excel files show up in nearly every data workflow, and read_excel covers the vast majority of what they throw at a script. Getting the sheet, header, types and missing values right at read time saves a lot of cleanup work further down the pipeline.