Python for Excel Users: A Starter Path for Singapore Professionals

If you already use VLOOKUP, PivotTables and filters, you know most of the logic of data analysis in Python. The pandas library does the same jobs with merge, pivot_table and boolean filters, but as a script you can rerun on next month's file in seconds. Four focused weeks will get you to useful work, and PCEP is a sensible first credential.

At a glance

• The tool: Python with pandas (the current documentation is for pandas 3.1.0), plus openpyxl to read and write .xlsx files
• Two ways to start: install Python on your laptop, or try Python in Excel, which runs in the Microsoft Cloud with a qualifying Microsoft 365 subscription
• The core map: VLOOKUP becomes merge, a PivotTable becomes pivot_table, SUMIFS becomes groupby
• Practice data: Singapore's open data portal, data.gov.sg, publishes datasets such as HDB's Resale Flat Prices as CSV files
• First credential: PCEP, 30 questions in 40 minutes with 70% needed to pass; a new exam version, PCEP-30-03, is scheduled for 31 October 2026

Why Excel users pick up pandas quickly

A pandas DataFrame is a sheet with named columns. When you write df["Amount"] * 1.09, the calculation runs down the whole column, exactly like filling a formula down. Filters, sorts, lookups and pivots all have direct equivalents, which the pandas team documents in its own comparison with spreadsheets.

What Python adds is not cleverness but repeatability. A monthly report that takes an afternoon of copying, pasting and refreshing becomes a script you run once the new export lands. The script is also an audit trail: anyone can read exactly what was done to the numbers, which is harder to show with a chain of manual steps.

Excel still wins for quick one-off questions, for sharing with colleagues who will never open a code editor, and for final formatting. The realistic goal is to use both: Python for the heavy, repeated preparation, Excel or Power BI for the finished view.

Setting up: two options

Option 1: Python on your laptop

Install a current Python 3 release from python.org, then add the two libraries you need:

python -m pip install pandas openpyxl

Use Visual Studio Code with its Python extension, or Jupyter notebooks if you like to see results step by step. On a company laptop, ask IT first: many organisations in Singapore restrict software installs, and some provide an approved Python environment.

Option 2: Python in Excel

If your organisation has a qualifying Microsoft 365 subscription, Microsoft's introduction to Python in Excel explains that you can choose Formulas, then Insert Python, or type =PY in a cell. The code runs in the Microsoft Cloud with a core set of libraries provided by Anaconda, so you need an internet connection but no local install. It works in Excel for Windows, the web and Mac, not on iPad, iPhone or Android.

Python in Excel is a gentle way to learn the syntax inside a familiar grid. For automating a process across many files, a local setup is more flexible.

The core map: Excel tasks in pandas

The TRIM note: Excel's TRIM also collapses double spaces inside text, while str.strip() only removes spaces at the ends. The pandas documentation points out the same gap. If you need the full TRIM behaviour, use .str.split().str.join(" ").

Three worked examples

The examples below use a typical sales export with columns such as Order Date, Customer, SKU, Region and Amount. Rename them to match your own file.

Cleaning a messy export

import pandas as pd

df = pd.read_excel("sales_export.xlsx", sheet_name="Data")

df.columns = df.columns.str.strip()                       # tidy header names
df["Customer"] = df["Customer"].str.strip().str.title()   # TRIM and PROPER
df["Amount"] = pd.to_numeric(df["Amount"], errors="coerce")  # text to numbers
df["Order Date"] = pd.to_datetime(df["Order Date"], dayfirst=True)
df = df.dropna(subset=["Amount"]).drop_duplicates()

Two details matter in Singapore. Dates are normally written day first (25/10/2026), so set dayfirst=True when dates arrive as text, otherwise 03/04 may be read as 4 March. And errors="coerce" turns entries like "N/A" into blanks you can count and inspect, instead of stopping the script.

Replacing VLOOKUP

