Write Legacy Array Formulas (Ctrl+Shift+Enter) with openpyxl

Before Excel 365 introduced dynamic arrays, a formula that worked on whole ranges at once — multiplying two columns and summing the products, or taking the maximum of a column where a condition holds — had to be entered with Ctrl+Shift+Enter. Excel then shows it in braces, {=SUM(A2:A5*B2:B5)}, and stores it in the file with a flag that says "evaluate as an array". Workbooks built for older Excel versions, shared with people who still use them, or following a house style written before 2019 are full of these. Writing one from Python is easy with openpyxl, if you know the flag exists: without it the same text is stored as an ordinary formula, and Excel and LibreOffice quietly evaluate only the first row. This page belongs to Working with Excel Formulas in Python.

Prerequisites

pip install openpyxl xlsxwriter

LibreOffice (soffice) is used to recalculate the files and check the results; it is optional but strongly recommended, because neither openpyxl nor XlsxWriter calculates formulas.

Step 1 — See why the flag matters

from openpyxl import Workbook
from openpyxl.worksheet.formula import ArrayFormula

def order_sheet():
    wb = Workbook()
    ws = wb.active
    ws.title = "Orders"
    ws.append(["Qty", "Price", "Line total"])
    for row in [(3, 89.0), (5, 45.5), (12, 14.0), (4, 22.0)]:
        ws.append(row)
    return wb, ws

wb, ws = order_sheet()
ws["E1"], ws["F1"] = "Plain formula", "Array formula"
ws["E2"] = "=SUM(A2:A5*B2:B5)"
ws["F2"] = ArrayFormula("F2", "=SUM(A2:A5*B2:B5)")
wb.save("orders_compare.xlsx")

E2 and F2 contain identical text. The only difference is that F2 is wrapped in ArrayFormula, whose first argument is the cell or range the array occupies. In the file, F2 is written as <f t="array" ref="F2">…</f> and E2 as a plain <f>. The next step shows what that changes.

Step 2 — Recalculate and compare

import subprocess
from openpyxl import load_workbook

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc",
                "orders_compare.xlsx"], check=True, capture_output=True)
values = load_workbook("recalc/orders_compare.xlsx", data_only=True)["Orders"]
print("plain formula E2:", values["E2"].value)
print("array formula F2:", values["F2"].value)

The array formula returns 750.5, the sum of all four line totals. The plain formula returns 267 — the first row's product only. Without the array flag, a range inside a scalar formula is reduced to the single cell on the formula's own row ("implicit intersection"), so A2:A5*B2:B5 became A2*B2. Pre-365 Excel does the same, or shows #VALUE! when the formula sits on a row outside the ranges. No error is raised anywhere; the workbook simply reports the wrong total. This is the strongest reason to verify formulas by recalculating, as described in Recalculate Excel Formulas Without Excel in Python.

Step 3 — A multi-cell array formula

A legacy array formula can also fill a block of cells with one formula — every cell shows its own element of the result:

wb, ws = order_sheet()
ws["C2"] = ArrayFormula("C2:C5", "=A2:A5*B2:B5")
ws["E1"], ws["E2"] = "Highest price for Qty > 4", ArrayFormula("E2", "=MAX(IF(A2:A5>4,B2:B5))")
ws["F1"], ws["F2"] = "Order total", ArrayFormula("F2", "=SUM(C2:C5)")
wb.save("orders_array.xlsx")

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc",
                "orders_array.xlsx"], check=True, capture_output=True)
v = load_workbook("recalc/orders_array.xlsx", data_only=True)["Orders"]
print("line totals:", [v[f"C{r}"].value for r in range(2, 6)])
print("max price where qty > 4:", v["E2"].value, "| total:", v["F2"].value)

The formula is assigned to the top-left cell, C2, and its ref names the whole block, C2:C5. After recalculation the four cells hold 267, 227.5, 168 and 88. MAX(IF(A2:A5>4, B2:B5)) is the classic conditional maximum that needs array evaluation: the IF builds an array of prices where the quantity exceeds 4 and FALSE elsewhere, and MAX ignores the FALSE values, giving 45.5. In Excel the block behaves as a unit: users cannot edit or delete one cell of it, only the whole range.

Step 4 — Read array formulas back

When you load a workbook that contains array formulas, openpyxl gives you the same object back:

ws = load_workbook("orders_array.xlsx")["Orders"]
for coord in ("C2", "C3", "E2"):
    value = ws[coord].value
    if isinstance(value, ArrayFormula):
        print(coord, "ArrayFormula", value.ref, value.text)
    else:
        print(coord, repr(value))

C2 holds an ArrayFormula with ref C2:C5 and its text; C3, which is covered by the array, reads as None — the formula lives only in the top-left cell. Code that audits formulas must therefore check for ArrayFormula objects as well as strings starting with =; a scan that only looks for strings misses every array formula. The audit approach in Audit Every Formula in a Workbook with Python can be extended the same way.

Step 5 — The same with XlsxWriter

XlsxWriter has a dedicated method. Its arguments are the range, the formula and an optional format and cached value:

import xlsxwriter

