Automating Excel with Python in 2026: 7 real workflows that save 2-4 hours every week

If you spend more than two hours a week clicking through Excel — copying columns, formatting rows, merging files, reading reports — Python will hand those hours back. Not in some abstract "you should learn to code" way. In a "ship a 40-line script this weekend, run it every Monday morning forever" way.

This article is seven concrete automations. Each has a real business case, the Python libraries you need, and a code skeleton you can copy. Pick one that matches what you actually do at work. Build it. The next six are easier.

1. Merge 20 daily reports into one master file

The pain: Every morning HR drops twenty xlsx files in a shared folder. You copy each sheet into one master workbook, by hand, for thirty minutes.

The script:

\\\`python

from pathlib import Path

from openpyxl import load_workbook, Workbook

folder = Path("/Users/you/Downloads/daily")

master = Workbook()

out_sheet = master.active

out_sheet.title = "All"

out_sheet.append(["source_file", "name", "hours", "department"])

for f in sorted(folder.glob("*.xlsx")):

wb = load_workbook(f, read_only=True)

sheet = wb.active

for row in sheet.iter_rows(min_row=2, values_only=True):

out_sheet.append([f.name, *row])

master.save("master.xlsx")

print(f"merged {len(list(folder.glob('*.xlsx')))} files → master.xlsx")

\\\`

Run it once a day from a cron job or a desktop shortcut. Thirty minutes of clicking becomes two seconds of running.

2. Validate that every invoice has a matching PO number

The pain: Finance sends invoices, ops keeps a PO ledger, and you check by hand that every invoice number appears in the ledger.

The script:

\\\`python

import openpyxl

invoices = openpyxl.load_workbook("invoices.xlsx").active

ledger = openpyxl.load_workbook("po_ledger.xlsx").active

known_pos = {row[0] for row in ledger.iter_rows(min_row=2, values_only=True)}

orphans = []

for row in invoices.iter_rows(min_row=2, values_only=True):

inv_num, po, amount = row[0], row[1], row[2]

if po not in known_pos:

orphans.append((inv_num, po, amount))

print(f"{len(orphans)} invoices missing PO match")

for o in orphans:

print(o)

\\\`

Catches a finance discrepancy that used to take an analyst half a day.

3. Generate a per-store sales PDF every Monday

\\\`python

import openpyxl

from datetime import date

Read the weekly raw export

wb = openpyxl.load_workbook("weekly_sales.xlsx", read_only=True)

sheet = wb.active

by_store = {}

for row in sheet.iter_rows(min_row=2, values_only=True):

store, sku, qty, revenue = row

by_store.setdefault(store, {"qty": 0, "revenue": 0.0})

by_store[store]["qty"] += qty

by_store[store]["revenue"] += revenue

Write per-store summaries

out = openpyxl.Workbook()

out.remove(out.active)

for store, totals in by_store.items():

s = out.create_sheet(title=store[:31]) # Excel sheet name max 31 chars

s.append(["Store", "Units", "Revenue"])

s.append([store, totals["qty"], totals["revenue"]])

out.save(f"sales_{date.today().isoformat()}.xlsx")

\\\`

Same data, eight tabs instead of one. Each regional manager sees just their store. Twenty minutes → fifteen seconds.

4. Pull a Google Sheet into Excel without opening Google Drive

\\\`python

import gspread, openpyxl

gc = gspread.service_account(filename="creds.json")

remote = gc.open("Monthly Targets").worksheet("May")

rows = remote.get_all_values()

local = openpyxl.Workbook()

sheet = local.active

for r in rows:

sheet.append(r)

local.save("targets_may.xlsx")

\\\`

When you need the live Google Sheet snapshotted into Excel for an offline meeting. Five seconds.

5. Auto-fill missing zip codes from city names

\\\`python

import requests, openpyxl

wb = openpyxl.load_workbook("customers.xlsx")

sheet = wb.active

for row in sheet.iter_rows(min_row=2):

city_cell, zip_cell = row[2], row[3]

if zip_cell.value:

continue

r = requests.get(f"https://api.zippopotam.us/us/{city_cell.value}", timeout=5)

if r.ok:

zip_cell.value = r.json()["places"][0]["post code"]

wb.save("customers_filled.xlsx")

\\\`

Free public API, no signup. 2,000 rows fill in two minutes.

6. Schedule a weekly "low stock" email

\\\`python

import openpyxl, smtplib

from email.mime.text import MIMEText

wb = openpyxl.load_workbook("inventory.xlsx", read_only=True)

sheet = wb.active

low = [r for r in sheet.iter_rows(min_row=2, values_only=True) if r[2] < 5]

if low:

body = "Low stock:\\n" + "\\n".join(f"{r[1]}: {r[2]} units" for r in low)

msg = MIMEText(body)

msg["Subject"] = f"Low stock — {len(low)} items"

msg["From"] = "you@example.com"

msg["To"] = "you@example.com"

with smtplib.SMTP("smtp.gmail.com", 587) as s:

s.starttls()

s.login("you@example.com", "app-password")

s.send_message(msg)

\\\`

Schedule on Windows Task Scheduler or macOS \launchd\. Manager sees a Monday-morning email when SKUs drop below five.

7. Detect duplicate rows across columns you choose

\\\`python

import openpyxl

from collections import Counter

sheet = openpyxl.load_workbook("contacts.xlsx").active

key_cols = (0, 4) # name and phone

seen = Counter()

for row in sheet.iter_rows(min_row=2, values_only=True):

key = tuple(row[c] for c in key_cols)

seen[key] += 1

dupes = [(k, n) for k, n in seen.items() if n > 1]

print(f"{len(dupes)} duplicate keys found")

for k, n in dupes[:20]:

print(f" {k!r} appears {n} times")

\\\`

CRMs accumulate duplicates. Catches them in seconds.

Want to learn this end-to-end?

We just shipped a Python for Office Workers track — 54 lessons covering Excel, Google Sheets, and web scraping. Built specifically for people who came here from accounting, ops, or analytics rather than a CS degree. First 15 Foundations lessons free, no signup.