Call Python from Excel with an xlwings UDF

Most Python-and-Excel automation runs one way: Python produces a file, somebody opens it. A user-defined function inverts that. You write a Python function, and a spreadsheet user types =REVENUE_FORECAST(B2:B13, 0.04) into a cell and gets the result — with your libraries, your logic, and no VBA. xlwings makes this a decorator and an add-in click. This guide covers the setup, the argument converters that make it pleasant, and the constraints worth knowing before you commit to the approach. It builds on Automating Excel with xlwings: the Basics.

Prerequisites

pip install xlwings
xlwings addin install

Windows with Excel installed. UDFs use COM automation, which exists only in Excel for Windows — there is no macOS or Linux equivalent. If you are producing files on a server, this is the wrong tool entirely; write .xlsx with openpyxl or xlsxwriter instead.

Then, once per machine, enable trust for the VBA object model: Excel → File → Options → Trust Center → Trust Center Settings → Macro Settings → tick "Trust access to the VBA project object model". Without it the add-in cannot register your functions and every call returns #NAME?.

Step 1 — Create the project

xlwings scaffolds a matched pair — a workbook and a Python module with the same stem:

xlwings quickstart forecast_tools
cd forecast_tools

That gives you forecast_tools.xlsm and forecast_tools.py. The names must match: the add-in looks for a module named after the workbook. Renaming one without the other is the second most common cause of #NAME?.

Step 2 — Write the function

Decorate a plain Python function with @xw.func and it becomes a worksheet formula:

# forecast_tools.py
import xlwings as xw

@xw.func
def growth_rate(first, last, periods):
    """Compound growth rate between two values over N periods."""
    if first <= 0 or periods <= 0:
        return "#VALUE! first and periods must be positive"
    return (last / first) ** (1 / periods) - 1

In Excel, click Import Functions on the xlwings ribbon tab, then use it like anything built in:

=growth_rate(B2, B13, 11)

Returning an error string rather than raising is deliberate. An unhandled exception surfaces as a bare #VALUE! with no explanation; a returned string lands in the cell and tells the user what went wrong.

Step 3 — Take ranges as DataFrames

The @xw.arg decorator converts an incoming range before your function sees it, which is what makes UDFs pleasant to write:

import numpy as np
import pandas as pd
import xlwings as xw

@xw.func
@xw.arg("data", pd.DataFrame, index=False, header=True)
def forecast_total(data, growth=0.04, periods=12):
    """Project a monthly revenue column forward and return the total."""
    if "revenue" not in data.columns:
        return "#VALUE! expected a 'revenue' column"

    revenue = pd.to_numeric(data["revenue"], errors="coerce").dropna()
    if revenue.empty:
        return "#VALUE! no numeric revenue values"

    base = float(revenue.iloc[-1])
    projected = base * ((1 + growth) ** np.arange(1, periods + 1))
    return float(revenue.sum() + projected.sum())

=forecast_total(A1:B13, 0.04, 12)

The index=False, header=True pair tells xlwings that the first row of the range is a header and no column should be treated as an index — which matches how a spreadsheet table is usually laid out. Getting these wrong is the usual reason a DataFrame arrives with the header as data.

Step 4 — Return a table

Return a DataFrame and Excel spills it across a range — on current versions using dynamic arrays, so the user types one formula and gets a block:

import pandas as pd
import xlwings as xw

@xw.func
@xw.arg("data", pd.DataFrame, index=False, header=True)
@xw.ret(index=False, header=True)
def summarise_by_region(data):
    """Return a region-level summary as a spilled table."""
    numeric = data.copy()
    numeric["revenue"] = pd.to_numeric(numeric["revenue"], errors="coerce")

    return (
        numeric.groupby("region", as_index=False)
               .agg(orders=("revenue", "size"),
                    revenue=("revenue", "sum"),
                    average=("revenue", "mean"))
               .round(2)
               .sort_values("revenue", ascending=False)
    )

@xw.ret controls the return conversion the way @xw.arg controls the input. Setting header=True writes the column names into the first spilled row, which is almost always what a reader wants.

Step 5 — Design for the round trip

Every call crosses a process boundary. That cost is small in absolute terms and enormous when multiplied by a filled-down column.

