Write Dynamic Array Formulas like FILTER and UNIQUE from Python

Write spilling Excel formulas — FILTER, UNIQUE, SORT, SEQUENCE, XLOOKUP — from Python so they work when the file opens. Why openpyxl output shows #NAME? or an @, what the _xlfn prefix and array metadata are, and how xlsxwriter handles both.

Formulas in workbooks openpyxl xlsxwriter

Dynamic array formulas changed how Excel works: =UNIQUE(A2:A500) or =FILTER(Data, Data[Amount]>1000) in one cell spills its results into as many cells as it needs, and the output resizes itself when the data changes. They are ideal for reports that Python builds and people then explore — the script writes the data and one formula, and Excel keeps the derived list current. Writing them from Python has one catch: a formula that is perfectly valid when typed into Excel can open as #NAME? or with a stray @ when written by a library. This guide, part of Working with Excel Formulas in Python, explains why and how to write them so they spill.

Prerequisites

Bash

pip install openpyxl xlsxwriter

The examples inspect the saved files directly, so no copy of Excel is needed to follow along. An .xlsx file is a zip archive, and the formula text and its attributes are plain XML inside it:

Python

import re
import zipfile

def formula_xml(path: str, sheet: str = "xl/worksheets/sheet1.xml") -> list[str]:
    xml = zipfile.ZipFile(path).read(sheet).decode()
    return re.findall(r'<c r="[A-Z]+\d+"[^>]*>(?:(?!</c>).)*<f[^>]*>(?:(?!</c>).)*</c>', xml)

Step 1 — See what openpyxl writes

openpyxl stores formula text verbatim. Write a dynamic array formula the way you would type it and look at the result:

Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
for row in [["Region", "Sales"], ["North", 10], ["South", 20], ["North", 30], ["East", 40], ["South", 15]]:
    ws.append(row)
ws["D2"] = "=SORT(UNIQUE(A2:A6))"
wb.save("openpyxl_dynamic.xlsx")

print(formula_xml("openpyxl_dynamic.xlsx"))
# ['<c r="D2"><f>SORT(UNIQUE(A2:A6))</f><v></v></c>']

Two things are missing, and each causes a different symptom when Excel opens the file:

The _xlfn prefix. Functions added after Excel 2010 are stored in the file format with a prefix — _xlfn.UNIQUE, _xlfn._xlws.SORT, _xlfn._xlws.FILTER. Without it, SORT and UNIQUE are unknown names and the cell shows #NAME?.
The dynamic array metadata. A spilling formula is marked with cm="1" on the cell and a metadata.xml part in the package. Without it, Excel treats the formula as a pre-2019 formula, inserts the implicit intersection operator @, and returns a single value instead of spilling.

Step 2 — Write it properly with xlsxwriter

xlsxwriter knows both rules. write_dynamic_array_formula adds the prefixes and the spill metadata for you:

Python

import xlsxwriter

book = xlsxwriter.Workbook("dynamic.xlsx")
ws = book.add_worksheet("Sales")
ws.write_row(0, 0, ["Region", "Sales"])
data = [("North", 10), ("South", 20), ("North", 30), ("East", 40), ("South", 15)]
for i, (region, sales) in enumerate(data, start=1):
    ws.write_row(i, 0, [region, sales])

ws.write("D1", "Regions")
ws.write_dynamic_array_formula("D2", "=SORT(UNIQUE(A2:A6))")
ws.write("F1", "Big orders")
ws.write_dynamic_array_formula("F2", "=FILTER(A2:B6, B2:B6>15)")
book.close()

for cell in formula_xml("dynamic.xlsx"):
    print(cell)
print("xl/metadata.xml" in zipfile.ZipFile("dynamic.xlsx").namelist())   # True

The output shows cm="1" on each cell and the formulas stored as _xlfn._xlws.SORT(_xlfn.UNIQUE(A2:A6)) and _xlfn._xlws.FILTER(A2:B6,B2:B6>15). You write the formula exactly as you would type it; xlsxwriter translates. Even plain write_formula recognises dynamic functions such as UNIQUE, SORT and FILTER and applies the same treatment, which makes it hard to get wrong.

Step 3 — Point dynamic formulas at a table

Dynamic arrays pair naturally with Excel tables: a table grows when rows are added, and a formula over its columns keeps up. Combine the two so the report stays correct after someone pastes new data:

Python

book = xlsxwriter.Workbook("dynamic_table.xlsx")
ws = book.add_worksheet("Sales")
ws.add_table("A1:C6", {
    "name": "Orders",
    "data": [["North", "Ana", 10], ["South", "Ben", 20], ["North", "Cy", 30],
             ["East", "Dee", 40], ["South", "Eve", 15]],
    "columns": [{"header": "Region"}, {"header": "Rep"}, {"header": "Sales"}],
})
ws.write("E1", "Top reps (sales > 15)")
ws.write_dynamic_array_formula("E2", "=SORT(FILTER(Orders[Rep], Orders[Sales]>15))")
ws.write("G1", "Total by region")
ws.write_dynamic_array_formula("G2", "=UNIQUE(Orders[Region])")
ws.write_dynamic_array_formula("H2", "=SUMIFS(Orders[Sales], Orders[Region], ANCHORARRAY(G2))")
book.close()
print(formula_xml("dynamic_table.xlsx")[-1])

ANCHORARRAY(G2) is how the file format stores the spill-range operator that Excel displays as G2#: it refers to whatever G2 spills, however many rows that is, so the SUMIFS beside it produces one total per region and resizes with the list. Write the function form from Python — xlsxwriter adds the _xlfn. prefix to ANCHORARRAY but passes a literal G2# through untouched, and a # in the stored formula is not valid file content. Structured references such as Orders[Sales] are covered in Use Structured Table References in Excel Formulas from Python.

