If you are comfortable in Excel, you already understand most of what pandas does. The difference is that a pandas script records every step, runs in seconds on millions of rows, and can be scheduled to run by itself. This guide maps familiar Excel tasks to their pandas equivalents.
Setting up
uv add pandas openpyxl # or: python -m pip install pandas openpyxlNew to Python environments? The uv guide covers setup in five minutes.
Opening a workbook
import pandas as pd
sales = pd.read_excel("sales.xlsx", sheet_name="Orders")
products = pd.read_excel("sales.xlsx", sheet_name="Products")
sales.head() # first five rows, like glancing at the top of a sheet
sales.info() # column names, types and missing values
sales.describe() # quick statistics for numeric columnsFiltering rows
# Excel: filter the Region column to "North" and Amount greater than 100
north_big = sales[(sales["region"] == "North") & (sales["amount"] > 100)]
# Several values, like ticking boxes in a filter drop-down
selected = sales[sales["region"].isin(["North", "West"])]Use & for AND and | for OR, and wrap each condition in brackets.
Adding a calculated column
sales["revenue"] = sales["quantity"] * sales["unit_price"]
# Excel IF: =IF(revenue > 500, "Large", "Standard")
sales["size"] = sales["revenue"].where(sales["revenue"] <= 500, "Large")
sales.loc[sales["revenue"] <= 500, "size"] = "Standard"For several conditions, numpy.select replaces nested IFs cleanly:
import numpy as np
conditions = [sales["revenue"] >= 1000, sales["revenue"] >= 500]
choices = ["Large", "Medium"]
sales["size"] = np.select(conditions, choices, default="Small")VLOOKUP and XLOOKUP become merge
# Bring product name and category into the sales table, matched on product_id
sales = sales.merge(
products[["product_id", "product_name", "category"]],
on="product_id",
how="left", # keep every sale, like VLOOKUP returning #N/A
validate="many_to_one" # raises an error if products has duplicate IDs
)
missing = sales[sales["product_name"].isna()] # your #N/A rowsThe validate argument is a hidden gem: it catches duplicate lookup keys, the same problem described as "fan-out" in the SQL joins guide.
SUMIFS and COUNTIFS become groupby
by_region = (
sales.groupby("region", as_index=False)
.agg(revenue=("revenue", "sum"),
orders=("order_id", "nunique"),
avg_order=("revenue", "mean"))
.sort_values("revenue", ascending=False)
)Pivot tables
pivot = pd.pivot_table(
sales,
index="region",
columns="category",
values="revenue",
aggfunc="sum",
fill_value=0,
margins=True, # adds the Grand Total row and column
margins_name="Total"
)Dates
sales["order_date"] = pd.to_datetime(sales["order_date"], dayfirst=True)
sales["month"] = sales["order_date"].dt.to_period("M")
sales["weekday"] = sales["order_date"].dt.day_name()
monthly = sales.groupby("month", as_index=False)["revenue"].sum()
monthly["change_pct"] = monthly["revenue"].pct_change().mul(100).round(1)Cleaning data
sales = sales.drop_duplicates(subset="order_id") # Remove Duplicates
sales["customer"] = sales["customer"].str.strip().str.title() # TRIM and PROPER
sales["discount"] = sales["discount"].fillna(0) # blanks become zero
sales = sales.rename(columns={"Cust Ref": "customer_ref"})Saving back to Excel
with pd.ExcelWriter("monthly_report.xlsx") as writer:
by_region.to_excel(writer, sheet_name="By region", index=False)
pivot.to_excel(writer, sheet_name="Pivot")
monthly.to_excel(writer, sheet_name="Monthly", index=False)Put these steps in one script and you have a report that rebuilds itself. The automation guide shows how to schedule it, and the pandas cheat sheet keeps every command above on a single page.
Written by Alessandro Ecclesie Agazzi, freelance analytics engineer in London. Updated 30 September 2026.