Automating Excel Files with Python and openpyxl

Plenty of business processes still run on spreadsheets. Someone exports data every Monday, pastes it into a template, fixes the formatting, adds totals, and emails it around. If that someone is you, Python can take over the whole routine and produce the same workbook in a second, every time, without copy-paste mistakes.

openpyxl is the standard library for reading and writing modern Excel files (.xlsx and .xlsm) in Python. Unlike exporting a CSV, it gives you real workbooks: multiple sheets, formulas, number formats, colors, frozen headers, conditional formatting, tables, and native Excel charts.

This post builds a formatted sales report from scratch, then reads it back, adds a summary sheet with a chart, combines several files into one, and covers the gotchas that catch people out (especially around formulas and dates).

Installing openpyxl

python -m pip install openpyxl

openpyxl is pure Python and doesn't need Excel installed, so it runs fine on Linux servers and in containers. It handles .xlsx/.xlsm only. For legacy .xls files you'd need a different library such as xlrd, or convert them first.

The Object Model

Three objects cover almost everything:

• Workbook: the file. Create one with Workbook() or open one with load_workbook().
• Worksheet: a sheet (tab) in the workbook. Access by name with wb["Sales"] or get the active one with wb.active.
• Cell: a single cell, with a .value and styling attributes like .font and .number_format.

Cells can be addressed in two ways: ws["B3"] using Excel's A1 notation, or ws.cell(row=3, column=2) using 1-based row and column numbers. The numeric form is easier inside loops. openpyxl.utils has get_column_letter(28) (returns "AB") and column_index_from_string("AB") (returns 28) to convert between them.

Creating a Formatted Report

Here's a complete script that writes a sales report with headers, formulas, number formats, a totals row, column widths, and a frozen header row:

# build_report.py
from datetime import date

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side

sales = [
    ("North", "Widget", date(2026, 9, 1), 120, 4.50),
    ("North", "Gadget", date(2026, 9, 2), 35, 19.99),
    ("South", "Widget", date(2026, 9, 2), 200, 4.50),
    ("South", "Gizmo", date(2026, 9, 5), 12, 49.00),
    ("East", "Gadget", date(2026, 9, 8), 60, 19.99),
    ("West", "Gizmo", date(2026, 9, 9), 8, 49.00),
]

wb = Workbook()
ws = wb.active
ws.title = "Sales"

headers = ["Region", "Product", "Date", "Units", "Unit price", "Revenue"]
ws.append(headers)
for row_number, (region, product, day, units, price) in enumerate(sales, start=2):
    ws.append([region, product, day, units, price, f"=D{row_number}*E{row_number}"])

last_row = ws.max_row
ws.append(["Total", None, None, f"=SUM(D2:D{last_row})", None, f"=SUM(F2:F{last_row})"])
total_row = ws.max_row

header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(fill_type="solid", start_color="1F4E78")
thin = Side(style="thin", color="BFBFBF")

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

for row in ws.iter_rows(min_row=2, max_row=last_row):
    row[2].number_format = "yyyy-mm-dd"
    row[4].number_format = '"$"#,##0.00'
    row[5].number_format = '"$"#,##0.00'

for cell in ws[total_row]:
    cell.font = Font(bold=True)
    cell.border = Border(top=thin)
ws.cell(row=total_row, column=6).number_format = '"$"#,##0.00'

widths = {"A": 10, "B": 12, "C": 12, "D": 8, "E": 12, "F": 14}
for column, width in widths.items():
    ws.column_dimensions[column].width = width

ws.freeze_panes = "A2"

wb.save("sales_report.xlsx")
print(f"Wrote {last_row - 1} rows to sales_report.xlsx")

Wrote 6 rows to sales_report.xlsx

Let's walk through the important parts.

Writing Rows

ws.append(list) writes a list as the next row after the last used one. It's the simplest way to write tabular data. Python types map to Excel types automatically: int and float become numbers, str becomes text, date and datetime become Excel dates, and None leaves the cell empty.

ws.max_row tells you the last row with data, which is how the script knows where the data ends and where to put the totals.

Formulas

Any string starting with = is stored as a formula. "=D2*E2" computes revenue for row 2, and "=SUM(D2:D7)" totals a column. Writing formulas instead of precomputed numbers keeps the workbook live: if someone edits a unit count in Excel, the totals update.

Use English function names and commas as separators (=SUM(A1,B1)), regardless of the language Excel is set to. That's how formulas are stored in the file format.

There's a catch covered later: openpyxl writes formulas but never calculates them.

Styles

Styling is done by assigning style objects to cell attributes:

Attribute	Object	Example
cell.font	Font	Font(bold=True, color="FFFFFF", size=12)
cell.fill	PatternFill	PatternFill(fill_type="solid", start_color="1F4E78")
cell.border	Border + Side	Border(top=Side(style="thin"))
cell.alignment	Alignment	Alignment(horizontal="center", wrap_text=True)
cell.number_format	string	"0.0%", "yyyy-mm-dd"

