5 Excel chores worth automating with Python (and how to tell which ones aren't)

#python #excel #automation #productivity

Most "Excel problems" I get asked about are not Excel problems. They are the same five boring steps someone does every Monday by hand. Here is how I decide whether a task is worth scripting, and the small Python patterns that cover most of them.

1. Count the clicks, not the rows

A 200,000-row file that you open once a year does not need a script. A 40-row file that three people copy-paste into a master sheet every morning does. Rule of thumb: if the same manual sequence happens weekly and takes more than 10 minutes, a script pays for itself within a month.

2. The four patterns that cover ~80% of requests

Merge: many files with the same columns into one sheet (glob + pandas.concat).
Clean: trim spaces, unify dates, split "Seoul, Gangnam-gu" into two columns, drop duplicates.
Summarize: pivot by month / store / product and write a new sheet the boss actually reads.
Rename and sort files: invoices named scan0042.pdf become 2026-10_ClientName_Invoice.pdf.

import pandas as pd, glob
frames = [pd.read_excel(f).assign(source=f) for f in glob.glob("in/*.xlsx")]
df = pd.concat(frames, ignore_index=True)
df["date"] = pd.to_datetime(df["date"], errors="coerce")
df.pivot_table(index=df["date"].dt.to_period("M"), columns="store",
               values="amount", aggfunc="sum").to_excel("summary.xlsx")

3. Ship it so a non-programmer can run it

The script is half the job. The other half: a one-click run.bat, an in/ and out/ folder, and a README with three lines. If the person has to open a terminal, they will stop using it in two weeks.

4. Never trust "it ran"

A script that exits without errors can still drop rows. I always print three numbers at the end: rows in, rows out, rows rejected (with a reason column). That single habit has caught more bugs than any test suite.

5. Keep the original

Write to a new file, never over the input. People forgive a slow script; nobody forgives a script that overwrote last quarter's numbers.

I run a small studio in Seoul doing exactly this kind of work (plus short AI product-ad videos). If you have a repetitive spreadsheet chore, I take small fixed-price jobs: one script, source code and a short how-to, usually delivered next day: Excel / file automation script on Ko-fi.

DEV Community