Write LET and LAMBDA Formulas from Python

LET gives names to intermediate results inside a formula — =LET(net, A2*(1-B2), net + net*C2) computes net once and reads like a small program. LAMBDA goes further and turns a formula into a reusable function that a workbook can name, such as =GrossUp(A2, B2). Both are Excel 365 functions, and both are among the easiest formulas to get wrong from Python: type =LET(...) into openpyxl exactly as you would in Excel, open the file, and the cell shows #NAME?. The formula is fine; what is missing is how Excel stores newer functions and their parameters inside the file. This page, part of Working with Excel Formulas in Python, shows the stored form, a helper that produces it, and how to check the result.

Prerequisites

pip install openpyxl xlsxwriter

LET needs Excel 2021 or Microsoft 365 and LAMBDA needs Microsoft 365; older versions show #NAME? whatever you do. LibreOffice 24.8 and later calculate LET but not LAMBDA, which is enough to test half of this page on a machine without Excel.

Why the formula shows #NAME?

Excel's file format predates these functions. To stay readable by older versions, Excel writes every function added after 2007 with a prefix, and writes the names you introduce inside LET and LAMBDA with another one:

When Excel shows you the formula it strips the prefixes; when it saves, it adds them back. openpyxl and xlsxwriter write your text as it is, so text without the prefixes reaches Excel as a call to an unknown function called LET, and a reference to an undefined name called net. The same rule is behind #NAME? on XLOOKUP, FILTER and friends — see Fix #NAME? Errors for New Excel Functions Written by Python.

Step 1 — Write a LET formula with openpyxl

Write the stored form directly. The workbook below computes a price after discount and then adds tax to it, naming the intermediate value once:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["List price", "Discount", "Tax rate", "Gross"])
for row in [[100, 0.2, 0.25], [80, 0.1, 0.25], [250, 0.0, 0.1]]:
    ws.append(row)
for r in range(2, 5):
    ws[f"D{r}"] = f"=_xlfn.LET(_xlpm.net,A{r}*(1-B{r}),_xlpm.net+_xlpm.net*C{r})"
wb.save("let_openpyxl.xlsx")
print(ws["D2"].value)

Every occurrence of the parameter needs the prefix — its definition and each use. Missing one gives #NAME? in Excel for that reference only, which can be hard to spot in a long formula. Spaces after commas are allowed but not needed; Excel stores the formula without them.

Step 2 — Generate the prefixes instead of typing them

Typing _xlpm. by hand invites mistakes. A small helper can take the readable form and produce the stored one: it prefixes LET and LAMBDA, finds the parameter names they declare, and prefixes every use of those names that is not part of a cell reference, a sheet name or a string:

import re

NEW_FUNCS = ("LET", "LAMBDA", "XLOOKUP", "FILTER", "SORT", "UNIQUE", "SEQUENCE", "TEXTJOIN", "IFS", "SWITCH")

def to_stored(formula):
    text = formula.replace(", ", ",")
    params = set()
    for m in re.finditer(r"\bLET\(([^()]*)", text):
        parts = m.group(1).split(",")
        params.update(p.strip() for p in parts[0:-1:2])
    for m in re.finditer(r"\bLAMBDA\(([^()]*)", text):
        params.update(p.strip() for p in m.group(1).split(",")[:-1])
    for name in sorted(params, key=len, reverse=True):
        text = re.sub(rf'(?<![\w.!"$])\b{re.escape(name)}\b(?![\w(!])', f"_xlpm.{name}", text)
    for fn in NEW_FUNCS:
        text = re.sub(rf"(?<![\w.]){fn}\(", f"_xlfn.{fn}(", text)
    return text

print(to_stored("=LET(net, A2*(1-B2), net + net*C2)"))
print(to_stored("=LET(rate, C2, gross, A2*(1+rate), ROUND(gross, 2))"))
print(to_stored("=LAMBDA(x, rate, x/(1-rate))(A2, B2)"))

LET declares names in pairs — name, value, name, value, … result — so the names are every other argument except the last; LAMBDA declares all its arguments except the last as parameters. The helper deliberately handles only simple bodies without nested parentheses in the declaration list, which covers the formulas most report scripts write. Anything more elaborate is easier to maintain written out in stored form, or, better, calculated in pandas and written as values.

Step 3 — Check the result without Excel

LibreOffice evaluates LET, so it can recalculate a copy of the file and prove the formulas work. Write the formulas through the helper, convert with headless LibreOffice, and read the calculated values back:

wb = Workbook()
ws = wb.active
ws.append(["List price", "Discount", "Tax rate", "Gross"])
for row in [[100, 0.2, 0.25], [80, 0.1, 0.25], [250, 0.0, 0.1]]:
    ws.append(row)
for r in range(2, 5):
    ws[f"D{r}"] = to_stored(f"=LET(net, A{r}*(1-B{r}), net + net*C{r})")
ws["F1"] = "=LET(x, 2, x*21)"          # no prefixes: what goes wrong
wb.save("let_check.xlsx")

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated let_check.xlsx > /dev/null 2>&1
ls recalculated

from openpyxl import load_workbook

