From Excel to Python: A Migration Plan for Analysts

McTaba runs coding and data bootcamps in Kenya. This guide is written to be useful whether or not you ever join one.

Imagine the last working day of the month. You open the sales workbook, paste in the till export, refresh four pivot tables, fix the dates that came in as text, and email the file. It takes three hours, and a pasted row in the wrong place breaks it.

Python does that job in one command, and it does it the same way every time. You do not need to leave Excel. Managers will still read Excel files. You only change who builds them.

Pick one report you run every month. That is your migration project. The wider skill set Python belongs to is mapped in our data science career guide for Kenya.

The four-week plan

Week	Task	Excel step it replaces
1	Load and clean files with pandas	Paste, trim, fix dates
2	Merge tables	VLOOKUP and XLOOKUP
3	Group and pivot	Pivot tables and SUMIFS
4	Compare, export, schedule	Manual checking and emailing

If you would rather learn this in a class, the Data Science to AI Engineering programme teaches Python from zero and then pandas. No coding, data or maths background needed.

Install nothing yet. Open Google Colab, upload your till export, and read it. If the first steps in Python are new, start with our first 30 days of Python.

import pandas as pd

sales = pd.read_excel("october_sales.xlsx")   # or read_csv for a .csv
print(sales.head())
print(sales.dtypes)

Now write down every manual fix you do in Excel and turn each into one line.

sales["product"] = sales["product"].str.strip().str.lower()
sales["date"] = pd.to_datetime(sales["date"], dayfirst=True)
sales = sales.drop_duplicates()
sales["revenue"] = sales["qty"] * sales["price"]

Notice dayfirst=True. Kenyan files usually write 03/10/2026 as 3 October. Without it, pandas may read it as 10 March. Check a few rows by eye.

Week one goal: a notebook that loads October and makes the same clean table you get after your manual fixes.

VLOOKUP pulls a column from another table using a shared key. In pandas the same job is merge. Imagine a made-up price list sheet with product and category, and a sales table with product and revenue.

catalogue = pd.read_excel("catalogue.xlsx")   # product, category

merged = sales.merge(catalogue, on="product", how="left")

# rows that found no match are the #N/A cells in Excel
missing = merged[merged["category"].isna()]
print(len(missing), "sales rows with no category")

Three differences from VLOOKUP that help you:

• The key can be on any column, not only the left one.
• how="left" keeps every sale, even without a match. Use "inner" to keep only matches.
• Duplicate keys in the lookup table multiply your rows. Check the row count before and after, as you would with a JOIN in SQL for data analysis.

Week two goal: your merged table has the same row count as the raw data, and you have a list of unmatched products to fix at the source.

A pivot table groups rows and adds them up. The simplest form is groupby.

by_category = (
    merged.groupby("category")["revenue"]
    .sum()
    .sort_values(ascending=False)
)
print(by_category)

When you need rows and columns, as in a classic pivot, use pivot_table:

report = pd.pivot_table(
    merged,
    index="category",
    columns=merged["date"].dt.to_period("W").astype(str),
    values="revenue",
    aggfunc="sum",
    fill_value=0,
    margins=True,        # adds the Total row and column
)
print(report)

SUMIFS maps onto a filter plus a sum: merged.loc[merged["category"] == "dairy", "revenue"].sum(). The pandas reshaping guide documents pivot_table in full.

Week three goal: the pivot in Python matches the pivot in last month's workbook, cell for cell.

Do not switch off the Excel version yet. Run both for one month end and compare the totals.

excel_total = 4_812_300      # type the total from the workbook (made-up number)
python_total = merged["revenue"].sum()
print("Difference:", python_total - excel_total)

If the difference is not zero, you have found something useful. Often the manual file had a stray filter or a pasted row. Fix the cause and note it. A mismatch you can explain builds more trust than a match you cannot.

Export for people who want Excel

with pd.ExcelWriter("october_report.xlsx") as writer:
    report.to_excel(writer, sheet_name="By category")
    missing.to_excel(writer, sheet_name="Unmatched")

Your manager still opens an Excel file. Last, put the code in a single function, build_report("october"), so that next month you change one word and run it.

Once the report builds itself, you have time for the next question: which numbers deserve trust, and how to tell a manager. See data storytelling for the memo.

• Quick one-off looks. For ten rows you will never see again, Excel is faster.
• Shared editing. A finance team typing into one sheet needs a spreadsheet.
• Formatting for print. Python can export, but fine layout is easier by hand.

Move the repeatable, error-prone work to Python and keep Excel for everything else. No school can promise you a job from this switch, but it is a skill analysts list on their CVs, and you can show a working project instead of saying you know it. If you want a class, the Data Science to AI Engineering programme starts on 11 January 2027 and costs KES 140,000.