Excel Automation with pandas and openpyxl

Whatever tools your company uses for analysis, the finished product often travels as an Excel file. Managers open it, finance copies numbers from it and a client expects it in their inbox on the first of the month. Many analysts spend hours every week rebuilding the same workbook by hand: paste the data, format the header, add a total, make the chart, save and send. Python can do that in seconds, identically, every time.

Two libraries do the work. pandas reads and writes Excel in one line (read_excel, to_excel) and is perfect for data. openpyxl gives you control of the workbook itself: fonts, colours, column widths, number formats, formulas, conditional formatting and charts. In this lesson we use both to produce a monthly FreshBasket city report: an Excel file that looks finished, with a styled header, rupee formatting, live totals, colour scales and a bar chart, and we also learn to read the awkward Excel files other people send us.

Everything is written to an out/ folder, so you can open the generated files in Excel, LibreOffice or Google Sheets and check the result yourself.

Worked examples

Example 1: DataFrame to Excel in one line

Question: How do we export a city summary for June 2026?

import pandas as pd
from pathlib import Path

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items = pd.read_csv("order_items.csv")
stores = pd.read_csv("stores.csv")

value = (items["quantity"] * items["unit_price"]).groupby(items["order_id"]).sum().rename("value")
d = orders.merge(value, on="order_id").merge(stores[["store_id", "city"]], on="store_id")
june = d[(d["order_date"] >= "2026-06-01") & (d["order_status"] == "Delivered")]

summary = (
    june.groupby("city")
    .agg(orders=("order_id", "count"), revenue=("value", "sum"), avg_minutes=("delivery_minutes", "mean"))
    .round(1)
    .sort_values("revenue", ascending=False)
    .reset_index()
)

Path("out").mkdir(exist_ok=True)
summary.to_excel("out/june_summary.xlsx", sheet_name="Summary", index=False)
print(summary)
print(Path("out/june_summary.xlsx").stat().st_size > 4000)

        city  orders  revenue  avg_minutes
0      Delhi     115  74254.0         24.0
1  Bengaluru     102  66532.0         24.0
2     Mumbai     100  64490.0         22.9
3  Hyderabad      78  56698.0         23.8
4       Pune      57  46983.0         23.8
5    Chennai      40  29245.0         23.6
6    Kolkata      31  25506.0         23.5
7  Ahmedabad      39  25163.0         24.6
True

How it works: DataFrame.to_excel(path, sheet_name=..., index=False) writes a workbook (it uses openpyxl behind the scenes for .xlsx files). index=False leaves out the row-number column that you rarely want in a report. We first prepared a tidy summary: delivered June orders, grouped by city, sorted by revenue.

Reading the result: The workbook has 8 city rows. The top city is Delhi with ₹74,254 from 115 orders.

Example 2: Several sheets in one workbook

Question: How do we put the summary and the raw data into one file?

payment = june.groupby("payment_method").agg(orders=("order_id", "count"), revenue=("value", "sum")).reset_index()

