Python for Excel

Overview

Install the add-in . Create an Excel function from a Python function in two easy steps as follows:

1. Write a Python function

Using the add-in code editor, write or paste your function code, e.g.:

def inches_to_mm(inches):
    """ Converts inches to millimeters. """
    return inches * 25.4

2. Run as an Excel function

Python code is stored in workbook settings, and the function is added to the name manager as follows:

=LAMBDA(inches, BOARDFLARE.EXEC("inches_to_mm", inches))

The function is now available for use in the workbook as follows:

=INCHES_TO_MM(inches)

See the slideshow  for a quick overview of usage.

How it works

Let’s use another example to explain the process in more detail. Suppose you have the following Python function:

def hello(first, last):
    """ Returns a greeting. """
    greeting = f"Hello {first, last}!"
    return greeting

When you save this code, the Python function name hello and arguments first, last are parsed to create a LAMBDA function =LAMBDA(first, last, BOARDFLARE.EXEC("hello", first, last)) which is saved to the name manager with the name HELLO. The Python code for the function named hello is saved to the workbook settings  so it is embedded in the workbook. The first line of the Python function docstring is also added as a comment to the name manager to provide an autocomplete description.

Each time the function is invoked, BOARDFLARE.EXEC loads the code from the workbook settings and runs it with the arguments. Since the code and LAMBDA are both stored in the workbook, anyone who uses the workbook can use the function. If they don’t have the add-in installed, Excel will automatically prompt them to do so.

Features

Some key features of the add-in are as follows:

✅ Use Python in formulas and LAMBDA functions.
🆓 Free add-in, no Office 365 license required.
🌐 Works in Excel for web as well as desktop.
☁️ Runtime has network access for API calls (needs CORS).
📦 Import custom packages (pure Python only).
🔒 Code is stored in your workbook and runs locally.

Limitations

• Your Python code must be a function. All data is passed to the function as arguments.
• Range references, e.g. xl("Sheet1!A1:A10"), cannot be used to pass data to the code.
• See Excel Type Conversion for how Excel types are converted to Python types.
• *args and **kwargs are not supported because LAMBDA functions do not support repeating arguments.
• If your version of Excel does not support LAMBDA functions, functions will be inserted in EXEC MODE, e.g. =BOARDFLARE.EXEC("hello", "John", "Doe") instead of =HELLO("John", "Doe"). Otherwise the work the same.
• Note all packages can be imported, and some may not work as expected. See Importing Packages for more details.

Importing Packages

Pyodide  is the Python runtime used by the add-in, and includes a number of built-in packages . You can import any of these packages as well as those from the Python standard library. Any imports for external Python packages will be loaded from PyPI , but only if they are pure Python, and depend on any packages that are either built into pyodide or also pure Python. Packages tagged as OS-independent  on PyPI should work. If you try to import an external package (not part of the Python standard library or one of the built-in packages) for which there is not a pure Python wheel available on PyPI, an error will be thrown

Your code will be scanned for imports and any packages needed will be installed for you using micropip. However, specific imports (e.g., from azure.core.credentials import AzureKeyCredential) will not be handled automatically, so you need to install these manually using await micropip.install(['package1', 'package2']). micropip has already been imported for you, and since the Python code is executed async, you can use a top-level await as follows:

await micropip.install(['azure-ai-textanalytics'])
from azure.core.credentials import AzureKeyCredential
# Continue with your code...

Type Conversion

The following type conversions will take place on the arguments passed to your function from Excel:

*Excel stores dates as numbers in serial format, and since we have no way to distinguish these from non-date numbers, they are not converted to datetime objects, and simply become a Python int. If you want to convert an Excel serial to a Python datetime, you can use the following code:

from datetime import datetime, timedelta
 
def excel_date_to_datetime(serial):
    return datetime(1899, 12, 30) + timedelta(days=serial)

The value returned by your Python function will similarly be converted to the corresponding Excel type as follows:

*Assumes formula is in cell A1.

If your function returns another Python type such as a list, set, or other non-scalar types, an error will be thrown. We have support for converting numpy and pandas types to help reduce errors, but recommend you not rely on these.

FAQ

Changelog

See GitHub releases  for the latest updates.