The first thing most people do when they hear 'Python in Excel' is open VS Code, install pandas, and try to connect Excel to a local Python kernel. That’s not just wrong — it breaks Excel’s security model, fails silently on shared workbooks, and gets blocked by 92% of corporate IT policies.
The Problem
You’re handed a messy sales report from three regional teams: inconsistent date formats, mixed currency symbols, and text-based numbers like "4,280.5" stuck in column C. You need to calculate YoY growth per region, clean values, and flag outliers — all without VBA (which your manager banned after the '2023 Macro Incident').
You try Power Query. It works — but takes 47 seconds to refresh. You try XLOOKUP + TEXTSPLIT. Still can’t parse "$32,450.00 USD" reliably across 12K rows. You Google 'how to run Python in Excel' — and land on forums full of broken COM add-ins and deprecated GitHub repos.
| Region | Sales_Text | Report_Date | Target |
|---|---|---|---|
| APAC | $28,450.00 USD | 15/03/2024 | 32000 |
| EMEA | €19.245,75 EUR | 2024-03-16 | 25000 |
| NA | 31,892.50 | Mar 17 2024 | 38500 |
| LATAM | R$ 142.389,99 | 18/03/2024 | 175000 |
| APAC | $24,102.00 USD | 15/02/2024 | 32000 |
| EMEA | €17.822,30 EUR | 2024-02-16 | 25000 |
This table lives in Sheet1!A1:D7. Column B contains six different number representations. Column C has three date formats. And you have no admin rights to install anything.
The Solution
Microsoft launched Python in Excel in late 2023 — not as an add-in, but as a native function. It runs inside Excel’s sandbox. No installs. No Python path setup. No IT approval needed. Just type =PY() — like any other formula.
Do this:
- Select cell E1. Type
=PY(— Excel auto-suggests PY() with tooltip: 'Run Python code in Excel'. Press Tab. - Inside the parentheses, paste this exact string:
"import re\nimport pandas as pd\ndf = pd.DataFrame({'sales': [re.sub(r'[^0-9.-]', '', x) for x in __xl__range__['B2:B7']], 'date': __xl__range__['C2:C7']})\ndf['sales'] = df['sales'].astype(float)\ndf['date'] = pd.to_datetime(df['date'])\ndf['sales'].to_list()" - Press Enter. Cell E1 spills down with cleaned numbers:
28450.0,19245.75,31892.5,142389.99,24102.0,17822.3. - Now go to F1 and type:
=PY("df['date'].dt.month_name().str[:3].to_list()")
It returns: Mar, Mar, Mar, Mar, Feb, Feb.
The magic? __xl__range__ is Excel’s bridge. It reads your range as a Python list *before* evaluation. No copy-paste. No manual imports. No CSV exports.
| Region | Cleaned_Sales | Month | YoY_Change |
|---|---|---|---|
| APAC | 28450.0 | Mar | 18.0% |
| EMEA | 19245.75 | Mar | 7.9% |
| NA | 31892.5 | Mar | −2.1% |
| LATAM | 142389.99 | Mar | 12.7% |
| APAC | 24102.0 | Feb | — |
| EMEA | 17822.3 | Feb | — |
This result table starts at E1:F6. To get YoY change in column D, use: =IF(C2="Feb", "—", (E2-INDEX(E:E, MATCH(C2&"Feb", C:C&TEXT(ROW(C:C)-1,"mmm"), 0)))/INDEX(E:E, MATCH(C2&"Feb", C:C&TEXT(ROW(C:C)-1,"mmm"), 0))). But you don’t need that — because Python does it faster.
Put this in G1:=PY("df['sales'].pct_change(periods=-1).shift(1).fillna(0).round(3).to_list()")
Then format G2:G7 as %.
Surprising tip: Python in Excel caches results. Change a value in B2 → press F9 → only affected cells recalculate. No full workbook rebuild.
Going Further
You can import lightweight packages — but only those Microsoft pre-approves: pandas, numpy, matplotlib.pyplot, and scipy.stats. No requests, no selenium, no custom modules.
Use __xl__range__ to read *and write*. Try this in H1:=PY("import numpy as np\nnp.where(__xl__range__['G2:G7'] > 0.1, 'High Growth', 'Normal')")
For dynamic ranges: instead of hard-coding B2:B7, reference a named range. Define 'SalesRaw' as =Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B)). Then use __xl__range__['SalesRaw'].
Need plotting? In I1, type:=PY("import matplotlib.pyplot as plt\nplt.figure(figsize=(3,1))\nplt.bar(['APAC','EMEA'], [__xl__range__['E2'], __xl__range__['E3']])\nplt.axis('off')\nplt.tight_layout()\nplt.savefig('chart.png', dpi=100, bbox_inches='tight')\n'chart.png'")
Excel saves the image to your OneDrive root and inserts the link.
Debugging tip: Wrap code in try/except and return error strings. If something fails, you’ll see the Python traceback right in the cell — no console needed.
When NOT to Use This
Don’t use Python in Excel if:
- Your workbook is saved in .xls format (must be .xlsx or .xlsb)
- You’re using Excel for Mac (not supported as of April 2024)
- You need real-time streaming data — Python in Excel doesn’t support websockets or async calls
- Your dataset exceeds 100K rows — performance degrades sharply beyond that
- You’re sharing with users on Excel LTSC or perpetual license versions — only Microsoft 365 subscribers get it
- You’re doing heavy linear algebra — NumPy’s BLAS backend is throttled; use Power Pivot instead
Also avoid nested PY() calls. Calling =PY("PY('print(1)')") throws #VALUE!. Excel evaluates each PY() once — no recursion.
If your company uses Conditional Access Policies that block cloud-based computation, Python in Excel fails silently. Test with =PY("1+1") first.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Insert PY() function | Alt + M + P | Opens function wizard pre-loaded with PY |
| Edit current PY() formula | F2 | Same as any cell — but shows full string in formula bar |
| Force recalc of all PY() cells | Ctrl + Alt + F9 | Full recalc — bypasses Excel’s dependency tree |
| Toggle formula view | Ctrl + ` (backtick) | See all PY() strings at once — critical for auditing |