Colors are hex RGB strings without a #. Style objects are immutable: to change one property of an existing font, assign a new Font rather than modifying cell.font in place. Iterating ws[1] gives you the cells of row 1, which is a convenient way to style a header.

Number Formats

number_format uses Excel's own format codes, the same ones you'd type in Excel's "Custom" format dialog. Some useful ones:

• '"$"#,##0.00' for currency with thousands separators
• "0.0%" for percentages (the cell value should be 0.25, not 25)
• "yyyy-mm-dd" or "dd/mm/yyyy" for dates
• "#,##0" for whole numbers with separators

Formatting changes only how a value is displayed. The underlying number stays exact, which is what you want for further calculations.

Column Widths and Freeze Panes

ws.column_dimensions["A"].width sets a column width in Excel's character-width units. openpyxl can't auto-fit columns because it doesn't render text, so set widths explicitly, or estimate them from the longest value in each column.

ws.freeze_panes = "A2" freezes everything above and to the left of A2, which keeps the header row visible while scrolling. "B2" would freeze the header row and the first column.

Reading a Workbook

# read_report.py
from openpyxl import load_workbook

wb = load_workbook("sales_report.xlsx")
ws = wb["Sales"]
print(wb.sheetnames, ws.dimensions, ws.max_row, ws.max_column)
print(ws["A2"].value, ws["C2"].value, ws["F2"].value)

for region, product, day, units, price, revenue in ws.iter_rows(
    min_row=2, max_row=4, values_only=True
):
    print(region, product, day.date(), units, price, revenue)

['Sales'] A1:F8 8 6
North 2026-09-01 00:00:00 =D2*E2
North Widget 2026-09-01 120 4.5 =D2*E2
North Gadget 2026-09-02 35 19.99 =D3*E3
South Widget 2026-09-02 200 4.5 =D4*E4

iter_rows() is the workhorse for reading. With values_only=True it yields plain tuples of values instead of Cell objects, which makes unpacking easy. min_row, max_row, min_col, and max_col limit the range. There's also iter_cols() if you need to go column by column.

Two things in that output deserve attention.

Dates Come Back as datetime

The script wrote date(2026, 9, 1) but reads back 2026-09-01 00:00:00. Excel has no separate date type; a date is a number formatted as a date, and openpyxl converts any cell with a date format to a datetime. Call .date() if you need a plain date.

Formulas Come Back as Formulas

ws["F2"].value is the string "=D2*E2", not a number. openpyxl doesn't have a calculation engine. When Excel saves a file, it stores both the formula and the last calculated result, and you can read those cached results with data_only=True:

wb = load_workbook("report_saved_by_excel.xlsx", data_only=True)

But a file created by openpyxl has never been opened in Excel, so there are no cached results, and data_only=True returns None for every formula cell. The same applies to pandas: pd.read_excel() on a freshly generated file shows NaN for formula columns.

The practical rules:

• If a Python process needs the number, compute it in Python and write the value, or compute it in Python as well as writing the formula.
• Use formulas for workbooks that people will open and edit in Excel.
• Be careful with data_only=True when you also save: loading in that mode and saving overwrites every formula with its cached value.

Updating a Workbook: Summary Sheet, Conditional Formatting, Table, and Chart

Opening an existing file, changing it, and saving is the core of most automation. This script adds conditional formatting to the sales sheet and a new summary sheet with an Excel table and a bar chart:

# add_extras.py
from collections import defaultdict

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule
from openpyxl.styles import Font, PatternFill
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = load_workbook("sales_report.xlsx")
ws = wb["Sales"]
last_row = ws.max_row - 1  # skip the totals row

# Highlight big orders and color-scale revenue
ws.conditional_formatting.add(
    f"D2:D{last_row}",
    CellIsRule(
        operator="greaterThan",
        formula=["100"],
        fill=PatternFill(fill_type="solid", start_color="C6EFCE"),
        font=Font(color="006100"),
    ),
)
ws.conditional_formatting.add(
    f"F2:F{last_row}",
    ColorScaleRule(start_type="min", start_color="F8696B", end_type="max", end_color="63BE7B"),
)

# Summary sheet computed in Python
revenue_by_region: dict[str, float] = defaultdict(float)
for region, _, _, units, price, _ in ws.iter_rows(min_row=2, max_row=last_row, values_only=True):
    revenue_by_region[region] += units * price

summary = wb.create_sheet("Summary")
summary.append(["Region", "Revenue"])
for region, revenue in sorted(revenue_by_region.items()):
    summary.append([region, round(revenue, 2)])
for cell in summary["B"][1:]:
    cell.number_format = '"$"#,##0.00'
summary.column_dimensions["B"].width = 14

table = Table(displayName="RegionRevenue", ref=f"A1:B{summary.max_row}")
table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
summary.add_table(table)

