Fix Python in Excel Errors

A Python formula can fail in more places than an ordinary Excel formula. The code itself can be wrong; the data sent to it can be too large or keep changing; the result can have nowhere to spill; and because the code runs in a Microsoft cloud container rather than on your machine, the connection, the service and your licence can all get in the way too. Each of those failures shows up as a different error value in the cell. This guide, part of Using Python in Excel, sorts them by where the problem is and gives the fix for each.

Prerequisites

• Excel with Python in Excel available, and a workbook with at least one =PY formula.
• Know where the tools are: Formulas → Reset runtime (or Ctrl+Alt+Shift+F9), the Diagnostics pane (right-click a Python cell → Diagnostics for this cell), and File → Options → Advanced, which holds the Python formula timeout setting.

Step 1 — Read #PYTHON! in the Diagnostics pane

#PYTHON! means the code raised an exception: a syntax error, a misspelt column name, a type error, a missing variable. The cell cannot show a traceback, so open the Diagnostics pane for that cell. It lists the exception type, the message and the line number, and it also collects anything your code printed with print().

sales = xl("Sales[#All]", headers=True)
sales.groupby("region")["Amount"].sum()      # KeyError: 'region' — the column is "Region"

The Diagnostics pane shows KeyError: 'region' against line 2. Column names from a table header are case-sensitive and keep any stray spaces, so when a KeyError makes no sense, return list(sales.columns) from a scratch cell and compare character by character. Three other #PYTHON! causes account for most of the rest:

• NameError for a variable defined in another cell — that cell calculates later. Python cells run across each row from column A, then down, sheet by sheet; move the definition above or to the left of its first use.
• ModuleNotFoundError — the package is not part of the curated distribution, and you cannot pip install inside Excel. Use an available library or do that step outside Excel.
• Errors from pd.read_csv, pd.read_excel or requests — the runtime has no access to local files or the network. Bring external data in with Power Query and reference the query with xl().

Step 2 — Make your own errors readable

The Diagnostics pane is fine for the author and useless for everybody else who opens the workbook. For formulas that depend on data other people maintain, check the inputs first and return a clear message instead of letting a raw exception turn into #PYTHON!.

sales = xl("Sales[#All]", headers=True)
required = {"Date", "Region", "Rep", "Amount"}
missing = required - set(sales.columns)

if missing:
    result = f"Sales table is missing column(s): {', '.join(sorted(missing))}"
elif sales["Amount"].isna().all():
    result = "Sales table has no amounts yet"
else:
    result = sales.groupby("Region", as_index=False)["Amount"].sum()
result

Returning a string in place of the expected DataFrame means the cell shows a sentence anyone can act on. The pattern scales to any input check — types, date ranges, duplicates — using the same tests you would run in a script, described in Validate Excel Columns Before Import with pandas.

Step 3 — Fix #CALC! from data size or volatile inputs

#CALC! in a Python cell has two documented causes. The first is size: the data involved in one calculation is limited to 100 MB, and referencing a huge range — whole columns, or a very large table with long text fields — can exceed it. The second is volatility: a formula that references cells whose values change on every recalculation, such as those driven by RAND() or NOW(), cannot settle.

# Too broad: sends every row and every column of a large table
# sales = xl("Sales[#All]", headers=True)

# Narrower: reference only the columns the analysis needs, via a smaller range or table
amounts = xl("Sales!D1:D50000", headers=True)
float(amounts["Amount"].sum())

For size, reference the narrowest range or table that contains what you need, pre-aggregate in Power Query before the data reaches Python, or split the work across cells so that no single formula carries the full dataset. For volatility, move the random or time-dependent value into Python itself — np.random.default_rng(42) or pd.Timestamp.now() — or copy the volatile cells as static values before referencing them.

Step 4 — Clear the way for #SPILL!

#SPILL! means the result was converted to Excel values and needs more cells than are free: something already sits in the block below or to the right. Select the cell to see the dashed outline of the range it wants.

sales = xl("Sales[#All]", headers=True)
summary = sales.groupby("Rep", as_index=False)["Amount"].sum()
summary.head(8)                  # a capped height makes the spill size predictable

Clear the cells in the way, move the formula to an empty area, or switch the output type back to Python object if the result is only an intermediate step. Capping rows with head() and selecting explicit columns stops a result from growing into neighbouring content as the data grows; the full technique is in Return DataFrames and Values from Python in Excel Cells.

