How to Read an Excel File in Python

To read an Excel file in Python, call pandas.read_excel("file.xlsx"), which returns a DataFrame, or use openpyxl.load_workbook() when you need individual cells, formulas or formatting. Both read .xlsx files, and pandas uses openpyxl underneath:

import pandas as pd
df = pd.read_excel("sales_2026.xlsx", sheet_name="Q3 Sales")

The workbook in these examples is a small 2026 sales report with a Stores sheet. I read it with Python 3.12.5, pandas 3.0.6 and openpyxl 3.1.5 (install them with pip install pandas openpyxl).

You’ll see pandas for table data, openpyxl for rows and cells, why formula cells come back empty, how ZIP codes lose their zeros, and reading every sheet at once.

Read an Excel file into pandas with read_excel()

For table-shaped data, pandas is the shortest path. It uses the first row as headers and picks a type for each column:

import pandas as pd

df = pd.read_excel("sales_2026.xlsx", sheet_name="Q3 Sales", nrows=5)
print(df)
print(df.dtypes.to_string())
print(f"Total revenue: ${df['Revenue'].sum():,.2f}")

Output:

        Date Store    City State    ZIP   Revenue
0 2026-07-06  S101  Austin    TX  73301  18250.50
1 2026-07-13  S102  Boston    MA   2134  12990.00
2 2026-08-03  S103   Tampa    FL  33601  15420.75
3 2026-08-17  S104  Newark    NJ   7102   9875.25
4 2026-09-07  S105  Denver    CO  80202  14310.00
Date       datetime64[us]
Store                 str
City                  str
State                 str
ZIP                 int64
Revenue           float64
Total revenue: $70,846.50

nrows=5 stops before the Total row, which would otherwise show up as data. Real exports are rarely this tidy, so the next section handles title rows and placeholders. To go the other way, see writing a DataFrame to Excel.

Read messy Excel sheets: title rows, columns and missing values

Exported reports often start with a title and a date line, mark missing data as text like n/a, and carry columns you don’t need. Read them as-is and the title becomes your header:

import pandas as pd

raw = pd.read_excel("sales_2026.xlsx", sheet_name="Report")
print(raw.columns.tolist()[:3])                     # the title row became the header

df = pd.read_excel("sales_2026.xlsx", sheet_name="Report", skiprows=2, usecols="A:D",
                   na_values=["n/a"], parse_dates=["Week"])
print(df)
print(df.dtypes.to_string())
print(f"Known sales total: ${df['Sales'].sum():,.2f}")

Output:

['Store Performance Report', 'Unnamed: 1', 'Unnamed: 2']
  Store       Week  Visitors    Sales
0  S101 2026-09-28    1840.0  18250.5
1  S102 2026-09-28       NaN  12990.0
2  S103 2026-09-28    1525.0      NaN
Store                  str
Week        datetime64[us]
Visitors           float64
Sales              float64
Known sales total: $31,240.50

na_values turns placeholders into NaN, so sum() skips them, and usecols="A:D" takes Excel column letters. read_excel also accepts a URL or an open file object instead of a path.

Why do ZIP codes lose their leading zeros?

The ZIP column is stored as text in the workbook, but pandas still turns anything that looks numeric into a number. Tell it the type you want:

import pandas as pd

wrong = pd.read_excel("sales_2026.xlsx", nrows=5, usecols=["City", "ZIP"])
right = pd.read_excel("sales_2026.xlsx", nrows=5, usecols=["City", "ZIP"], dtype={"ZIP": str})
print(wrong["ZIP"].tolist())
print(right["ZIP"].tolist())

Output:

[73301, 2134, 33601, 7102, 80202]
['73301', '02134', '33601', '07102', '80202']

Boston’s 02134 becomes 2134 unless you pass dtype={"ZIP": str}. The same fix applies to account numbers and product codes, and padding them back is shown in padding a string with zeros in Python.

Read rows and cells with openpyxl

openpyxl works with the workbook itself. You can read one cell by name, by row and column number, or walk the rows as tuples:

from openpyxl import load_workbook

wb = load_workbook("sales_2026.xlsx")
print(wb.sheetnames)