products = pd.read_excel("products.xlsx")   # SKU, Category, UnitCost

merged = df.merge(products, on="SKU", how="left", validate="many_to_one")

unmatched = merged[merged["Category"].isna()]
print(len(unmatched), "rows have no matching SKU")

This is where pandas is safer than VLOOKUP. VLOOKUP quietly returns the first match; merge returns every match, so a duplicated SKU in the lookup table would silently double your sales. The validate="many_to_one" option makes pandas stop with an error if the lookup keys are not unique, and the unmatched check replaces scrolling for #N/A.

Replacing a PivotTable

summary = pd.pivot_table(
    merged,
    values="Amount",
    index="Region",
    columns="Category",
    aggfunc="sum",
    fill_value=0,
    margins=True,        # adds an "All" total row and column
)
summary.to_excel("sales_by_region.xlsx")

monthly = (
    merged.assign(Month=merged["Order Date"].dt.to_period("M"))
          .groupby(["Month", "Region"], as_index=False)["Amount"]
          .sum()
)

Next month, drop the new export into the folder and run the same file. That is the whole point.

One habit to unlearn

In Excel you click a cell and change it. In pandas 3, which makes Copy-on-Write the only mode, the shortcut many tutorials still show, df["Amount"][df["Region"] == "East"] = 0, does not change df at all. The pandas migration guide says chained assignment "will never work". Write it in one step instead:

df.loc[df["Region"] == "East", "Amount"] = 0

Also resist fixing values by hand in the output file. If a correction is needed, put it in the script, so it happens again next month.

Practise on Singapore data

Use public data while you learn, so you never risk a client file. Singapore's open data portal, data.gov.sg, publishes HDB's Resale Flat Prices collection as CSV files covering transactions from 1990. Load the latest file with pd.read_csv, look at df.columns and df.head() first, then try:

1. Count transactions by town.
2. Build a pivot of the median resale price by town and flat type.
3. Group by month and plot the trend with .plot() (install matplotlib first).

When you move to real work data, keep personal data such as NRIC numbers and phone numbers out of notebooks you share or upload. The PDPA's protection duty applies to scripts and exports as much as to spreadsheets.

A four-week starter plan

• Week 1, core Python. Variables, data types, lists, dictionaries, loops, conditions and functions. These are also the foundations PCEP tests.
• Week 2, reading and cleaning. Load one of your own recurring exports, clean it with the steps above and compare the result with your manual version.
• Week 3, lookups and summaries. Rebuild one real monthly report with merge, groupby and pivot_table, including checks for unmatched rows.
• Week 4, automate. Use pathlib to loop over every file in a folder, combine them with pd.concat and write one output workbook. Then take a PCEP practice test to find your gaps.

If your reports end in dashboards, our guide to Power BI month-end reporting for Singapore finance teams shows where Python preparation fits.

PCEP as a first credential

PCEP, the Certified Entry-Level Python Programmer from the OpenEDG Python Institute, checks core Python rather than pandas. The PCEP page lists 30 questions in 40 minutes (plus 5 minutes for the agreement and tutorial), a passing score of 70% and validity of five years. PCEP-30-02 is the active version, and PCEP-30-03 is scheduled for release on 31 October 2026 as a routine update, so check which version you will sit and study the matching syllabus.

Think of it as proof of fundamentals. Your portfolio of rebuilt reports proves the data skill. If you are weighing the next step, our comparison of PCEP and PCAP explains when the associate level is worth it.

How CertTulen Academy can help

CertTulen Academy runs live, MCT-led online classes with hands-on labs. Our Python Entry-Level Programming (PCEP) course builds the core Python you need before pandas makes sense, and professional certification courses include an exam voucher, mock practice tests and lab access (see the course page for details). When you are ready for more, PCAP takes you to the associate-level syllabus. Contact us or WhatsApp +60 16-241 8686 for a batch that suits Singapore time, or a private cohort for your team.