ws = load_workbook("recalculated/let_check.xlsx", data_only=True).active
print([ws[f"D{r}"].value for r in range(2, 5)], ws["F1"].value)

The three gross prices come back as 100, 90 and 275 — net price plus tax — while the unprefixed formula in F1 is #NAME?, the same result Excel gives. This check is worth keeping in a test suite; Recalculate Excel Formulas Without Excel shows how to wrap it.

Step 4 — Named LAMBDA functions

A LAMBDA becomes truly useful when it is stored as a defined name: the workbook gains a function, GrossUp, that any cell can call. The definition is a defined name whose value is the stored LAMBDA; the calls are ordinary formulas. xlsxwriter supports this directly, and writes the calls as dynamic array formulas so Excel does not insert an implicit-intersection @:

import xlsxwriter

book = xlsxwriter.Workbook("grossup.xlsx")
sheet = book.add_worksheet("Pay")
sheet.write_row("A1", ["Net pay", "Tax rate", "Gross needed"])
for i, (net, rate) in enumerate([(3000, 0.2), (4200, 0.3), (1800, 0.0)], start=2):
    sheet.write_row(f"A{i}", [net, rate])
    sheet.write_dynamic_array_formula(f"C{i}", f"=GrossUp(A{i},B{i})")
book.define_name("GrossUp", "=" + to_stored("=LAMBDA(net, rate, net/(1-rate))")[1:])
book.close()

import zipfile
xml = zipfile.ZipFile("grossup.xlsx").read("xl/workbook.xml").decode()
print(re.search(r"<definedName.*?</definedName>", xml).group(0))

In Excel 365, GrossUp then appears in the Name Manager and in formula autocomplete like a built-in function, and changing its definition updates every cell that calls it. LibreOffice cannot evaluate LAMBDA, so this part has to be checked in Excel itself before the workbook goes out. openpyxl can write the same defined name — wb.defined_names["GrossUp"] = DefinedName("GrossUp", attr_text=...) — but its cell formulas are written as ordinary formulas, which is fine for single results.

xlsxwriter's use_future_functions option

xlsxwriter can add the _xlfn. prefixes itself: xlsxwriter.Workbook(path, {"use_future_functions": True}). It does not add _xlpm. to LET or LAMBDA parameters, though — a LET written through it is stored as _xlfn.LET(net,...) with bare names. Keep using a helper, or the stored form, for the parameters. The option also has a cost: xlsxwriter scans every formula with a list of function names, which slows down very formula-heavy workbooks slightly.

Common pitfalls

• Prefix on the function but not the parameters. _xlfn.LET(net, ...) still leaves net undefined as far as Excel is concerned.
• A parameter that looks like a cell reference. Names such as A1, R2C3 or TAX2026 collide with references in some reference styles. Use descriptive names with underscores: tax_rate.
• Reading the formula back. openpyxl returns the stored text with prefixes; strip them if you show formulas to people.
• Sharing with older Excel. Excel 2019 and earlier show #NAME? for LET and LAMBDA no matter how they are stored. If the audience is mixed, calculate in Python and write values.
• Cached values. Neither library calculates formulas, so the file has no stored results until Excel or LibreOffice opens it — see Store Formula Results with xlsxwriter.

Conclusion

Excel stores LET and LAMBDA as _xlfn.LET and _xlfn.LAMBDA, and every name they declare as _xlpm.name, at the declaration and at each use. Write that stored form from openpyxl or xlsxwriter — generated by a helper rather than typed — check LET workbooks by letting LibreOffice recalculate a copy, and define named LAMBDA functions as defined names whose calls are written as ordinary or dynamic array formulas. Check LAMBDA workbooks in Excel 365 itself.

Frequently asked questions

Why does my LET formula show #NAME? in Excel? The file must contain _xlfn.LET and _xlpm. before each parameter name, which is how Excel stores these newer functions. openpyxl and xlsxwriter write your text exactly as given, so a formula typed as =LET(x, 1, x+1) is stored without the prefixes and Excel does not recognise it.

What is the _xlpm prefix? It marks a name as a parameter declared inside LET or LAMBDA, as opposed to a defined name or a cell reference. Excel adds it when it saves the file and hides it when it shows the formula.

Can openpyxl create a named LAMBDA function? Yes. Add a defined name whose value is the stored LAMBDA, _xlfn.LAMBDA(_xlpm.x,...), and write cell formulas that call the name. xlsxwriter's define_name does the same and can write the calls as dynamic array formulas.

Can LibreOffice calculate LET and LAMBDA? Recent LibreOffice versions calculate LET, so a headless LibreOffice recalculation is a good test. LAMBDA is not supported and evaluates to an error there, so named LAMBDA workbooks must be checked in Excel 365.

Related

• Up one level: Working with Excel Formulas in Python — writing, reading and recalculating formulas.
• Write Dynamic Array Formulas with Python — FILTER, SORT, UNIQUE and spill ranges.
• Write a Formula to an Excel Cell with openpyxl — the basics this page builds on.
• Create a Named Range in Excel with openpyxl — the defined-name API used for LAMBDA.