The Short Answer
Excel is the right default for exploration and delivery. You open a file, drag a field into a pivot table, and you see the answer before you have finished deciding what question to ask. For a 12,000-row sales export, that loop is faster than anything Python gives you because there is no setup. When the orders CSV in my test grew past 120,000 rows, the experience changed: scrolling stuttered, a pivot refresh took roughly 8 seconds, and the workbook hit 45MB after I attached two more sheets.
Python (pandas) is the right default for anything that repeats, anything big, and anything you want to hand to a future version of yourself. The same file opened in 0.6 seconds. The monthly aggregation was one line. The cleaning steps were saved in a .py file that I could re-run next month against new data without redoing a single manual step. If you are still deciding which language to learn first, my take is that SQL or Python first is a more useful question than Excel versus Python, because SQL usually enters the stack before either.
The Dataset I Used for Both Tests
Everything below runs against one file: orders.csv, 120,000 rows, columns OrderID, OrderDate, CustomerID, Region, Category, Amount, and Discount. A second table, customers.csv, holds CustomerID, CustomerName, SignupDate, and Segment, with 9,400 rows. This is deliberately boring — it looks like what Shopify, Stripe, or any internal order system exports.
The three jobs are: total sales by month, drop rows with a missing Amount, and attach customer names to each order. I picked those three because they cover aggregation, cleaning, and joining. Get comfortable with all three and you can handle most analyst requests without opening a single wizard.
Job 1: Reading a 120K-Row CSV
Double-clicking a 120,000-row CSV opens it in the grid and Excel tries to guess types, which is where the slow first paint comes from. Go to Data > Get Data > From Text/CSV instead. In the preview window, set Delimiter to Comma, then click Transform Data so you land in Power Query before anything loads. Check that OrderDate is typed as Date and Amount as Decimal Number, then Close & Load To > PivotTable or a new worksheet.
Excel's grid holds 1,048,576 rows, so 120,000 rows technically fit. That does not mean Excel handles them well. The recalc I measured — about 8 seconds for a pivot refresh plus dependent formulas — happened at 120,000 rows on a 16GB laptop with nothing else open. At 400,000 rows the same workbook became unusable for interactive work and the xlsx passed 45MB.
Load the file with pd.read_csv("orders.csv", parse_dates=["OrderDate"]). On my machine that returned in 0.6 seconds. Let pandas infer dtypes on the first run, then write them down and pass them explicitly — dtype={"Region": "category"} — because reading Region as a 120,000-value string column wastes memory and makes groupby slower. Use pd.read_csv once on a sample of 5,000 rows with nrows=5000 to inspect the columns before committing.
Run df.shape, df.head(), and df.dtypes immediately after reading. I check df["Amount"].sum() and compare it to the total Excel's status bar shows for the Amount column — if those two numbers differ, something in the file is malformed, usually a stray quote breaking a field. Ten seconds of checking saves you from reporting the wrong total to your manager.
A common mistake is opening the CSV by double-clicking and assuming Excel's import dialog means the file is now type-safe. Excel applies its own guesses on top of your guesses. Power Query's preview is the only place in Excel where you can see and change the data types before they hit the grid. In pandas, you control this with dtype and parse_dates. Both tools want you to be explicit; only one of them asks.
Job 2: Total Sales by Month
This is the job that exposes the real difference. In Excel a pivot table answers it in four clicks, and the answer is visible immediately. In pandas it is one line, and that one line is a file you can run again forever. Same output, different shelf life.
Insert a column F and name it Month. In F2 enter =TEXT([@OrderDate],"yyyy-mm") and fill down. The TEXT function returns text, not a date, which is exactly what you want for grouping but means it will not sort chronologically on its own. Drag Month into Rows and Amount into Values, set the value field to Sum, and you get 24 rows of monthly totals.
Add a net column G with =[@Amount]*(1-[@Discount]). Then build a small summary table and use =SUMIFS(Orders[Amount], Orders[Month], $I2) next to =SUMIFS(Orders[Net], Orders[Month], $I2). Comparing gross against net by month is where you spot a month with heavy discounting that still missed its target — the kind of thing a plain revenue chart hides.
df["month"] = df["OrderDate"].dt.to_period("M") then df.groupby("month")["amount"].sum(). That single expression replaced the helper column, the fill-down, and the pivot configuration. Use dt.to_period("M") rather than a string slice so the result keeps a real time ordering and plots correctly.
Compute net first: df["net"] = df["amount"] * (1 - df["discount"]). Then run df.groupby("month")[["amount", "net"]].sum(). One call, two columns, no second SUMIFS range to keep aligned. This is the moment most Excel users stop arguing — keeping two formula ranges in sync across 24 rows is a job you can lose to a single inserted row.
If you like the cross-tab layout, run df.pivot_table(index="month", columns="region", values="amount", aggfunc="sum", fill_value=0). You now have the pivot table, but it is a variable you can assign to a plot, mutate, or export — not a floating object anchored to a range. That is the structural difference worth internalizing: in Excel the pivot is the deliverable, in pandas it is an intermediate step.
Job 3: Cleaning Missing Amounts and Bad Dates
The test file has 1,847 blank Amount cells and 312 rows where OrderDate parsed as text because someone typed 03/14/26 in a US-format column that was otherwise ISO. Both tools can fix this. Only one of them documents what it did.
Select the Amount column and read the status bar, or use =COUNTBLANK(Orders[Amount]) in a scratch cell. Do this before you delete. I have watched analysts filter out blanks, save, and only then realize 1,847 rows was 6% of the file — enough to change the monthly trend line they had already presented.
Dropping 1,847 rows hides them; filling with the column median (=MEDIAN(Orders[Amount])) invents revenue that never happened. For this file I flag instead: add a column with =IF([@Amount]="","NO_AMOUNT","OK") and keep the rows. Then when a regional total looks low, you can filter to NO_AMOUNT and see whether the missingness sits in one Region rather than everywhere.
Use Data > Text to Columns on the OrderDate column, click Next twice, and set the column format to Date with MDY order. Rows that still will not convert are the genuinely broken ones — grab them with =ISNUMBER([@OrderDate]) showing FALSE and fix them by hand. 312 is small enough to eyeball; 3,000 would not be.
Run df.isna().sum() and df.isna().mean().round(3). That gives you a count and a share per column in one pass, which Excel's status bar cannot do across seven columns. Seeing Amount at 0.015 and Region at 0.000 tells you the missingness is concentrated, not random — a different cleaning decision than a column with 30% gaps.
To drop: df = df.dropna(subset=["amount"]). To flag instead: df["amount_missing"] = df["amount"].isna(). To fill only where it is defensible: df["amount"] = df["amount"].fillna(df["amount"].median()). Each choice is one line in a script, so the next person can see exactly what you did — and revert it if they disagree.
Run pd.to_datetime(df["order_date"], errors="coerce", format="mixed") and then count df["order_date"].isna().sum(). Everything unparseable becomes NaT and lands in one place. In my test that surfaced all 312 rows in a single check, and the errors="coerce" flag meant one broken value could not abort the entire load — which is exactly what happens if you let pandas raise.
This will break if you clean in place without a copy. Always keep the raw frame — raw = df.copy() at the top of the script — so you can re-derive any cleaning decision with new information. I have had to answer "how many rows did we drop last quarter" twice this year, and both times only the person with the raw file could answer it.
Job 4: Merging the Customer Table
Orders has CustomerID. Customers has CustomerID and CustomerName. You want a name next to every order. In Excel this is the lookup every analyst learns first, and it is also the formula that quietly returns wrong answers more often than any other.
Load customers.csv into a sheet named Customers, select A1:D9401, and define the name Customers in the Name Box. Then in the Orders sheet add a CustomerName column with =XLOOKUP([@CustomerID], Customers[CustomerID], Customers[CustomerName], "UNMATCHED"). The fourth argument matters — without it, undmatched IDs return #N/A and your pivot silently drops those rows from the total.
Put =COUNTIF(Orders[CustomerName],"#N/A") somewhere and look at the number. If it is 0, the join is clean. If it is 400, you either have orphan orders or a trailing-space problem in one of the ID columns, which you fix with TRIM. Skipping this check is how a report ends up 3% short with nobody able to explain why.
Run merged = orders.merge(customers, on="customer_id", how="left") then merged["customername"].isna().sum(). The how="left" argument keeps every order even when the customer is missing, matching the XLOOKUP default argument above. Then use validate="many_to_one" so pandas raises if the customer table has duplicate IDs — a check Excel gives you no way to run at all.
If the merge throws a MergeError about many_to_one, your customers.csv has duplicate CustomerID values and your Excel XLOOKUP has been returning the first match without telling you. This is the single most useful thing pandas has done for my reporting accuracy — it turned a silent wrong answer into a loud error message.
The Numbers, Side by Side
These are from my own machine — a 16GB laptop, Excel 365 current channel, Python 3.12 with pandas 2.x — measured on the same 120,000-row file. Your absolute numbers will differ, but the ratio will not.
Read the CSV: Excel via Power Query about 6 seconds to import and type the columns; pandas 0.6 seconds. Monthly aggregation: Excel pivot refresh roughly 8 seconds and it re-runs on every refresh; pandas under 0.1 seconds after the read. Merge with the 9,400-row customer table: Excel XLOOKUP across 120,000 rows took about 4 seconds and left the workbook sluggish to scroll; pandas merge finished in roughly 0.3 seconds. File on disk: saved xlsx 45MB; the CSV plus a 3KB script is under 25MB, and the script is the part that matters.
The file-size number is the one people underestimate. A 45MB xlsx is slow to email, slow to open in Teams preview, and Excel refuses to let two people edit it at once without the co-authoring dance. The pandas version is a 3KB .py file that works on anyone's machine with Python installed. The workload lives in text you can diff; in Excel it lives in a binary container you cannot.
Where Excel Actually Wins
Excel's advantage is not speed, it is immediacy and social reach. I can hand a workbook to a marketing manager who has never opened a terminal and they can filter it, change a number, and send it on. That handoff is worth more than a few seconds of compute in most companies.
Traceability matters too. Every cell shows its formula on click. A stakeholder can select a total, see =SUMIFS(Orders[Amount],Orders[Month],$I2), and understand where the number came from without reading a script. Conditional formatting, sparklines, and a chart dropped next to the table also beat any matplotlib default I have ever produced under deadline.
And for one-off questions, Excel's setup cost is close to zero. If someone asks for last quarter's revenue by Region and I have the file open, the pivot is built in under a minute. Opening a notebook, reading the CSV, writing the groupby, and formatting the output takes longer for a question I will never be asked again. If your work is mostly SQL already, the same reasoning applies to the SQL-versus-spreadsheet boundary, which excel vs SQL: when to use which covers in more detail.
Where Python Actually Wins
Repetition is the dividing line. If I clean, join, and aggregate the same way every month, that is a script, not a spreadsheet ritual. The script runs while I am asleep, produces the same output every time, and if a number looks wrong I can read the twelve lines that made it.
Volume is the second line. Above a few hundred thousand rows Excel stops being an interactive tool — every scroll and recalc costs you. pandas holds millions of rows in memory and does not care about your screen. If your export is already 300,000 rows, this decision was made for you.
Then there is version control and modelling. A .py file goes into Git, gets a commit message, and someone can see what changed in March. A chain of merges and filters in a script is also reproducible in a way that a sequence of manual pivot refreshes is not, and you can call scipy or statsmodels on the same frame for a regression without exporting anything. If your cleaning steps are the part that trips you up, pandas data cleaning for Excel users maps the spreadsheet habits onto the pandas equivalents.
The Hybrid Workflow I Actually Use
Most weeks I do not choose. I process in pandas and deliver in Excel, because the two halves have different audiences. The processing layer is mine and lives in Git. The delivery layer goes to someone who wants to change a filter and click a chart.
One script: read orders.csv, coerce dates, drop or flag the 1,847 null amounts, merge customers, groupby month and region. Keep the raw frame untouched at the top and write intermediate decisions as separate columns rather than overwriting.
Use with pd.ExcelWriter("monthly_report.xlsx", engine="openpyxl") as writer: and write each summary to its own sheet — summary.to_excel(writer, sheet_name="Monthly", index=False). Sheet per question, not sheet per 120,000-row dump. The workbook stays a few hundred KB and opens instantly.
Open the generated file, apply number formats, freeze the header row, and set the print area. Save that as your template and have the script write into it next month, so the formatting is not rebuilt each cycle. Column widths and currency formats are the two things pandas will never get right for you.
Store monthly_report.py and monthly_report.xlsx in the same folder, and commit both. When a stakeholder asks in July why the March total changed, you have the March inputs and the exact code that produced the number. That single folder answers questions that otherwise take an afternoon of archaeology.
df.to_excel() is slow — noticeably slower than writing CSV, and it gets worse with every thousand rows. Write more than about 100,000 rows and you will sit there watching the spinner. For anything that size, write the raw output to Parquet and build only the small summary tables into Excel. You will hit this wall around your third monthly report if you skip it.
When Not to Reach for Python
Do not rewrite a 20-minute one-off task as a script. If the request will never repeat and nobody will audit it, a pivot table in Excel is the correct answer and building a pipeline is procrastination dressed as rigour. I have watched people spend two days automating a task that ran quarterly and took fifteen minutes by hand.
The harder case is maintainability by non-technical colleagues. If the person who owns this report after you cannot read Python, a script becomes a black box that nobody can fix, and the first time it fails you get the call anyway. In that situation a well-built Power Query workbook or a clean pivot template is genuinely the better engineering choice, even though the pandas version would be faster to run.
There is also the environment question. A script needs Python, pandas, and a working command line on whatever machine runs it. A workbook needs Excel. In companies where IT installs software slowly and you cannot guarantee a colleague's laptop, that is a real constraint and not a hypothetical one.
How to Decide in Three Questions
Ask in this order and stop at the first yes. Does it repeat monthly or more often? Write it in pandas — see the workflow above. Is the file over 50,000 rows? Use pandas for processing and Excel for delivery. Will a non-technical colleague own and maintain it after you? Build it in Excel, Power Query, or a template. If the answer is no to all three, use Excel and ship the answer.
Two things to do next. First, take your own most annoying recurring spreadsheet task and rewrite just the read-and-aggregate step in pandas — that is usually 80% of the pain. Second, if you are choosing what to learn for a data analyst role, treat pandas as a complement to Excel and SQL rather than a replacement, and be ready to say out loud which one you would pick for a 40,000-row weekly file and why.
