Spreadsheets vs Python With AI: When to Switch (Same Task, Both Ways)

You've got a CSV of 5,000 orders with duplicate rows, regions typed four ways, rupee signs inside the amounts and dates in two formats. Do you fix it with formulas or with Python? The honest answer for spreadsheets vs Python with AI is: it depends on how often you'll repeat the job and how big the file will get. To show where the line sits, I did the same task both ways on 2026-10-09 and compared the results.

The task: remove duplicate orders, normalise region names, turn ₹1,234 text into numbers, unify two date formats into months, and produce revenue by month and region. The data is synthetic (generated by a seeded script, 5,000 real orders plus 150 duplicated rows). The spreadsheet version ran in headless LibreOffice, not Excel, so treat the timings as indicative of formula cost, not as an Excel benchmark.

What does the spreadsheet version look like?

One data sheet with five raw columns (order_id, customer_id, region, order_date, amount) and five helper columns. These are the formulas for row 2:

F2  region_clean   =PROPER(TRIM(C2))
G2  amount_num     =VALUE(SUBSTITUTE(SUBSTITUTE(E2,"₹",""),",",""))
H2  month          =IF(ISNUMBER(FIND("-",D2)),LEFT(D2,7),MID(D2,7,4)&"-"&MID(D2,4,2))
I2  first_seen     =IF(COUNTIF($A$2:A2,A2)=1,1,0)
J2  amount_dedup   =G2*I2

And the summary cell for month in A2 and region in B$1:

=SUMIFS(Data!$J$2:$J$5151, Data!$F$2:$F$5151, B$1, Data!$H$2:$H$5151, $A2)

Strengths are obvious: you can see every intermediate column, click a cell and read its logic, and a colleague who has never opened Python can audit it. The COUNTIF($A$2:A2,A2) trick marks the first occurrence of each order_id, so duplicates contribute zero.

The weakness is also visible. That COUNTIF range grows by one row each time, so the work grows with the square of the row count. The date formula assumes exactly two formats; a third format silently produces junk. For prompt wording that gets formulas like these right, see ChatGPT prompts for Excel and Google Sheets formulas.

What does the pandas version look like?

This is the whole script, which I ran against the same file:

import sys, time
import pandas as pd

t0 = time.perf_counter()
df = pd.read_csv(sys.argv[1], dtype=str)
raw_rows = len(df)
df = df.drop_duplicates()                      # exact duplicate rows
df["region"] = df["region"].str.strip().str.title()
df["amount"] = df["amount"].str.replace("₹", "", regex=False).str.replace(",", "", regex=False).astype(int)
iso = df["order_date"].str.contains("-")
df["date"] = pd.NaT
df.loc[iso, "date"] = pd.to_datetime(df.loc[iso, "order_date"], format="%Y-%m-%d")
df.loc[~iso, "date"] = pd.to_datetime(df.loc[~iso, "order_date"], format="%d/%m/%Y")
df["month"] = pd.to_datetime(df["date"]).dt.strftime("%Y-%m")
pivot = df.pivot_table(index="month", columns="region", values="amount", aggfunc="sum", fill_value=0)
top = df.groupby("customer_id")["amount"].sum().nlargest(3)
elapsed = time.perf_counter() - t0
print("raw rows:", raw_rows, "after dedupe:", len(df))
print("total revenue:", int(df["amount"].sum()))
print(pivot.head(3).to_string())
print("top3 customers:", top.to_dict())
print(f"seconds: {elapsed:.2f}")

Real output on the 5,000-order file (pandas 3.0.5, Python 3.13):

raw rows: 5150 after dedupe: 5000
total revenue: 49968764
region      East    North    South     West
month                                      
2026-01  1357047  1522851  1378091  1289194
2026-02  1442375  1242023  1463252  1293882
2026-03  1476445  1471129  1355898  1627641
top3 customers: {'C00040': 412738, 'C00193': 352450, 'C00105': 337522}
seconds: 0.12

Two details: I read everything as strings (dtype=str) so pandas never guesses types for me, and format= is explicit in both date parses, which makes an unexpected format raise an error instead of passing through. The script also answers a question the spreadsheet version did not attempt, the top customers, in two lines. In a spreadsheet that needs a pivot table or a UNIQUE plus SUMIF setup.

Do the two versions agree?

They should, and I checked rather than assumed. After recalculating the workbook in LibreOffice, the summary sheet gave a total of 49,968,764 and 5,000 rows kept, with the first three monthly rows identical to the pandas pivot (for example, January East 1,357,047 and North 1,522,851). I repeated this at 20,000 orders: both gave 200,745,618 and 20,000 rows. Matching to the rupee at two sizes is the evidence that both versions are right. It is the check to run on any AI-written version too.

How do they scale?

Timings from this machine, so treat them as relative:

Quadrupling the rows made the spreadsheet about five times slower, which is the COUNTIF quadratic cost showing. I did not try a million-row spreadsheet: it would need roughly 1.03 million rows times ten columns, and the duplicate check would take far longer than I would wait. The million-row pandas run finished in about 9 to 10 seconds across two runs and fit in memory on this machine; timings vary run to run and machine to machine, and a re-run of the 20,000-row LibreOffice recalculation took 13.7 s against the 12.9 s above.

The hard limits, per the vendor pages, read on 2026-10-09 (limits change, so confirm on the page): Excel allows 1,048,576 rows by 16,384 columns per worksheet. Google Sheets allows up to 20 million cells or 100MB for files created in or converted to Sheets. My million-row file technically fits in Excel's row limit, but with helper columns it would be a miserable experience. The cap is not the practical limit; patience is.

When should you switch? A decision table

Three signals I would treat as "switch now": you've copied the same helper columns into a third workbook, a file takes more than a few seconds to recalculate, or someone asks "how did you get this number?" and the honest answer is a series of manual edits. For a spreadsheet that already exists and just needs smarter automation, Google Sheets with Apps Script and the Claude API is a middle path.

How do I get AI to write each version without trusting it blindly?

The prompt structure is the same for both. These are untested templates (I did not run them on a live model), so treat the output as a draft:

I have a table with these columns and five sample rows (pasted below).
Goal: [exact output, for example "revenue by month and region, duplicates removed"].
Known messiness: [region spelled in several cases, amounts like "₹1,234", dates as YYYY-MM-DD or DD/MM/YYYY].
Write [Excel formulas for helper columns, one per column / a Python pandas script].
Rules: do not guess a date format, fail loudly on anything unexpected; print row counts before and after each cleaning step.
Then give me three checks I can run to confirm the result is right.

Then verify the same way every time:

1. Row counts. Raw rows minus duplicates should equal the rows kept (here 5,150 minus 150 is 5,000).
2. One total by hand. Sum one region for one month with a filter and compare.
3. Break it on purpose. Add a row with a third date format. A good script errors; a bad one hides it. In the formula version, the unknown format quietly yields a wrong month, which is the case to look for.

More on structuring data questions for a model is in prompting for data analysis. If you want the model to run queries on a database rather than a file, the text-to-SQL analyst agent is the next step.

What I did not test

I did not run the formulas in Excel or Google Sheets, only LibreOffice, and function behaviour on locale-specific dates can differ. I did not have a model write either version, so no claim is made about how often AI gets them right. The data is synthetic, and real files are messier in ways no generator copies. Run both versions on your own file's first 500 rows before you commit to either.