Automate Excel with Python (openpyxl) — A Beginner's Guide

Be honest: how much of your time goes into Excel busywork? Copying rows between sheets, re-formatting the same report every Monday, typing the same formulas into column D over and over.

It's not that the work is hard. It's that it's repetitive — and repetitive work is exactly what computers are for. In this guide, you'll learn to automate Excel with Python using a library called openpyxl. By the end, you'll have a script that turns a raw data file into a formatted report and emails it — no mouse involved.

If you've already read our Send Emails with Python guide, you'll recognize the final step: the report your script builds becomes the attachment in an automated email. That's the whole pipeline.

What Is openpyxl?

openpyxl is a free Python library that reads and writes Excel files (.xlsx format). It's the tool that lets your script do everything you'd normally do by hand: create sheets, fill in cells, add formulas, apply colors, and save the file.

The key advantage: you don't need Excel installed. Your Python script builds the .xlsx file from scratch, so it runs anywhere — including a headless server in a closet.

A good way to think about it: Excel is the office. openpyxl is the robot that goes into the office at night, does the paperwork, and leaves a finished report on your desk by morning.

One honest limitation up front: openpyxl works with formulas and formatting, but it can't run VBA macros or recalculate formulas (more on that later). For 95% of reporting tasks, that doesn't matter.

Install openpyxl

One command:

pip install openpyxl

Verify it worked:

python3 -c "import openpyxl; print(openpyxl.__version__)"
# Output: 3.1.5

That's it. No Excel, no licenses, no setup wizard.

Reading an Existing Spreadsheet

Before writing anything, let's read one. Say you have sales.xlsx with columns Product, Amount, Date:

from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")   # open the file
ws = wb.active                     # the sheet Excel shows first

# Cells are addressed like in Excel: "A1", "B2"...
print(ws["A1"].value)              # Output: Product

# Loop through every data row (skip the header row)
for row in ws.iter_rows(min_row=2, values_only=True):
    product, amount, date = row
    print(f"{product}: ${amount} on {date}")

Output:

Product
Keyboard: $45.99 on 2026-08-01
Monitor: $189 on 2026-08-02
Mouse: $12.5 on 2026-08-03

Numbers come out in Python's natural style — 189 (not 189.00), 12.5 (not 12.50). If you want consistent money formatting, use {amount:.2f} in the f-string (for numeric cells).

iter_rows() with values_only=True is the pattern you'll use constantly — it hands you plain Python values instead of cell objects, which is what you want 90% of the time.

Writing Your First Spreadsheet

The reverse direction is just as simple:

from openpyxl import Workbook

wb = Workbook()          # a brand-new file in memory
ws = wb.active
ws.title = "July Sales"  # rename the default sheet

# Header row
ws["A1"] = "Product"
ws["B1"] = "Amount"

# Data
ws.append(["Keyboard", 45.99])
ws.append(["Monitor", 189.00])

wb.save("july-sales.xlsx")
print("Created july-sales.xlsx")

ws.append() adds a row at the end automatically — much cleaner than tracking row numbers yourself.

Formatting: Make It Look Professional

A raw grid of numbers looks like a raw grid of numbers. Twenty lines of code turn it into a real report:

from openpyxl.styles import Font, PatternFill, Alignment

# Bold header with white text on blue
for cell in ws[1]:  # row 1, every column
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="0071E3")
    cell.alignment = Alignment(horizontal="center")

# Money format with 2 decimals
for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        cell.number_format = '"$"#,##0.00'

# Sensible column widths
ws.column_dimensions["A"].width = 25
ws.column_dimensions["B"].width = 12

# Keep the header visible when scrolling
ws.freeze_panes = "A2"

wb.save("july-sales.xlsx")

The colors take hex codes, so you can match your company branding. One line, done — no clicking through the ribbon ten times.

Writing Formulas

Formulas are just strings:

ws["C2"] = "=B2*0.1"      # 10% tax
ws["D2"] = "=SUM(B2:B10)" # total

The formula sits in the cell, and Excel calculates it when the file opens. But here's the catch we promised earlier: openpyxl never computes formula results. If you write a formula with openpyxl and then read it back with openpyxl, you'll get the formula string — or worse, None.

