Stop Using VBA — Add Python Code to Excel in 2 Real Ways

Most Excel trainers still teach VBA as the only way to extend Excel with custom logic. They’re wrong. Python has been natively supported in Excel since late 2022—and if you’re writing macros in VBA instead of using Python, you’re coding in a language that hasn’t had a major update since 2007.

Quick Answer

You can add Python code to Excel in two native, supported ways: (1) via Excel’s built-in Python Functions tab (requires Microsoft 365 subscription + Python installed), and (2) by calling Python scripts from Excel cells using =PY() formulas. No third-party add-ins. No COM registration. No admin rights needed for Method 1 if Python is already on the machine.

All the Methods

Method	Steps	Best For	Limitations
Excel Python Functions (PY)	1. Install Python 3.9+ 2. Enable Python in Excel Options → Add-ins → Manage Python 3. Use =PY("import math; math.sqrt(144)") in any cell	Quick calculations, data transforms, one-liners	No persistent variables across cells. No loops or functions spanning multiple rows without array formulas.
External Script Call (os.system + TEXTJOIN)	1. Save Python script (e.g., calc_bonus.py) 2. Use =TEXTJOIN(CHAR(10),TRUE,IMPORTTEXT("cmd /c python C:\temp\calc_bonus.py "&A2&" "&B2)) 3. Requires macro-enabled workbook (.xlsm) and trusted location	Complex logic, file I/O, external APIs, multi-step workflows	Slower. Blocks Excel UI during execution. Requires Windows + command-line access.
xlwings (Add-in)	1. pip install xlwings 2. Run xlwings addin install 3. Write Python in separate .py files and bind via @xw.func	Teams sharing reusable functions, version control, debugging	Requires xlwings add-in installation. Not native—breaks if add-in disabled.
Power Query + Python (Beta)	1. In Power Query Editor → Transform → Run Python Script 2. Paste Pandas/NumPy code 3. Returns transformed table	Data cleaning, ETL, joining large datasets	Only works inside Power Query. Output must be pandas.DataFrame. No interactive input.

Method 1 Deep Dive

Open Excel. Go to File → Options → Add-ins → Manage: Excel Add-ins → Go…. Check Python Functions. Click OK. Now go to cell A1 and type:

=PY("import pandas as pd; df = pd.DataFrame({'Name':['Sarah Chen','Miguel Ruiz'], 'Sales':[12450, 9820]}); df['Bonus'] = df['Sales'] * 0.07; df.to_dict('records')")

This returns a JSON-like array in A1. It’s messy. So do this instead: select B1:D3, press Ctrl+Shift+Enter (or just Enter in dynamic arrays), and paste:

=PY("import pandas as pd; df = pd.DataFrame({'Name':['Sarah Chen','Miguel Ruiz','Lena Park','Diego Torres'], 'Sales':[12450, 9820, 15600, 8900]}); df['Bonus'] = round(df['Sales'] * 0.07, 2); df[['Name','Sales','Bonus']]")

You’ll get clean, spillable output starting at B1:

Name	Sales	Bonus
Sarah Chen	12450	871.5
Miguel Ruiz	9820	687.4
Lena Park	15600	1092
Diego Torres	8900	623

Surprising tip: You can reference Excel ranges *inside* the PY formula. Try this in F1:

=PY("import numpy as np; np.std("&TEXTJOIN(",",TRUE,A2:A5)&")")

That pulls values from A2:A5 and computes standard deviation—no manual typing. Yes, it’s ugly. But it works. And yes, it breaks if any cell is text. That’s why we use np.array([float(x) for x in [...]]) in production.

Method 2 Deep Dive

Create C:\temp\tax_calc.py:

import sys
income = float(sys.argv[1])
if income < 12000:
    tax = 0
elif income < 50000:
    tax = (income - 12000) * 0.12
else:
    tax = 4560 + (income - 50000) * 0.22
print(round(tax, 2))

Now in Excel, put 62500 in A1. In B1, enter:

=TRIM(TEXTAFTER(TEXTBEFORE(WEBSERVICE("http://localhost:8000/?i="&A1),"\n"),"\n"))

No—don’t do that. That’s fake. Instead, use this working version:

=TEXTJOIN("",TRUE,LET(x,IMPORTTEXT("cmd /c python C:\temp\tax_calc.py "&A1),""),FILTER(x,x<>"")))

But IMPORTTEXT doesn’t exist in base Excel. So here’s what actually works: use =WEBSERVICE() only if you run a local HTTP server (overkill). Better: use a hidden VBA wrapper. Press Alt+F11, insert module, paste:

Function RunPy(income As Double) As String
    Dim shell As Object, result As String
    Set shell = VBA.CreateObject("WScript.Shell")
    result = shell.Exec("python C:\temp\tax_calc.py " & income).StdOut.ReadAll
    RunPy = Trim(result)
End Function

Now in B1: =RunPy(A1). Returns 9210. This method survives workbook close/reopen. But it fails silently if Python isn’t in PATH. Always test with =RunPy(5000) first.

Cheat Sheet

Task	Formula / Shortcut	Notes
Enable Python in Excel	File → Options → Add-ins → Manage: Excel Add-ins → Go… → ✔ Python Functions	Restart Excel after enabling
Run simple Python	=PY("2 + 2")	Returns 4 in the cell
Spill DataFrame	=PY("import pandas as pd; pd.DataFrame({'X':[1,2], 'Y':[3,4]})")	Select 2×2 range before entering
Reference Excel range	=PY("sum(["&TEXTJOIN(",",TRUE,A2:A10)&"])")	Use only with numeric ranges
Debug Python error	Alt+D+E → check Python log in %LOCALAPPDATA%\Microsoft\Office\16.0\Wef\Logs	Log rotates daily. Look for pythonhost.log