chart = BarChart()
chart.title = "Revenue by region"
chart.y_axis.title = "Revenue ($)"
data = Reference(summary, min_col=2, min_row=1, max_row=summary.max_row)
categories = Reference(summary, min_col=1, min_row=2, max_row=summary.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.width, chart.height = 16, 8
summary.add_chart(chart, "D2")

# Restrict the Region column to a dropdown
dv = DataValidation(type="list", formula1='"North,South,East,West"', allow_blank=False)
ws.add_data_validation(dv)
dv.add(f"A2:A{last_row}")

wb.move_sheet("Summary", offset=-1)
wb.active = 0
wb.save("sales_report.xlsx")
print(wb.sheetnames)

['Summary', 'Sales']

What each section does:

• Conditional formatting is evaluated by Excel when the file is opened, so it keeps working as values change. CellIsRule applies a style when a comparison is true; ColorScaleRule shades cells on a gradient from min to max. There are also FormulaRule (any formula) and DataBarRule.
• The summary is computed in Python because the revenue cells hold formulas with no cached values, as explained above. That's the formula rule in action: compute the numbers you need in code.
• Table turns a range into an Excel table with filter buttons and banded rows. displayName must be unique in the workbook and can't contain spaces.
• BarChart creates a native Excel chart that references cells. Reference describes a range; titles_from_data=True uses the first cell (the "Revenue" header) as the series name. LineChart, PieChart, ScatterChart, and AreaChart work the same way.
• DataValidation adds a dropdown so people editing the sheet can only pick a valid region.
• move_sheet reorders tabs, and setting wb.active = 0 makes the summary the sheet that opens first.

Saving to the same filename overwrites the original. During development, saving to a new name is safer.

Processing Many Files

A common chore: a folder of monthly files that need to become one. Combining them is a loop over pathlib paths:

# combine_files.py
from pathlib import Path

from openpyxl import Workbook, load_workbook

combined = Workbook()
out = combined.active
out.title = "All months"
out.append(["Month", "Name", "Tickets"])

for path in sorted(Path("monthly").glob("*.xlsx")):
    wb = load_workbook(path, read_only=True)
    ws = wb.active
    for name, tickets in ws.iter_rows(min_row=2, values_only=True):
        out.append([path.stem, name, tickets])
    wb.close()

combined.save("combined.xlsx")
print(f"Combined {out.max_row - 1} rows")

With monthly/jan.xlsx and monthly/feb.xlsx each holding two rows, this prints Combined 4 rows. path.stem (the filename without extension) records which file each row came from.

read_only=True opens the file in a streaming mode that uses far less memory, which matters for large inputs. Read-only workbooks must be closed explicitly with wb.close() because they keep the file handle open.

Large Files: Read-Only and Write-Only Modes

openpyxl loads the whole workbook into memory as Python objects by default, and that gets heavy beyond a few hundred thousand cells. Two modes help:

# write_large.py
from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Events")
ws.append(["id", "value"])
for i in range(100_000):
    ws.append([i, i * 2])
wb.save("large.xlsx")

A write-only workbook streams rows to disk as you append them. It starts with no sheets (so use create_sheet), you can only append, and you can't go back and edit earlier cells. In exchange, memory stays flat regardless of size.

Mode	Use it for	Limitations
Default	Editing, styling, small to medium files	Memory grows with cell count
read_only=True	Reading big files	No editing; call close()
write_only=True	Generating big files	Append only; no random access

openpyxl and pandas

If your data is already in a DataFrame, pandas can write it to Excel using openpyxl under the hood, and then you can reach in for formatting:

import pandas as pd

df = pd.DataFrame({"region": ["North", "South"], "revenue": [1239.65, 1488.0]})

with pd.ExcelWriter("pandas_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Data", index=False)
    sheet = writer.sheets["Data"]
    sheet.freeze_panes = "A2"
    sheet.column_dimensions["B"].width = 14

writer.sheets["Data"] is a regular openpyxl worksheet, so every technique in this post applies. A good split is pandas for reshaping and aggregating data and openpyxl for presentation. For more on working with DataFrames, see using Python for data analysis.

Common Gotchas

• Formulas aren't calculated. Covered above, and it's the number one surprise. Compute values in Python when your code needs them.
• Saving can drop things openpyxl doesn't understand. openpyxl doesn't read every feature of the file format, so shapes, form controls, and some advanced objects in an existing workbook can be lost on a load and save round trip. Test on a copy before automating changes to a complex template.
• Macros. To keep VBA in an .xlsm file, load it with load_workbook(path, keep_vba=True) and save it with the .xlsm extension. openpyxl can't run or edit the macros.
• Merged cells. ws.merge_cells("A10:F10") merges a range; only the top-left cell holds a value.
• The file is open in Excel. On Windows, saving over a file that's open in Excel raises PermissionError.
• Rows and columns are 1-based. ws.cell(row=0, column=0) raises an error.

Conclusion

openpyxl turns repetitive spreadsheet work into a script: Workbook and load_workbook to create and open files, append and iter_rows to write and read data, style objects and number formats for presentation, and conditional formatting, tables, and charts for the finishing touches that make a report look hand-built.

Keep the formula limitation in mind and compute anything your code depends on in Python. Use read-only and write-only modes for big files, and pair openpyxl with pandas when the data work gets heavy. Once the script exists, the Monday-morning report becomes a single command, and something you can schedule to run on its own.