What Most People Miss About Using Python Code in Excel

By Michael Lee
Yes, Python code can be used in Excel. But if you’re double-clicking a cell expecting to type import pandas as pd and hit Enter, you’re about to waste two hours.

The Setup

You’re auditing quarterly sales for a midsize SaaS reseller. Finance dropped a raw export into Excel: 9 rows of messy transaction data with inconsistent date formats, mixed-case product names, and revenue values buried in text strings like "USD $14,250.75". No column headers yet. No validation. Just chaos in columns A:C.
ABC
2024-03-12cloudflare proUSD $1,240.00
Mar 15, 2024ZOOM BUSINESS$2,890.50
2024/03/18microsoft 365 e5USD 3,420.00
03/22/2024Slack EnterpriseUSD$1,985.25
2024-03-25asana premium$1,020.75
Mar 28, 2024NOTION TEAMUSD $2,110.30
2024/04/01jira software cloud$3,750.00
04/05/2024Figma OrganizationUSD$2,340.80
2024-04-10Linear ProUSD $1,650.00

The Challenge

You need to clean this before importing into Power BI. Specifically:
  • Parse all dates into ISO format (2024-03-12) in column D
  • Standardize product names to Title Case with no extra whitespace in column E
  • Extract numeric revenue as float in column F
Doing this manually is error-prone. Built-in Excel functions? TEXTSPLIT() helps with currency, but DATEVALUE() chokes on "Mar 15, 2024" unless you wrap it in SUBSTITUTE(). And there’s no native function to handle *all three* date formats at once. You’d need nested IFs, dozens of SUBSTITUTE calls, and pray your regional settings don’t flip. The beauty of this approach is that you don’t write Python *in* Excel cells — you write it once, in a script, then call it from Excel using PyXLL or xlwings. What makes this elegant is that Excel stays the UI; Python does the heavy lifting — no macros, no VBA, no COM interop headaches.

Walking Through It

We’ll use xlwings — free, open-source, and works with standard Python installs (no Anaconda required). First, install it: pip install xlwings. Then create clean_sales.py in the same folder as your Excel file. Here’s the core function: 
@xw.func
@xw.arg('data', ndim=2)
def clean_sales(data):
    import re
    import pandas as pd
    df = pd.DataFrame(data, columns=['date_raw', 'product_raw', 'revenue_raw'])
    df['date'] = pd.to_datetime(df['date_raw']).dt.strftime('%Y-%m-%d')
    df['product'] = df['product_raw'].str.title().str.strip()
    df['revenue'] = df['revenue_raw'].str.extract(r'([\d.,]+)').replace(',', '', regex=True).astype(float)
    return df[['date', 'product', 'revenue']].values.tolist()
Now back in Excel:
  1. Press Alt+F8, type xlwings_addin, press Enter — this loads xlwings’ ribbon tab
  2. Go to xlwings → Configure → Python Interpreter, point to your Python install (e.g., C:\Python311\python.exe)
  3. In cell D2, enter =clean_sales(A2:C10)
Before (A2:C10):
ABC
2024-03-12cloudflare proUSD $1,240.00
Mar 15, 2024ZOOM BUSINESS$2,890.50
2024/03/18microsoft 365 e5USD 3,420.00
03/22/2024Slack EnterpriseUSD$1,985.25
After (D2:F5):
DEF
2024-03-12Cloudflare Pro1240.0
2024-03-15Zoom Business2890.5
2024-03-18Microsoft 365 E53420.0
2024-03-22Slack Enterprise1985.25
Notice how pd.to_datetime() handles all four date formats automatically — no IFs, no TEXT(), no guesswork. That’s the counterintuitive part: Excel’s date logic is brittle; Pandas’ is forgiving. Let it do the work.

The Result

Here’s the full cleaned output — all nine rows, ready for pivot tables or export:
DEF
2024-03-12Cloudflare Pro1240.0
2024-03-15Zoom Business2890.5
2024-03-18Microsoft 365 E53420.0
2024-03-22Slack Enterprise1985.25
2024-03-25Asana Premium1020.75
2024-03-28Notion Team2110.3
2024-04-01Jira Software Cloud3750.0
2024-04-05Figma Organization2340.8
2024-04-10Linear Pro1650.0

What Could Go Wrong

  • Python path misconfigured: If Excel throws #VALUE! and xlwings shows “Python not found”, check xlwings → Configure → Python Interpreter. Don’t assume it auto-detects — paste the full path to python.exe, not just the folder.
  • Function name mismatch: Typing =Clean_Sales() instead of =clean_sales() fails silently — no error, just #N/A. xlwings is case-sensitive and ignores underscores in naming — stick to snake_case exactly as defined.
  • Data type bleed: If your Python function returns a numpy array with dtype=object, Excel may truncate decimals or convert floats to scientific notation. Always coerce with .astype(float) or .tolist() before returning.
Ready to go further? Here’s what to do next:
TaskShortcut / CommandNotes
Reload Python module after editingAlt+F8 → xlwings_reloadNo restart needed — edits apply instantly
View Python errors in ExcelAlt+X, LOpens xlwings log window — essential for debugging
Call Python from VBAUse xw.sheets[0].range("D2").value = xw.Book.caller().sheets[0].range("A2:C10").options(ndim=2).valueFor hybrid workflows — rare, but powerful
Export cleaned data to CSVAdd df.to_csv("cleaned_sales.csv", index=False) before returnAutomates handoff to analysts downstream
Michael Lee

Michael Lee

Michael covers the latest in office software updates