book = xlsxwriter.Workbook("orders_xw.xlsx")
sheet = book.add_worksheet("Orders")
sheet.write_row(0, 0, ["Qty", "Price", "Line total"])
for i, row in enumerate([(3, 89.0), (5, 45.5), (12, 14.0), (4, 22.0)], start=1):
    sheet.write_row(i, 0, row)
sheet.write_array_formula("C2:C5", "{=A2:A5*B2:B5}")
sheet.write_array_formula("F2", "{=SUM(A2:A5*B2:B5)}", None, 750.5)
book.close()
print(type(load_workbook("orders_xw.xlsx")["Orders"]["F2"].value).__name__)

The braces in the formula string are optional; XlsxWriter strips them. Passing the result as the last argument caches it, so programs that read the file without recalculating see 750.5 instead of 0 — the problem described in Fix Formula Cells Reading as NaN (or 0) in pandas.

Step 6 — Legacy arrays or dynamic arrays?

Excel 365 evaluates every formula as an array formula and spills multi-cell results automatically, so =A2:A5*B2:B5 typed into one cell fills four. Files written for 365 can use those dynamic array formulas instead, covered in Write Dynamic Array Formulas with Python. Legacy array formulas are still the right choice when the workbook must work in Excel 2016 or 2019, in LibreOffice versions without spilling, or in a house style that expects fixed blocks — and they work in Excel 365 too, which opens them unchanged with their braces.

Step 7 — Find plain formulas that should be arrays

Workbooks generated by older scripts may already contain the bug from Step 1. A rough scan finds candidates: ordinary formulas in which a range takes part in arithmetic or a comparison — the pattern that only makes sense under array evaluation:

import re

RANGE_OP = re.compile(r"[A-Z]+\d+:[A-Z]+\d+\s*[*/+\-<>=]|[*/+\-<>=]\s*[A-Z]+\d+:[A-Z]+\d+")

def suspicious_formulas(ws):
    hits = []
    for row in ws.iter_rows():
        for cell in row:
            v = cell.value
            if isinstance(v, str) and v.startswith("=") and RANGE_OP.search(v) and "SUMPRODUCT" not in v.upper():
                hits.append((cell.coordinate, v))
    return hits

legacy = load_workbook("orders_compare.xlsx")["Orders"]
for coord, text in suspicious_formulas(legacy):
    print("candidate:", coord, text)
    legacy[coord] = ArrayFormula(coord, text)
legacy.parent.save("orders_fixed.xlsx")
print(type(load_workbook("orders_fixed.xlsx")["Orders"]["E2"].value).__name__)

The pattern flags =SUM(A2:A5*B2:B5) in E2 and leaves F2 alone, because it is already an ArrayFormula object rather than a string. SUMPRODUCT is excluded because it evaluates its arguments as arrays by itself — which also makes it the usual way to avoid array formulas altogether: =SUMPRODUCT(A2:A5, B2:B5) returns 750.5 as an ordinary formula. Treat the scan as a list of suspects to check, not an automatic fix: a formula such as =A2:A5=B2 might be deliberate, and a recalculation before and after the change shows whether the numbers moved.

Common pitfalls

• Plain text instead of ArrayFormula. The formula silently evaluates one row; always wrap range-wise formulas.
• A ref that does not match the result. In a LibreOffice recalculation, a block smaller than the result showed only the first values, and a larger one filled the extra cells with #N/A; size the block to the result.
• Writing into a covered cell. openpyxl lets you assign a value to C3 inside the C2:C5 array, producing a file Excel complains about; leave covered cells alone.
• Scanning formulas as strings only. ArrayFormula objects are not strings; check for them explicitly.
• Expecting cached results. openpyxl stores none; recalculate or use XlsxWriter's value argument.

Conclusion

Formulas that operate on whole ranges — SUM(A2:A5*B2:B5), MAX(IF(...)) — must be stored with the array flag, or they quietly evaluate a single row. Write them with ArrayFormula(ref, text) in openpyxl or write_array_formula in XlsxWriter, put multi-cell arrays in the top-left cell with a ref covering the block, read them back as ArrayFormula objects, and verify the results by recalculating in LibreOffice. Use dynamic arrays when every reader has Excel 365.

Frequently asked questions

How do I write a Ctrl+Shift+Enter array formula with openpyxl? Assign an ArrayFormula object to the top-left cell: ws"F2" = ArrayFormula("F2", "=SUM(A2:A5*B2:B5)"). The first argument is the cell or range the array occupies.

Why does my SUM of two multiplied ranges return only the first row? It was written as an ordinary formula, so Excel and LibreOffice reduce each range to the cell on the formula's row. Write it as an ArrayFormula so it is evaluated over the whole ranges.

How do I write an array formula that fills several cells? Use ArrayFormula("C2:C5", "=A2:A5*B2:B5") on cell C2. The ref covers the whole block, and the other cells in the block stay empty in openpyxl.

How do I detect array formulas when reading a workbook? Load it without data_only and check isinstance(cell.value, ArrayFormula); the object has ref and text attributes. Covered cells in a multi-cell array read as None.

Related

• Up one level: Working with Excel Formulas in Python — writing, reading and checking formulas.
• Write a Formula to an Excel Cell with openpyxl — ordinary formulas.
• Use Structured Table References in Formulas from Python — formulas that name table columns.
• SUMPRODUCT and Weighted Average in pandas — the same calculation in pandas.