Read and Write Excel Files in Python (pandas, openpyxl)
Install openpyxl and use pandas to read and write Excel files: sheets, formulas, formatting, large files, the calamine engine change and common errors.
Install pandas and openpyxl, then use pandas.read_excel() and DataFrame.to_excel() for tables, and openpyxl directly when you need formulas, formatting or control over individual cells. For modern .xlsx files that covers most of what people do with spreadsheets in Python.
This guide shows both, with the commands and errors you will actually meet. Everything below was run on 7 November 2026 with Python 3.13, pandas 3.1.1, openpyxl 3.1.5 and python-calamine 0.9.1. One change deserves attention before you start: pandas has begun a deprecation that affects the default engine for reading .xlsx files, covered in its own section below.
Install
pip install pandas openpyxl
# or, in a uv project
uv add pandas openpyxlpandas 3.1.1 requires Python 3.11 or later, and openpyxl 3.1.5 requires 3.8 or later. If you forget openpyxl, writing or reading an .xlsx file fails like this:
ModuleNotFoundError: No module named 'openpyxl'
openpyxl describes itself as a library to read and write Excel 2010 xlsx, xlsm, xltx and xltm files. It reads and writes the files directly, so you do not need Excel installed. It does not guard against some XML attacks by default, and its documentation advises installing defusedxml if you open files from untrusted sources. If you add lxml, openpyxl uses it, and its documentation says it helps with large writes. Images need Pillow (pip install pillow).
Which library does what?
| Library | Reads | Writes | Use it for |
|---|---|---|---|
| pandas | Yes | Yes | Tables as DataFrames; it calls an engine library underneath |
| openpyxl | .xlsx, .xlsm |
.xlsx, .xlsm |
Cell-level control, formulas, styles, editing existing workbooks |
| XlsxWriter | No | .xlsx only |
Creating new files; pandas uses it for .xlsx output if installed |
| python-calamine | .xls, .xlsx, .xlsm, .xlsb |
No | Reading, including formats openpyxl cannot read |
| xlrd | .xls |
No | Old .xls files; deprecated in pandas |
pandas’ documentation says .xlsx files are written with xlsxwriter if it is installed, and otherwise with openpyxl. XlsxWriter’s own PyPI description calls it a Python module for creating Excel XLSX files, so it is for writing new files rather than reading them.
Write an Excel file with pandas
import pandas as pd
df = pd.DataFrame({
"product": ["widget", "gadget", "gizmo"],
"units": [10, 4, 7],
"price": [2.5, 10.0, 4.25],
"date": pd.to_datetime(["2026-11-01", "2026-11-02", "2026-11-03"]),
})
df.to_excel("sales.xlsx", index=False)index=False leaves out the row numbers. You can also pass sheet_name="Nov", float_format="%.2f" and freeze_panes=(1, 0) to freeze the header row.
Several sheets in one file
Use ExcelWriter so every sheet goes into the same workbook:
with pd.ExcelWriter("multi.xlsx", engine="openpyxl") as xw:
df.to_excel(xw, sheet_name="Nov", index=False)
df.assign(units=df.units * 2).to_excel(xw, sheet_name="Dec", index=False)Add a sheet to an existing file
By default, writing to a path creates a new file and replaces the old one, so a second plain to_excel("multi.xlsx", ...) left a workbook containing only the new sheet. To add to an existing file, open it in append mode and say what to do if the sheet already exists:
with pd.ExcelWriter("multi.xlsx", engine="openpyxl", mode="a", if_sheet_exists="replace") as xw:
df.to_excel(xw, sheet_name="Summary", index=False)Without if_sheet_exists, writing a sheet that already exists raises ValueError: Sheet 'Only' already exists and if_sheet_exists is set to 'error'.
Read an Excel file with pandas
df = pd.read_excel("sales.xlsx")Column types come back sensibly: here units was an integer, price a float and date a datetime. The useful options:
pd.read_excel("multi.xlsx", sheet_name="Nov") # one sheet by name
pd.read_excel("multi.xlsx", sheet_name=0) # or by position
pd.read_excel("multi.xlsx", sheet_name=None) # every sheet, as a dict of DataFrames
pd.read_excel("multi.xlsx", usecols=["product", "units"], nrows=2) # choose columns and rows
pd.read_excel("multi.xlsx", header=None, skiprows=1, names=["a", "b", "c", "d"])
pd.read_excel("multi.xlsx", dtype={"units": "float64"})With sheet_name=None you get a dictionary keyed by sheet name (here Nov and Dec); a list such as [0, "Dec"] returns a dictionary keyed by the items you passed. usecols also accepts a column-letter range such as "A:D".
The default engine is changing
Pandas 3.1.0 deprecated relying on the default engine for .xlsx and .xlsm files when python-calamine is also installed. The default is expected to change from openpyxl to calamine in a future version, and the xlrd and pyxlsb engines are deprecated in favour of calamine. With python-calamine present and no engine given, this appeared:
Pandas4Warning: The default engine for reading 'xlsx' files will change from 'openpyxl'
to 'calamine' in a future version. Pass engine='openpyxl' (or set the
'io.excel.xlsx.reader' option) to keep the current engine and silence this warning.
There are two ways to keep your current behaviour and silence it, and both produced no warning in testing:
pd.read_excel("sales.xlsx", engine="openpyxl") # per call
pd.set_option("io.excel.xlsx.reader", "openpyxl") # for the whole programOr opt in to the new engine now with engine="calamine", which also reads .xls and .xlsb. Passing engine explicitly is a good habit either way, because it makes your code independent of the default. If python-calamine is not installed, none of this warns.
Use openpyxl directly
pandas is for tables. When you need a formula, a bold header or a column width, work on the workbook:
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["product", "units", "price", "total"])
for i, (name, units, price) in enumerate([("widget", 10, 2.5), ("gadget", 4, 10.0)], start=2):
ws.append([name, units, price, f"=B{i}*C{i}"])
ws["A5"] = "Total"
ws["D5"] = "=SUM(D2:D3)"
for cell in ws[1]:
cell.font = Font(bold=True)
ws.column_dimensions["A"].width = 14
ws["C2"].number_format = "0.00"
ws.freeze_panes = "A2"
wb.save("report.xlsx")Reading it back with load_workbook("report.xlsx") showed the formula text (=B2*C2), the 0.00 number format, the bold header and the column width of 14. ws.iter_rows(min_row=2, values_only=True) yields each row as a tuple.
To change an existing workbook, load it, edit cells and save. Be aware that openpyxl’s documentation warns it does not read everything in an Excel file: shapes will be lost from existing files if you open and save them under the same name. Visual Basic content is kept only if you ask (keep_vba=True), and kept macros are still not editable.
Format a pandas sheet with openpyxl
You can combine the two. Inside an ExcelWriter, xw.sheets["Sheet"] is the openpyxl worksheet:
with pd.ExcelWriter("fmt.xlsx", engine="openpyxl") as xw:
df.to_excel(xw, sheet_name="S", index=False)
ws = xw.sheets["S"]
ws.column_dimensions["A"].width = 18
for cell in ws[1]:
cell.font = Font(bold=True)The formula trap
Formulas are the most common surprise. openpyxl writes the formula text but does not calculate it. Excel calculates it and stores the result the next time the file is opened and saved. Until then:
load_workbook("report.xlsx", data_only=True)returnedNonefor the formula cells. The docs describedata_onlyas returning the value stored the last time Excel read the sheet, and a file that has never been in Excel has none.pd.read_excel("report.xlsx", engine="openpyxl")returnedNaNfor thetotalcolumn.
So if you generate a workbook with formulas in Python and read it straight back, the results are empty. The fixes are to open and save the file in Excel or LibreOffice first (which stores the results), or to compute the numbers in Python and write plain values.
Large files
For very large workbooks openpyxl has two modes that the docs say use near-constant memory:
# Writing: append rows one at a time
wb = Workbook(write_only=True)
ws = wb.create_sheet("big")
ws.append(["n", "square"])
for i in range(100_000):
ws.append([i, i * i])
wb.save("big.xlsx")
# Reading: lazy, rows streamed from the file
wb = load_workbook("big.xlsx", read_only=True)
for row in wb["big"].iter_rows(min_row=2, values_only=True):
...
wb.close()A write-only workbook can only append rows and can be saved only once; a second save failed in testing. Read-only workbooks must be closed explicitly with close(). This test wrote and read 100,000 rows with both modes. For more on handling big tables in Python, see Polars vs pandas.
Errors you will meet
| You see | Cause | Fix |
|---|---|---|
ModuleNotFoundError: No module named 'openpyxl' |
openpyxl not installed in this environment | pip install openpyxl |
InvalidFileException: openpyxl does not support the old .xls file format... |
Opening .xls with openpyxl |
Convert to .xlsx, or read with pandas and engine="calamine" |
ValueError: Excel file format cannot be determined, you must specify an engine manually. |
pandas cannot tell the format, for example a CSV or a damaged .xls |
Use read_csv for CSV files, or pass engine= |
ValueError: Worksheet named 'Nope' not found |
Wrong sheet_name |
Check pd.ExcelFile("file.xlsx").sheet_names |
ValueError: Sheet 'X' already exists and if_sheet_exists is set to 'error'. |
Appending a sheet that already exists | Set if_sheet_exists="replace" |
NaN or None where a formula should be |
The file holds no stored formula results | Save the file in Excel, or calculate in Python |
Which approach should you use?
- Analyse a spreadsheet or save results as a sheet: pandas.
- Style the output, write formulas or edit a template: openpyxl, or pandas plus openpyxl for the formatting.
- Create large new files: openpyxl’s write-only mode, or XlsxWriter.
- Read old
.xlsor.xlsbfiles: pandas withengine="calamine". - No Excel at all: a plain CSV read with
read_csvis simpler, because it needs no Excel library at all.
New to the DataFrame itself? The pandas resource page and NumPy vs pandas are the place to start. If you keep your dependencies in a project, the Python tooling stack for 2026 shows how uv add records them in pyproject.toml.
Frequently Asked Questions
How do I install openpyxl?
Run pip install openpyxl, or uv add openpyxl in a uv project. To read and write Excel files with pandas as well, install both: pip install pandas openpyxl. Without openpyxl installed, pandas 3.1.1 stops with ModuleNotFoundError: No module named ‘openpyxl’ when you read or write an .xlsx file.
Should I use pandas or openpyxl for Excel files?
Use pandas when your data is a table that you want to analyse or save as a sheet. Use openpyxl when you need cell-level control: formulas, fonts, column widths, number formats or an existing workbook’s layout. They work together, because pandas uses openpyxl behind the scenes for .xlsx files.
Why do my Excel formulas come back as None or NaN?
openpyxl and pandas read the value Excel last stored for a formula cell. A file that openpyxl wrote has formulas but no stored results, so data_only=True returns None in openpyxl and pandas returns NaN. Open and save the file in Excel or LibreOffice to store the results, or calculate the values in Python instead.
Can pandas read old .xls files?
pandas can read .xls through the xlrd engine, but pandas 3.1.0 deprecated the xlrd and pyxlsb engines and recommends engine=‘calamine’ instead. openpyxl cannot read .xls at all; it raises an error telling you to use xlrd or convert the file to .xlsx.
How do I read a very large Excel file in Python?
Open it with openpyxl in read-only mode, load_workbook(path, read_only=True), and iterate over rows, closing the workbook when you finish. To write a very large file, use a write-only workbook and append rows. Both modes keep memory use close to constant according to openpyxl’s documentation.