To read calculated values, open the file once in Excel (or LibreOffice) and save it, then read with:

wb = load_workbook("sales.xlsx", data_only=True)
ws = wb.active
print(ws["D2"].value)  # Output: 400.23 (the actual number)

If data_only=True returns None, it means Excel never opened the file to bake in the results. Open it once manually, save, and it works.

Adding Charts (Yes, Python Can Do That Too)

Reports without charts are just numbers. openpyxl can embed real Excel charts:

from openpyxl.chart import BarChart, Reference

# Point at the data: values in column B (rows 1-10), labels in column A
data = Reference(ws, min_col=2, min_row=1, max_row=10)
labels = Reference(ws, min_col=1, min_row=2, max_row=10)

chart = BarChart()
chart.title = "Sales by Product"
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)

ws.add_chart(chart, "E2")   # place the chart at cell E2
wb.save("july-sales.xlsx")

Open the file and the chart is sitting there, ready for your boss's Monday meeting. Bar, line, pie, and scatter charts are all supported.

Real Project: CSV → Formatted Excel Report

Time to put it together. Here's a realistic job: every week you download a CSV of order data, and every week you format it by hand into a report. Let's kill that.

"""weekly-report.py — Turn orders.csv into a formatted Excel report."""
import csv
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

# ── 1. Read the raw CSV ──
with open("orders.csv") as f:
    rows = list(csv.reader(f))

header, data = rows[0], rows[1:]

# ── 2. Build the workbook ──
wb = Workbook()
ws = wb.active
ws.title = "Weekly Report"

ws.append(header)
for row in data:
    ws.append(row)

# ── 3. Style the header ──
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="0071E3")

# ── 4. Add a totals row with a formula ──
last_row = len(data) + 1
ws.cell(row=last_row + 2, column=1, value="TOTAL")
ws.cell(row=last_row + 2, column=2,
        value=f"=SUM(B2:B{last_row})").font = Font(bold=True)

# ── 5. Widths + freeze header ──
ws.column_dimensions["A"].width = 25
ws.column_dimensions["B"].width = 12
ws.freeze_panes = "A2"

wb.save("weekly-report.xlsx")
print("Report saved: weekly-report.xlsx")

Run it:

python3 weekly-report.py
# Report saved: weekly-report.xlsx

Ten seconds of work replaces an hour of Monday-morning formatting. If you combine this with our email automation guide, the same script can attach the file and send it to your boss before you're out of bed — and with cron, it runs itself every Monday at 8 AM.

openpyxl Cheat Sheet

openpyxl vs pandas — Which One?

You've probably seen pandas recommended for Excel work, and you might wonder which to learn. The honest answer: they solve different halves of the problem.

The classic combo: use pandas to crunch the numbers, then hand the result to openpyxl to make it look good. If you only care about the data, pandas alone is fine. If you need a polished report with formulas and colors, openpyxl is the tool — and it's easier to learn as your first Excel automation step.

Common Pitfalls

1. PermissionError when saving — the file is open in Excel. Close it first. (Or save to a new filename.)
2. data_only=True returns None — formulas haven't been calculated yet. Open the file in Excel once and save.
3. Old .xls files won't open — openpyxl only supports .xlsx (Excel 2007+). If you're stuck with .xls, convert it once in Excel and you're set.
4. Forgetting to call wb.save() — everything happens in memory until you save. No save, no file.

What's Next?

Excel automation is the middle piece of a classic pipeline: collect data → process it → deliver it. You now own the middle. The other pieces are already written:

• Send Emails with Python — attach your new report to an automated email
• Python Web Scraping for Beginners — scrape prices or data, then drop them straight into a spreadsheet
• Linux Cron Jobs for Beginners — run your report script every Monday morning automatically
• Organize Your Downloads Folder with Python — sort the CSV files that pile up in your downloads

Start small: automate one spreadsheet you already maintain by hand. The first time you watch a script produce your Monday report in ten seconds, you'll never go back.

All code in this article was tested and runs successfully on Python 3.8.10 (Ubuntu 20.04) with openpyxl 3.1.5 — verified August 2026.