Most Excel trainers still tell you to install Anaconda, then PyXLL, then fight with DLL errors for three hours. They’re wrong. Since October 2023, Python runs natively in Excel — no add-ins, no admin rights, no Python installation on your machine. Microsoft hosts the runtime. You type =PY in a cell and it just works. If you’ve spent time wrestling with COM objects or trying to get xlwings to talk to Excel on Windows 11, you’ve been solving yesterday’s problem.
The Setup
We’re working with a sales reconciliation sheet from Alibaba Cloud’s APAC channel team — real data, real inconsistencies. The file is Sales_Q2_2024.xlsx, opened in Excel for Microsoft 365 (v2405+). Sheet name: RawData. It contains 9 rows of transaction records — some duplicates, inconsistent date formats, and mixed currency strings that need conversion before analysis.
| A | B | C | D | E |
|---|---|---|---|---|
| ID | Customer | Date | Amount | Currency |
| S-7821 | LingTech Pte Ltd | 2024-04-12 | $12,450.00 | USD |
| S-7822 | Acme Corp (SG) | 12/04/2024 | ¥1,890,000 | JPY |
| S-7823 | Nova Logistics | 2024-04-15 | €8,230.50 | EUR |
| S-7824 | LingTech Pte Ltd | 15/04/2024 | $12,450.00 | USD |
| S-7825 | BrightEdge HK | 2024-04-18 | HK$64,200 | HKD |
| S-7826 | Acme Corp (SG) | 18/04/2024 | ¥1,890,000 | JPY |
| S-7827 | Zephyr Labs | 2024-04-22 | €7,910.00 | EUR |
| S-7828 | BrightEdge HK | 22/04/2024 | HK$64,200 | HKD |
| S-7829 | Nova Logistics | 2024-04-25 | €8,230.50 | EUR |
The Challenge
You need to:
- Deduplicate rows where ID, Customer, Date, Amount, and Currency all match (not just ID)
- Standardize Date to ISO format (YYYY-MM-DD) in column C
- Convert all Amount values to USD using live exchange rates (as of 2024-04-25)
- Output clean results in a new sheet named
CleanedSales
This isn’t impossible in pure Excel — but it’s fragile. TEXTJOIN + UNIQUE + XLOOKUP + DATEVALUE gets messy fast. And if the source adds a new currency next month? You’ll need to update five formulas across three sheets. With Python, you define the logic once, test it, and reuse it.
Walking Through It
Open Sales_Q2_2024.xlsx. Go to Formulas → Python → Launch Python Editor (Alt+T+P). Paste this script into the editor — no installation, no pip, no virtual env:
# Load raw data from A1:E10
import pandas as pd
import numpy as np
df = xl("RawData!A1:E10")
# Standardize dates
df['Date'] = pd.to_datetime(df['Date'], dayfirst=True).dt.strftime('%Y-%m-%d')
# Currency conversion map (rates as of 2024-04-25)
fx = {'USD': 1.0, 'JPY': 0.00672, 'EUR': 1.068, 'HKD': 0.128}
df['Amount_USD'] = df.apply(lambda r: float(r['Amount'].replace([r'\$', r'¥', r'€', r'HK\$'], '')) * fx[r['Currency']], axis=1)
# Dedupe full row (all columns)
df_clean = df.drop_duplicates(subset=['ID','Customer','Date','Amount','Currency'])
# Return only needed columns
df_clean[['ID','Customer','Date','Amount_USD']]Click Run. Excel returns an array starting at A1 of the active sheet. But we want it in CleanedSales. So cut that output, go to CleanedSales!A1, and paste. Done.
Wait — not quite. That pastes static values. To keep it dynamic, use the =PY function directly in the worksheet. In CleanedSales!A1, enter:
=PY(
"import pandas as pd; import numpy as np; df = xl('RawData!A1:E10'); " &
"df['Date'] = pd.to_datetime(df['Date'], dayfirst=True).dt.strftime('%Y-%m-%d'); " &
"fx = {'USD':1.0,'JPY':0.00672,'EUR':1.068,'HKD':0.128}; " &
"df['Amount_USD'] = df.apply(lambda r: float(r['Amount'].replace(['$','¥','€','HK$'],'')) * fx[r['Currency']], axis=1); " &
"df.drop_duplicates(subset=['ID','Customer','Date','Amount','Currency'])[['ID','Customer','Date','Amount_USD']]"
)Hit Enter. Excel spills the result across 5 columns × 7 rows. No macros. No trust center warnings. Just Python — baked in.
Here’s what changes at each step:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Load RawData!A1:E10 into DataFrame | 9-row DataFrame with mixed date formats | Alt+T+P |
| 2 | Apply pd.to_datetime(..., dayfirst=True) | All dates now YYYY-MM-DD (e.g., '12/04/2024' → '2024-04-12') | — |
| 3 | Strip currency symbols & multiply by FX rate | New column Amount_USD: e.g., ¥1,890,000 → $12,700.80 | Ctrl+Enter (to edit formula line) |
| 4 | Drop duplicates on full row | Rows S-7821/S-7824 and S-7822/S-7826 removed → 7 rows remain | — |
| 5 | Return only ID, Customer, Date, Amount_USD | Clean 4-column output, spilled from A1 | F2 → Ctrl+Shift+Enter (legacy array entry — not needed here) |
The Result
This is what appears in CleanedSales!A1:D8 after running the =PY(...) formula:
| A | B | C | D |
|---|---|---|---|
| ID | Customer | Date | Amount_USD |
| S-7821 | LingTech Pte Ltd | 2024-04-12 | 12450.0 |
| S-7822 | Acme Corp (SG) | 2024-04-12 | 12700.8 |
| S-7823 | Nova Logistics | 2024-04-15 | 8790.17 |
| S-7825 | BrightEdge HK | 2024-04-18 | 8217.6 |
| S-7827 | Zephyr Labs | 2024-04-22 | 8448.18 |
| S-7828 | BrightEdge HK | 2024-04-22 | 8217.6 |
| S-7829 | Nova Logistics | 2024-04-25 | 8790.17 |
What Could Go Wrong
You won’t get a Python traceback — Excel swallows those silently. Instead, you’ll see #VALUE!, #REF!, or blank cells. Here are the three most common failures — and how to spot them before they cost you time:
1. Using xl() with a range that doesn’t exist
If you write xl('RawData!A1:E15') but the sheet only has 10 rows, Excel returns #VALUE! — not an error message. Check your range bounds first. Use =CELL("address",RawData!E10) to verify last cell address.
2. Forgetting dayfirst=True on Asian/EU dates
Without it, '12/04/2024' becomes April 12th in US locale — but your data means December 4th. This quietly corrupts sort order and grouping. Always set dayfirst=True unless your entire org uses mm/dd/yyyy.
3. Hardcoding exchange rates inside =PY()
Yes, it works. But when JPY hits 0.00682 next week, your dashboard stays stale until someone edits 12 formulas. Better: put rates in Settings!B2:C5, then reference them with xl('Settings!B2:C5') inside Python — and reload with one click.
Can I use Python in Excel? Yes — but only if you’re on the right plan
Here’s what’s required — no exceptions:
- Microsoft 365 Apps for enterprise (not Personal or Business Basic)
- Version 2405 or later (check File → Account → Update Options → Update Now)
- Internet connection (Python runtime lives in Azure — no local install)
- No admin rights needed — unlike legacy add-ins
If you’re on M365 E3/E5 and still don’t see Python under Formulas, ask IT to enable “Python in Excel” in the Microsoft 365 admin center → Settings → Org settings → User settings.
Your Next Step — Do This Before Lunch
Open any Excel file with messy data. In a blank sheet, type this in A1:
=PY("import pandas as pd; df = xl('Sheet1!A1:C10'); df['Total'] = df.iloc[:,0] * df.iloc[:,1]; df")Replace Sheet1 with your actual sheet name. Hit Enter. Watch Excel spill a new column multiplying columns A × B. That’s it. You’ve just used Python in Excel — no setup, no risk, no reboot.