Step 5 — Handle #BUSY!, #CONNECT! and #TIMEOUT!

These three come from the cloud runtime rather than from your formula.

• #BUSY! means the calculation is still running in the Microsoft cloud. It is normal for a few seconds. If it persists for more than about a minute, reset the runtime with Formulas → Reset runtime or Ctrl+Alt+Shift+F9.
• #CONNECT! means the connection to the service was lost — a network drop, sleep and resume, or a service interruption. Check connectivity, then reset the runtime.
• #TIMEOUT! means the formula ran longer than the permitted execution time. Retry or reset first; if the formula is genuinely slow, raise the limit under File → Options → Advanced, in the calculation settings for the workbook, where the Python formula timeout is set — and look at making the code faster.

sales = xl("Sales[#All]", headers=True)

# Slow: a Python loop over rows
# totals = {}
# for _, row in sales.iterrows():
#     totals[row["Region"]] = totals.get(row["Region"], 0) + row["Amount"]

# Fast: one vectorised aggregation
sales.groupby("Region")["Amount"].sum()

A reset clears all variables, so every Python cell recalculates from scratch afterwards. Row-by-row loops with iterrows are the usual cause of genuine timeouts; vectorised pandas operations do the same work in a fraction of the time. If your Microsoft 365 plan offers premium compute for Python in Excel, it also shortens queueing on busy formulas.

Step 6 — Resolve #BLOCKED! and #UNKNOWN!

#BLOCKED! means Python formulas are not allowed to calculate for this workbook or account. Python in Excel must be enabled in the workbook, and the account needs access to Microsoft 365 connected services — an administrator can turn those off, and Python in Excel is not supported on device-based subscriptions or shared computer activation. The fix is organisational rather than technical: confirm the licence type and connected-services policy with whoever manages your tenant.

#UNKNOWN! means the version of Excel you opened the file in does not support Python in Excel at all. Mobile apps on iPad, iPhone and Android are the common case. The formulas are still stored in the file and calculate again when it is opened in a supported version, but readers on unsupported clients see only the error — which is a good reason to convert final results to static values before sharing a workbook widely.

Common pitfalls

Performance and scale notes

Most service-side errors get rarer as formulas get lighter. Keep the data you send small, aggregate before charting, compute shared intermediate results once in an early cell and reuse the variable, and use Partial calculation while editing so a single change does not trigger every Python cell in the workbook. When a workbook routinely needs more than the runtime comfortably allows — very large data, external connections, custom packages — the job belongs in a script instead; the trade-offs are laid out in Python in Excel vs openpyxl and xlwings.

Conclusion

Diagnose a Python in Excel error by where it comes from. #PYTHON! is your code — read the Diagnostics pane and check calculation order. #CALC! is the data — narrow it or remove volatility. #SPILL! is the output — make room or cap its size. #BUSY!, #CONNECT! and #TIMEOUT! are the runtime — wait, check the connection, reset, and speed up slow code. #BLOCKED! and #UNKNOWN! are access and version — a licence, policy or client question rather than a code one. For formulas other people rely on, validate inputs and return a readable message before any of these can appear.

Frequently asked questions

Where do print statements go in Python in Excel? To the Diagnostics pane. Right-click the cell and choose Diagnostics for this cell to see printed output along with any error details.

Does resetting the runtime lose my data? No. The workbook's cells and formulas are untouched. The reset clears Python variables in memory, so every Python formula recalculates from the beginning.

Why does a formula work for me but show #BLOCKED! for a colleague? Access is decided per account and client. Their licence type, their organisation's connected-services policy or their Excel version may not allow Python in Excel even though the file is identical.

Can I install a missing Python package? No. Python in Excel uses a curated distribution and does not allow installing packages. Check whether an available library covers the need, or move that step into a script run outside Excel.

Related

• Up one level: Using Python in Excel — the full set of guides for =PY formulas.
• Reference Cells, Ranges and Tables with xl() in Python in Excel — the references behind many #PYTHON! and #CALC! errors.
• Return DataFrames and Values from Python in Excel Cells — avoiding #SPILL! by shaping results.
• Troubleshooting Common Python Excel Errors — errors from scripts that read and write workbooks.
• Handle COM Errors and Excel Dialog Prompts in Python — the equivalent guide for driving Excel from outside.