pandas equivalents of the Excel formulas you use most
Moving a report from Excel to Python is mostly a matter of knowing eight translations and three ways they bite.
6 minute read. Published 15 November 2026.
The translations
| Excel | pandas |
|---|---|
| VLOOKUP / XLOOKUP | orders.merge(prices, on="sku", how="left") |
| SUMIF | df.loc[df["region"] == "West", "amount"].sum() |
| SUMIF for every region | df.groupby("region")["amount"].sum() |
| COUNTIF | (df["status"] == "Open").sum() |
| IF | np.where(df["score"] >= 50, "Pass", "Fail") |
| IFERROR(x, 0) | df["x"].fillna(0) |
| TRIM | df["name"].str.strip() |
| Pivot table | df.pivot_table(index="region", columns="month", values="amount", aggfunc="sum") |
A lookup is a join
VLOOKUP runs once per row and returns one value. A merge joins two whole tables at once. The result is the same when the lookup table has one row per key.
When the lookup table has two rows for the same key, VLOOKUP quietly takes the first. A merge keeps both, so your orders table grows and every total after it is too high. Check for duplicates in the key before you merge.
merged = orders.merge(prices, on="sku", how="left", validate="m:1")
how="left"Keep every order, even when there is no price. That is what VLOOKUP does.validate="m:1"Raise an error if a sku appears twice in prices, instead of silently multiplying rows.merged["price"].isna().sum()Count the orders that found no price. This is your number of #N/A cells.
Three differences that cause wrong answers
- Capital letters. Excel treats "west" and "West" as equal in a comparison. pandas does not. Lowercase both sides first.
- Blanks. An empty Excel cell counts as zero in arithmetic. A missing value in pandas is NaN, and NaN spreads through a calculation. Decide whether to fill it with 0 or leave it out.
- Rounding. Excel's ROUND sends halves away from zero. pandas rounds halves to the nearest even digit, so 0.125 becomes 0.12 where Excel gives 0.13.
Check the two agree
- Pick three numbers from the Excel report: a grand total, one subtotal and a row count.
- Compute the same three in pandas.
- If the row count differs, look for duplicate keys in a merge.
- If only the totals differ, look for blanks and rounding.
Translating one formula at a time
For formulas that are not on this list, SheetSense's translator takes an Excel formula and returns the pandas, SQL and Google Sheets versions with an explanation. The formula library has worked examples for sixty common ones.
Translations are written by AI. Run them on a few rows where you already know the answer.
Paste an Excel formula and get the pandas version. Translate a formula