Two decorators help when a scalar function is genuinely the right shape. @xw.func(volatile=False) — the default — tells Excel the result depends only on the arguments, so it is not recalculated on every change elsewhere. And caching pays for itself when a function is called repeatedly with the same inputs:

from functools import lru_cache
import xlwings as xw

@lru_cache(maxsize=512)
def _lookup(code):
    """Expensive work — a database or API call — cached across calls."""
    return fetch_rate_from_source(code)

@xw.func
def rate_for(code):
    try:
        return _lookup(str(code).strip().upper())
    except Exception as exc:
        return f"#VALUE! {exc}"

Note that lru_cache lives on the private helper, not the decorated function — xlwings needs the real function object to register it, and caching the wrapper can confuse the argument conversion.

Common pitfalls and fixes

Performance and scale notes

The dominant cost is the boundary crossing, not your Python. A trivial scalar function still pays the full COM round trip, so the practical guidance is entirely about call count.

Take a range, return a range. One call over a thousand rows is roughly a thousand times cheaper than a thousand calls over one row each, and the pandas work inside is vectorised anyway.

Keep imports at module level. Importing pandas inside the function body re-runs the lookup on every call; at module level it happens once when the interpreter starts.

Cache anything external. A UDF that queries a database on each call will make Excel feel broken. lru_cache on the helper turns a thousand identical lookups into one — and where the source is a real database, consider pulling the whole table once with the approach in refreshing an Excel report from a database on a schedule rather than querying per cell.

There is also a design question worth asking before building UDFs at all. They tie the workbook to a Windows machine with Python and the add-in installed — which is fine for a small analyst team and a real obstacle for anything distributed widely. Where the calculation can happen ahead of time, generating a finished workbook is more portable and needs nothing installed on the reader's machine:

# Often the better answer: compute in Python, ship a plain .xlsx.
summary = summarise_by_region(data)
summary.to_excel("regional_summary.xlsx", index=False, engine="xlsxwriter")

Reserve UDFs for the case that genuinely needs them: a user typing new inputs and wanting your logic to respond interactively.

Conclusion

An xlwings UDF turns a Python function into a worksheet formula with two decorators and an add-in click. Match the module name to the workbook, trust the VBA object model, and use @xw.arg with pd.DataFrame so ranges arrive in a shape worth working with. Return error strings rather than raising, so users see a reason instead of a bare #VALUE!. Above all, design for the round trip: take a whole range and spill a whole range back, because a scalar function filled down a thousand rows makes a thousand crossings and turns a workbook unusable. And remember it is Windows-only — if the report just needs producing, generate the file instead.

Frequently asked questions

Do xlwings UDFs work on macOS or Linux? No. User-defined functions require the COM automation layer that only exists in Excel for Windows. On macOS you can still drive Excel with xlwings scripts, and on Linux neither works — use openpyxl or xlsxwriter to produce files instead.

Why does Excel say my function name is not recognised? Either the add-in is not installed, the workbook's module name does not match the Python file, or you have not clicked Import Functions after adding or renaming a function. All three produce the same #NAME? error.

Can a UDF return a whole table? Yes. Return a DataFrame or a list of lists and Excel spills it across a range. On current versions this uses dynamic arrays automatically; on older ones the caller must enter it as an array formula.

Are UDFs fast enough for a large model? Each call crosses the process boundary between Excel and Python, so thousands of individual calls are slow. Design the function to take a whole range and return a whole range, so one call does the work of a thousand.

How do I debug a UDF that returns an error? Set the add-in to debug mode and run the Python file directly, which attaches your interpreter to Excel so breakpoints work. Failing that, wrap the body in a try block and return the exception text so the message lands in the cell.

Related

• Up to the parent: Automating Excel with xlwings: the Basics — the wider xlwings model.
• Read and Write a Live Excel Workbook with xlwings — driving Excel from Python, the other direction.
• xlwings: Run a Macro from Python — calling existing VBA rather than replacing it.
• openpyxl vs xlsxwriter vs pandas.ExcelWriter — the file-generating alternative to a UDF.
• Refresh an Excel Report from a Database on a Schedule — pulling data once instead of per cell.