The spreadsheet is not the data. read_excel() is the doorway.
Finance sends you 'the workbook'. It has six tabs, a title banner row, merged cells, and one tab with the actual numbers. read_excel() is how you get from that to a DataFrame in one call.
pd.read_excel() reads a workbook and hands you a DataFrame. The default is the first sheet, but sheet_name accepts a name, a zero-based index, a list of them, or None for every sheet at once — the None case returns a dict of DataFrames keyed by sheet name. Under it sit three interchangeable engines: openpyxl for .xlsx/.xlsm, xlrd for legacy .xls, and calamine (the Rust engine, pandas 2.2+) for both, fastest of the three.
Excel is still where the business world lives: budgets, inventory counts, sensor logs, commission reports. Real workbooks are rarely clean — a title banner in row 1, notes in row 2, headers somewhere lower, columns you do not need, numbers stored as text. read_excel() has a flag for every one of those problems: skiprows, usecols, dtype, names. One call and the spreadsheet becomes a DataFrame you can group, merge, and clean.
df = pd.read_excel("monthly_report.xlsx", sheet_name="orders")
dfregion product units unit_price 0 North Valve 120 19.50 1 South Gauge 45 7.25 2 North Valve 80 19.50 3 East Pump 200 44.00 4 South Gauge 60 7.25 5 West Pump 150 41.00
One sheet by name — the shape most people need. sheet_name="orders" is safer than an index that shifts when someone inserts a cover sheet.
sheets = pd.read_excel("monthly_report.xlsx", sheet_name=None)
list(sheets)
sheets["summary"]['orders', 'summary']
metric value
0 total_units 655.0
1 revenue 13212.5
sheet_name=None reads every sheet in one pass and returns a dict — iterate it to validate or stack tabs programmatically.
df2 = pd.read_excel("monthly_report.xlsx", sheet_name="orders",
usecols="A:D", dtype={"region": "category"})
df2.dtypesregion category product str units int64 unit_price float64 dtype: object
usecols="A:D" grabs Excel column letters; dtype sets types at load, so low-cardinality columns become category before any processing.
df3 = pd.read_excel("junk_report.xlsx", sheet_name="orders", skiprows=2)
df3region product units unit_price 0 North Valve 120 19.50 1 South Gauge 45 7.25 2 North Valve 80 19.50 3 East Pump 200 44.00 4 South Gauge 60 7.25 5 West Pump 150 41.00
skiprows=2 jumps the title banner and meta line so the real header row becomes column names. Pair with names= if you want clean columns too.
| Flag | Meaning |
|---|---|
sheet_name | 0, "name", [list], or None for all sheets (returns a dict) |
usecols | Excel letters "A:C", header-based list ["region"], or a callable |
skiprows / nrows | jump banner rows at the top; cap rows read — both vital for junk-heavy corporate sheets |
dtype | per-column dtypes applied at load — stop silent inference surprises |
names + header=0 | replace the sheet's own header with your own column names |
converters | per-column callables — read dates as str to stop auto-conversion |
engine | "openpyxl" (.xlsx), "xlrd" (.xls), "calamine" (Rust, both) |
read_excel() shipped in pandas 0.4.0 (2008), in the same initial wave as read_csv — Wes McKinney built it at AQR while wrangling daily-updated Excel files from operations, and Excel import was a day-one need. The xlrd engine read both .xls and .xlsx for years.
When xlrd dropped xlsx support in v2.0 (Dec 2020) over security concerns, pandas reworked its Excel IO to a multi-engine design: openpyxl took .xlsx/.xlsm, xlrd kept only .xls, odf for OpenDocument, and pyxlsb for binary Excel. pandas 2.2 (Jan 2024) added the calamine Rust engine — a speed drop for big workbooks.
get_default_engine() in pandas.core.excel checks the file extension and picks the engine by a per-format default; passing engine= overrides it, and _excel_engines is a dict of engine names → classes (registered via register_writer/reader in pandas.io.excel). The calamine engine (pandas 2.2+) goes through python-calamine, a Rust binding to the Calamine crate — it parses the xlsx zip in native code and returns Arrow-backed data, which is why large workbooks load in a fraction of the openpyxl time. The whole resolution is ~30 lines; worth reading once so engine errors stop being mysterious.