📎 This article includes 2 downloadable practice files ↓

You do not need to become a software developer to get value from Python. If you already use filters, pivot tables and VLOOKUP, you understand most of what the pandas library does. This post walks through a real routine — load, clean, summarise, save — and shows the Excel equivalent of every line.

Setup (once)

Install Python from python.org (tick “Add to PATH”), then in a terminal:

pip install pandas openpyxl

1. Load the file — like opening the workbook

import pandas as pd

df = pd.read_excel("sales.xlsx", sheet_name="Data")
print(df.head())        # first 5 rows
print(df.shape)         # (rows, columns)

2. Clean it — like TRIM, Find & Replace and Remove Duplicates

df.columns = df.columns.str.strip().str.lower()          # tidy headers
df["region"] = df["region"].str.strip().str.title()        # TRIM + PROPER
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")  # numbers stored as text
df = df.dropna(subset=["amount"]).drop_duplicates()

3. Filter — like AutoFilter

big_north = df[(df["region"] == "North") & (df["amount"] > 10000)]

4. Summarise — like a pivot table

pivot = pd.pivot_table(df, index="region", columns="product",
                       values="amount", aggfunc="sum", fill_value=0, margins=True)
print(pivot)

5. Lookup — like VLOOKUP / XLOOKUP

managers = pd.read_excel("sales.xlsx", sheet_name="Managers")   # region, manager
df = df.merge(managers, on="region", how="left")

merge with how="left" keeps every sales row and adds the manager — exactly what an XLOOKUP down the whole column does, but in one line and without #N/A surprises.

6. Save — to a new workbook with several sheets

with pd.ExcelWriter("summary.xlsx") as xl:
    pivot.to_excel(xl, sheet_name="Pivot")
    big_north.to_excel(xl, sheet_name="Big North", index=False)

When is Python better than Excel?

• The same steps every week — a script never forgets a step.
• Files too big for Excel to open comfortably (hundreds of thousands of rows).
• Combining many files — see merge 100 Excel files with Python.

And when is Excel better? When someone needs to explore the data by eye, or when the output is a formatted report people will edit. Use both.

Try it yourself: step by step

1. Download sales.xlsx and clean_and_summarise.py into the same folder.
2. Open the workbook first: rows 2–7 have messy regions (“ north ”) and rows 8–11 have quantities stored as text — the problems the cleaning steps fix.
3. Open a terminal in that folder (in File Explorer type cmd in the address bar) and run pip install pandas openpyxl once.
4. Run python clean_and_summarise.py. The pivot prints on screen and summary.xlsx appears with two sheets.
5. Change one line — e.g. aggfunc="sum" to aggfunc="mean" — run again and compare.