ws = wb["Q3 Sales"]
print("B2 =", ws["B2"].value, "| row 3, col 3 =", ws.cell(row=3, column=3).value)
print("size:", ws.max_row, "rows x", ws.max_column, "columns")

for date, store, city, state, zip_code, revenue in ws.iter_rows(min_row=2, max_row=6, values_only=True):
    print(f"{date:%b %d}  {store}  {city:<7} {state}  {zip_code}  ${revenue:>10,.2f}")

Output:

['Q3 Sales', 'Stores', 'Report']
B2 = S101 | row 3, col 3 = Boston
size: 8 rows x 6 columns
Jul 06  S101  Austin  TX  73301  $ 18,250.50
Jul 13  S102  Boston  MA  02134  $ 12,990.00
Aug 03  S103  Tampa   FL  33601  $ 15,420.75
Aug 17  S104  Newark  NJ  07102  $  9,875.25
Sep 07  S105  Denver  CO  80202  $ 14,310.00

values_only=True gives plain values instead of cell objects. Dates come back as datetime, which is why {date:%b %d} works. For file paths built from user input, getting the current directory in Python helps.

Why does openpyxl return None for a formula cell?

By default you get the formula text. With data_only=True you get the value Excel last calculated and saved. A file created by a script has never been calculated, so there’s no value yet:

import pandas as pd
from openpyxl import load_workbook

print("default:  ", load_workbook("sales_2026.xlsx")["Q3 Sales"]["F8"].value)
print("data_only:", load_workbook("sales_2026.xlsx", data_only=True)["Q3 Sales"]["F8"].value)
print("pandas:   ", pd.read_excel("sales_2026.xlsx").iloc[6]["Revenue"])

Output:

default:   =SUM(F2:F6)
data_only: None
pandas:    nan

openpyxl doesn’t calculate formulas. Open and save the file in Excel once, or compute the total in Python and round it as shown in rounding numbers to 2 decimal places.

Read every sheet in an Excel workbook

sheet_name=None loads all sheets into a dictionary, keyed by sheet name. From there you can join them like database tables:

import pandas as pd

sheets = pd.read_excel("sales_2026.xlsx", sheet_name=None)   # every sheet -> dict of DataFrames
for name, frame in sheets.items():
    print(f"{name}: {frame.shape[0]} rows x {frame.shape[1]} columns")

stores = sheets["Stores"]
sales = sheets["Q3 Sales"].dropna(subset=["Store"])
merged = sales.merge(stores, on="Store")[["City", "Manager", "Revenue"]]
print(merged.to_string(index=False))

Output:

Q3 Sales: 7 rows x 6 columns
Stores: 5 rows x 3 columns
Report: 5 rows x 5 columns
  City      Manager  Revenue
Austin Emily Carter 18250.50
Boston James Wilson 12990.00
 Tampa Olivia Brown 15420.75
Newark   Liam Davis  9875.25
Denver   Ava Miller 14310.00

dropna removes the Total row, which has no store ID. If your data arrives as CSV instead, reading a CSV with pandas works the same way.

Read large Excel files with read_only mode

For big workbooks, read_only=True streams rows instead of loading everything into memory:

from openpyxl import load_workbook

wb = load_workbook("sales_2026.xlsx", read_only=True, data_only=True)
ws = wb["Stores"]
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)
wb.close()                     # read-only mode keeps the file open until you close it

Output:

('S101', 'Emily Carter', 2019)
('S102', 'James Wilson', 2021)
('S103', 'Olivia Brown', 2022)
('S104', 'Liam Davis', 2024)
('S105', 'Ava Miller', 2025)

Remember to call close(). Old .xls files aren’t supported by openpyxl; pandas needs the xlrd package for those. To find files before reading them, see listing files in a directory with Python.

pandas vs openpyxl for reading Excel

Task	pandas	openpyxl
Whole table	read_excel()	iter_rows(values_only=True)
One cell	df.iloc[r, c]	ws["B2"].value
All sheets	sheet_name=None	wb.sheetnames
Formula text	No	Default load
Cached formula value	Yes (NaN if none)	data_only=True
Huge files	nrows, usecols	read_only=True

These spreadsheet guides go well with this one:

• Read Excel files in Python (more examples)
• Read large CSV files in Python
• Save a pandas DataFrame to CSV
• Write a Python dictionary to CSV

Frequently asked questions