Step 4 — If you must use openpyxl

Sometimes the workbook is being edited with openpyxl for other reasons and you only need to add one dynamic formula. openpyxl cannot write the spill metadata, so the best it can do is the prefixed text, which Excel will open as a single-value formula with @. You can still get a correct result in that case by writing a legacy array formula over a fixed range, where the size is known in advance:

Python

from openpyxl.worksheet.formula import ArrayFormula

wb = Workbook()
ws = wb.active
for row in [["Region", "Sales"], ["North", 10], ["South", 20], ["North", 30], ["East", 40], ["South", 15]]:
    ws.append(row)
ws["D2"] = ArrayFormula("D2:D4", "=_xlfn._xlws.SORT(_xlfn.UNIQUE(A2:A6))")
wb.save("openpyxl_array.xlsx")
print(formula_xml("openpyxl_array.xlsx"))

This writes a classic array formula across D2:D4 — the braces {=…} style. It does not resize, so if a fourth region appears it will not be shown, and if one disappears the last cell shows #N/A. It is a workaround for known-size outputs, not a replacement. When the output size can change, write the workbook, or at least that sheet, with xlsxwriter.

Step 5 — Check generated workbooks before shipping

Whichever library produced a workbook, a short scan catches the two faults before a reader does: dynamic functions stored without their prefix, and dynamic-array cells without the spill metadata. The scan reads the sheet XML directly, so it runs in milliseconds and needs no Excel:

Python

DYNAMIC = ("FILTER", "UNIQUE", "SORT", "SORTBY", "SEQUENCE", "XLOOKUP", "XMATCH", "LET", "RANDARRAY")

def dynamic_formula_problems(path: str) -> list[str]:
    problems = []
    with zipfile.ZipFile(path) as z:
        for name in z.namelist():
            if not (name.startswith("xl/worksheets/sheet") and name.endswith(".xml")):
                continue
            for cell, attrs, text in re.findall(r'<c r="([A-Z]+\d+)"([^>]*)>\s*<f[^>]*>([^<]*)</f>', z.read(name).decode()):
                used = [fn for fn in DYNAMIC if re.search(rf"(?<![._\w]){fn}\(", text)]
                if used:
                    problems.append(f"{name}:{cell} unprefixed {used}")
                elif "_xlfn." in text and any(fn in text for fn in DYNAMIC) and 'cm="1"' not in attrs:
                    problems.append(f"{name}:{cell} no spill metadata")
    return problems

print(dynamic_formula_problems("openpyxl_dynamic.xlsx"))   # ["xl/worksheets/sheet1.xml:D2 unprefixed ['UNIQUE', 'SORT']"]
print(dynamic_formula_problems("dynamic.xlsx"))            # []

Run it as a final step in the job that produces the workbook, and fail the run if it returns anything. It is also a quick way to audit workbooks produced by older scripts: every entry is a cell that shows #NAME? or a single value where a list was intended.

Common pitfalls

Testing with LibreOffice. LibreOffice calculates SORT, UNIQUE and FILTER, but it does not spill dynamic arrays loaded from an .xlsx file; a recalculation returns only the first value. Check the formula text and cm attribute as shown here, or open the file in Excel.
Spill collisions. A spilling formula needs empty cells below or beside it. If Python also writes values into that area, Excel shows #SPILL!. Leave the spill range empty.
Older Excel versions. Excel 2016 and earlier open dynamic formulas as legacy arrays. If recipients may use old versions, compute the list in pandas and write values instead.
Reading results back. A file written by Python has no cached results, so load_workbook(..., data_only=True) returns None for these cells until Excel has opened and saved the file. See Read Formula Results with openpyxl data_only.

Performance and scale notes

A dynamic array formula costs nothing to write — it is one cell — and Excel evaluates it quickly even over tens of thousands of rows. The practical limit is the spill size: a FILTER that returns 100,000 rows is better computed in pandas and written as data, because a huge spill range slows recalculation every time anything changes. Use formulas for the derived views a reader interacts with, and values for bulk output.

Conclusion

Excel needs two things in the file for a spilling formula: _xlfn prefixes on post-2010 functions and the dynamic array metadata. openpyxl writes neither, so its formulas open as #NAME? or @. xlsxwriter's write_dynamic_array_formula — and write_formula for recognised functions — writes both, and pairs well with tables and the # spill operator. Verify by inspecting the saved XML rather than by recalculating in LibreOffice.

Frequently asked questions

Why does my UNIQUE formula show #NAME? when written with openpyxl? Functions added to Excel after 2010 must be stored in the file with a prefix such as _xlfn. openpyxl writes the formula text exactly as you give it, so UNIQUE without the prefix is an unknown function when the file opens.

Why does Excel put an @ in front of my formula? The formula was saved without the metadata that marks it as a dynamic array, so Excel opens it as a legacy formula and adds the implicit intersection operator. It then returns a single value instead of spilling.

Does xlsxwriter handle the prefixes automatically? Yes. write_dynamic_array_formula adds the prefixes and the spill metadata, and write_formula detects dynamic functions such as FILTER, SORT and UNIQUE and does the same.

Can LibreOffice calculate these formulas for testing? Not reliably. Recent LibreOffice versions support the functions but do not spill dynamic arrays loaded from xlsx files, so recalculating with LibreOffice returns only the first value. Test the formula text and metadata instead, or open the file in Excel.

Up one level: Working with Excel Formulas in Python — writing and reading formulas.
Write a Formula to an Excel Cell with openpyxl — the basics of formula text.
Building Excel Reports with xlsxwriter — the library that handles dynamic arrays.
XLOOKUP Equivalent in pandas — when to compute in Python instead.

More on formulas in workbooks