with pd.ExcelWriter("out/june_report.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="By city", index=False)
    payment.to_excel(writer, sheet_name="By payment", index=False)
    june[["order_id", "order_date", "city", "payment_method", "value"]].to_excel(writer, sheet_name="Orders", index=False)

xl = pd.ExcelFile("out/june_report.xlsx")
print(xl.sheet_names)
print({name: xl.parse(name).shape for name in xl.sheet_names})

['By city', 'By payment', 'Orders']
{'By city': (8, 4), 'By payment': (4, 3), 'Orders': (562, 5)}

How it works: ExcelWriter keeps one workbook open while you write several DataFrames, each into its own sheet; the with block saves and closes the file at the end. pd.ExcelFile opens an existing workbook so you can list sheet_names and parse any sheet without re-reading the file each time.

Reading the result: The workbook has the sheets ['By city', 'By payment', 'Orders']. The raw Orders sheet holds 562 rows, matching the 562 delivered June orders.

Example 3: Reading Excel back with pandas

Question: What options matter when reading a sheet?

back = pd.read_excel("out/june_report.xlsx", sheet_name="Orders", usecols=["order_id", "order_date", "value"], nrows=5)
print(back)
print(back.dtypes.to_dict())

whole = pd.read_excel("out/june_report.xlsx", sheet_name="Orders", dtype={"order_id": str}, parse_dates=["order_date"])
print(whole["order_date"].min().date(), whole["order_date"].max().date())
print(round(whole["value"].sum(), 0) == round(june["value"].sum(), 0))

  order_id order_date  value
0   O01188 2026-06-01    340
1   O01782 2026-06-01    117
2   O01900 2026-06-01    519
3   O02090 2026-06-01    989
4   O02242 2026-06-01     97
{'order_id': <StringDtype(storage='python', na_value=nan)>, 'order_date': dtype('<M8[us]'), 'value': dtype('int64')}
2026-06-01 2026-06-30
True

How it works: read_excel takes sheet_name (a name or number; None returns all sheets as a dictionary), usecols to pick columns, nrows to limit rows, dtype to force types (important for IDs, so that "00123" is not read as 123) and parse_dates. The final line is a round-trip check: what we read back sums to what we wrote.

Reading the result: The June data runs from 2026-06-01 to 2026-06-30 and totals ₹388,871, the same as the original.

Example 4: Open the workbook with openpyxl

Question: How do we inspect cells and ranges directly?

from openpyxl import load_workbook

wb = load_workbook("out/june_report.xlsx")
ws = wb["By city"]

print(wb.sheetnames)
print(ws.dimensions, ws.max_row, ws.max_column)
print(ws["A1"].value, ws["B1"].value, "|", ws["A2"].value, ws["C2"].value)

for row in ws.iter_rows(min_row=2, max_row=4, values_only=True):
    print(row)

['By city', 'By payment', 'Orders']
A1:D9 9 4
city orders | Delhi 74254
('Delhi', 115, 74254, 24)
('Bengaluru', 102, 66532, 24)
('Mumbai', 100, 64490, 22.9)

How it works: load_workbook opens a file for editing. Sheets are looked up by name (wb["By city"]), and cells by address (ws["A2"]) or by ws.cell(row=2, column=1). iter_rows(values_only=True) yields each row as a tuple, which is perfect for loops. Row and column numbers in openpyxl start at 1, like Excel (not 0, like Python lists).

Reading the result: The sheet occupies the range A1:D9 (9 rows including the header and 4 columns). Cell A2 holds "Delhi".

Example 5: Style the header and format numbers

Question: How do we make the sheet look professional?

from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

ws = wb["By city"]
header_fill = PatternFill("solid", start_color="028090")
thin = Side(style="thin", color="D0D5DD")

for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")

for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    for cell in row:
        cell.border = Border(bottom=thin)
    row[2].number_format = '"₹"#,##0'      # revenue
    row[3].number_format = "0.0"           # minutes

for i, width in enumerate([16, 10, 14, 14], start=1):
    ws.column_dimensions[get_column_letter(i)].width = width

ws.freeze_panes = "A2"
ws.auto_filter.ref = ws.dimensions
print(ws["C2"].number_format, ws.freeze_panes, ws.auto_filter.ref)

"₹"#,##0 A2 A1:D9

How it works: Each cell has font, fill, alignment, border and number_format properties. A number format changes how a number looks without changing its value: '"₹"#,##0' shows 25000 as ₹25,000. freeze_panes = "A2" keeps the header visible when scrolling, and auto_filter adds the dropdown filter arrows. Column widths are set per column letter, and get_column_letter(1) converts 1 to "A".

Reading the result: The revenue column uses the format "₹"#,##0, the panes are frozen at A2 and the filter covers A1:D9.

Example 6: Formulas and a totals row

Question: How do we add a live total that updates when someone edits the numbers?

last = ws.max_row
total_row = last + 1
ws.cell(row=total_row, column=1, value="Total").font = Font(bold=True)
ws.cell(row=total_row, column=2, value=f"=SUM(B2:B{last})")
ws.cell(row=total_row, column=3, value=f"=SUM(C2:C{last})").number_format = '"₹"#,##0'
ws.cell(row=total_row, column=4, value=f"=AVERAGE(D2:D{last})").number_format = "0.0"
for cell in ws[total_row]:
    cell.font = Font(bold=True)
    cell.border = Border(top=Side(style="medium", color="264653"))

print(ws.cell(row=total_row, column=3).value)
print(ws.cell(row=total_row, column=3).data_type)
print("pandas total:", round(summary["revenue"].sum(), 0))

=SUM(C2:C9)
f
pandas total: 388871.0

How it works: Writing a string that starts with = stores a formula, so Excel recalculates it whenever the numbers change. A caution: openpyxl writes the formula but does not calculate it. If you read the file back with data_only=True before opening it in Excel, the cell shows None. That is why the last line computes the expected total independently in pandas: you verify the logic in Python and let Excel display the live version. data_type "f" confirms the cell holds a formula.

Reading the result: The total cell contains =SUM(C2:C9) with data type "f", and Excel will display the pandas figure ₹388,871 when it opens the file.

Example 7: Conditional formatting

Question: How do we highlight the best and the slowest cities automatically?

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule

rng = f"C2:C{last}"
ws.conditional_formatting.add(rng, ColorScaleRule(start_type="min", start_color="FFFFFF", end_type="max", end_color="2A9D8F"))

slow_rule = CellIsRule(operator="greaterThan", formula=["24"], font=Font(color="C0392B", bold=True))
ws.conditional_formatting.add(f"D2:D{last}", slow_rule)

print(len(ws.conditional_formatting))
for cf in ws.conditional_formatting:
    print(cf.sqref, [r.type for r in cf.rules])

2
C2:C9 ['colorScale']
D2:D9 ['cellIs']

How it works: Conditional formatting colours cells according to their values, evaluated live by Excel. ColorScaleRule shades revenue from white (lowest) to teal (highest), so the best city stands out. CellIsRule applies a style only to cells that meet a condition: here, average delivery above 24 minutes turns red and bold. Because the rules live in the workbook, they keep working when someone updates the numbers.

Reading the result: The sheet now has 2 conditional-format ranges: a colour scale on revenue and a threshold rule on delivery minutes. 1 of the 8 cities currently average more than 24 minutes.

Example 8: A native Excel chart

Question: How do we add a chart that updates with the data?

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.type = "bar"
chart.title = "June revenue by city"
chart.y_axis.title = "Revenue (Rs)"
chart.style = 10

data = Reference(ws, min_col=3, min_row=1, max_row=last)
cats = Reference(ws, min_col=1, min_row=2, max_row=last)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.legend = None
chart.height, chart.width = 8, 16
ws.add_chart(chart, "G2")

wb.save("out/june_report_styled.xlsx")
print(len(ws._charts), "chart(s) on the sheet")
print(Path("out/june_report_styled.xlsx").exists())

1 chart(s) on the sheet
True

How it works: An openpyxl chart is built from References to cell ranges: one for the values (including the header, used as the series name) and one for the category labels. add_chart(chart, "G2") anchors it at cell G2. Because it is a real Excel chart, the reader can click it, restyle it and watch it update with the data. wb.save writes the workbook; remember that nothing changes on disk until you save.

Reading the result: The sheet now holds 1 chart anchored at G2, and the styled workbook has been saved with the formatting, formulas, conditional rules and chart from the last few examples.

Example 9: Reading a messy Excel report

Question: A colleague sends a workbook with a title block above the table and a footer below it. How do we read just the data?

from openpyxl import Workbook

wbm = Workbook()
wsm = wbm.active
wsm.title = "Report"
wsm["A1"] = "FreshBasket - Store performance"
wsm["A2"] = "Prepared by: Operations"
wsm.append([])
wsm.append(["Store", "Orders", "Revenue"])
for row in [("Bengaluru North", 210, 143500), ("Mumbai West", 185, 120300), ("Delhi Central", 199, 131900)]:
    wsm.append(row)
wsm.append([])
wsm.append(["Source: internal systems", None, None])
wbm.save("out/messy_report.xlsx")

raw = pd.read_excel("out/messy_report.xlsx", header=None)
print(raw.shape)

clean = pd.read_excel("out/messy_report.xlsx", header=3, usecols="A:C", nrows=3)
print(clean)
print(clean.dtypes.to_dict())

(9, 3)
             Store  Orders  Revenue
0  Bengaluru North     210   143500
1      Mumbai West     185   120300
2    Delhi Central     199   131900
{'Store': <StringDtype(storage='python', na_value=nan)>, 'Orders': dtype('int64'), 'Revenue': dtype('int64')}

How it works: Reading with header=None shows the sheet as it really is, so you can count the junk rows. Then header=3 says "the column names are in row 4 (counting from 0)", usecols="A:C" takes Excel-style column letters, and nrows=3 stops before the footer. For files that change shape, a more robust approach is to search for the row that contains "Store" and use its position, rather than hard-coding the number.

Reading the result: The raw sheet has 9 rows including titles and footers; the clean table has 3 stores and 594 orders in total.

Example 10: One function that builds the finished report

Question: How do we regenerate the report for any month, with one call?

def build_city_report(month, path):
    start = pd.Period(month).to_timestamp()
    end = (pd.Period(month) + 1).to_timestamp()
    m = d[(d["order_date"] >= start) & (d["order_date"] < end) & (d["order_status"] == "Delivered")]
    table = (
        m.groupby("city").agg(orders=("order_id", "count"), revenue=("value", "sum"), avg_minutes=("delivery_minutes", "mean"))
        .round(1).sort_values("revenue", ascending=False).reset_index()
    )
    table.to_excel(path, sheet_name="By city", index=False)

    wb_ = load_workbook(path)
    ws_ = wb_["By city"]
    for cell in ws_[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill("solid", start_color="028090")
    for row in ws_.iter_rows(min_row=2):
        row[2].number_format = '"₹"#,##0'
    n = ws_.max_row
    ws_.append(["Total", f"=SUM(B2:B{n})", f"=SUM(C2:C{n})", f"=AVERAGE(D2:D{n})"])
    ws_.freeze_panes = "A2"
    for col, w in zip("ABCD", (16, 10, 14, 14)):
        ws_.column_dimensions[col].width = w
    wb_.save(path)
    return table

for month in ["2026-04", "2026-05", "2026-06"]:
    t = build_city_report(month, f"out/report_{month}.xlsx")
    print(month, len(t), "cities", f"revenue {t['revenue'].sum():,.0f}")

print(sorted(p.name for p in Path("out").glob("report_*.xlsx")))

2026-04 8 cities revenue 376,434
2026-05 8 cities revenue 362,430
2026-06 8 cities revenue 388,871
['report_2026-04.xlsx', 'report_2026-05.xlsx', 'report_2026-06.xlsx']

How it works: Everything we did by hand is now a function of the month and the output path: filter, summarise, export, style, add formulas, save. Calling it in a loop produces three identical-looking reports. Put the function in a script, schedule it (the next lesson shows how) and the monthly report builds itself. The only thing left for a human is to read it.

Reading the result: Three workbooks were created. June's revenue was ₹388,871 across 8 cities, and each file has styled headers, rupee formats and a live totals row.

Common mistakes

1. Expecting openpyxl to calculate formulas. It stores them; Excel (or LibreOffice) computes them when the file is opened. Reading with data_only=True before that gives None.

2. Overwriting the only copy of a workbook. Save edited workbooks under a new name until you trust the script.

3. Losing charts, images or pivot tables by loading and re-saving a workbook that contains features openpyxl does not fully support. Test on a copy first.

4. Forgetting that Excel rows and columns start at 1, not 0.

5. Writing a huge DataFrame cell by cell. Use to_excel; it is far faster, then style only the header and a few columns.

load_workbook("out/does_not_exist.xlsx")

Traceback (most recent call last):
  File "example.py", line 1, in <module>
    load_workbook("out/does_not_exist.xlsx")
    ~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 346, in load_workbook
    reader = ExcelReader(filename, read_only, keep_vba,
                         data_only, keep_links, rich_text)
  File "example.py", line 123, in __init__
    self.archive = _validate_archive(fn)
                   ~~~~~~~~~~~~~~~~~^^^^
  File "example.py", line 95, in _validate_archive
    archive = ZipFile(filename, 'r')
  File "example.py", line 1414, in __init__
    self.fp = io.open(file, filemode)
              ~~~~~~~^^^^^^^^^^^^^^^^
FileNotFoundError: [Errno 2] No such file or directory: 'out/does_not_exist.xlsx'

Practice questions

Practice 1 (Easy): Export the top five products

Write the five best-selling products by units in June to a workbook out/top_products.xlsx.

products = pd.read_csv("products.csv")
june_items = items.merge(june[["order_id"]], on="order_id").merge(products[["product_id", "product_name"]], on="product_id")
top5 = june_items.groupby("product_name")["quantity"].sum().nlargest(5).reset_index()
top5.to_excel("out/top_products.xlsx", index=False)
print(top5)

             product_name  quantity
0             Paneer 200g       265
1           Spinach Bunch       247
2  Instant Noodles 4-pack       199
3           Detergent 2kg       193
4              Besan 500g       171

Practice 2 (Medium): Highlight the best row

Open out/june_report_styled.xlsx, fill the whole row of the city with the highest revenue in light yellow, and save it as out/june_report_highlight.xlsx.

wb2 = load_workbook("out/june_report_styled.xlsx")
ws2 = wb2["By city"]
best_row = max(range(2, ws2.max_row), key=lambda r: ws2.cell(row=r, column=3).value)
for cell in ws2[best_row]:
    cell.fill = PatternFill("solid", start_color="FFF3B0")
wb2.save("out/june_report_highlight.xlsx")
print(ws2.cell(row=best_row, column=1).value, ws2.cell(row=best_row, column=3).value)

Delhi 74254

Practice 3 (Hard): One sheet per city

Create a workbook with one sheet per city containing that city's June orders, plus an Index sheet listing each city and its order count with a hyperlink to its sheet.

wb3 = Workbook()
index = wb3.active
index.title = "Index"
index.append(["City", "Orders"])

for city, g in june.groupby("city"):
    sheet = wb3.create_sheet(title=city[:31])
    sheet.append(["order_id", "order_date", "payment_method", "value"])
    for r in g[["order_id", "order_date", "payment_method", "value"]].itertuples(index=False):
        sheet.append([r.order_id, r.order_date.to_pydatetime(), r.payment_method, r.value])
    index.append([city, len(g)])
    link_cell = index.cell(row=index.max_row, column=1)
    link_cell.hyperlink = f"#'{city[:31]}'!A1"
    link_cell.style = "Hyperlink"

wb3.save("out/orders_by_city.xlsx")
print(wb3.sheetnames)
print(sum(wb3[n].max_row - 1 for n in wb3.sheetnames[1:]), len(june))

['Index', 'Ahmedabad', 'Bengaluru', 'Chennai', 'Delhi', 'Hyderabad', 'Kolkata', 'Mumbai', 'Pune']
562 562

Key takeaways

• to_excel and read_excel handle data; ExcelWriter writes several sheets.
• openpyxl exposes the workbook: sheets, cells, styles, number formats, freeze panes and filters.
• Formulas are stored as strings and calculated by Excel; verify the numbers independently in pandas.
• Conditional formatting and native charts keep updating when the data changes.
• Read messy files with header=, usecols=, nrows= and dtype=, after looking at the raw sheet with header=None.
• Wrap the whole job in a function of the month and the path so the report builds itself.