Array Formulas and Why They Break Imports to Databases

The workbook looks complete in Excel. The top row has a formula returning a list of customers, and the column below it fills with names. You load the file with pandas and get the first name, a column of blanks and a warning you do not understand. Or you get all the names, and a rerun after someone edits the sheet loses half of them. Array formulas are stored differently from ordinary formulas, and importers handle them unevenly. This article explains the storage, so the odd behavior makes sense, and gives import strategies that work. It builds on what happens when code reads Excel formulas and complements dynamic array functions FILTER, SORT and UNIQUE.

Three kinds of formula

Excel has three generations of formulas that produce several values, and each is saved differently.

Ordinary formulas produce one value in one cell, and the file stores the formula text and the cached result in that cell.

Legacy array formulas (entered with Ctrl+Shift+Enter in older Excel, shown in braces) may produce a range of values. The file stores them with an attribute telling the reader that the formula is an array formula and which range it covers.

Dynamic array formulas (Microsoft 365 and Excel 2021) spill automatically. The file stores a similar structure, with an additional flag in the cell metadata that marks the formula as dynamic, so that Excel knows the range can grow and shrink.

In all three, the cells covered by the array, apart from the anchor, hold values that are results and not formulas.

What the XML looks like

A sheet is XML. The anchor cell of a legacy array formula over B2:B4 looks like this:

<c r="B2"><f t="array" ref="B2:B4">TRANSPOSE(D2:F2)</f><v>10</v></c>
<c r="B3"><v>20</v></c>
<c r="B4"><v>30</v></c>

The first cell holds the formula in an f element of type array, with ref naming the full range, and its cached value in v. The other two cells have values only. There is no formula in them.

A dynamic array adds a cell metadata index (cm) on the anchor cell, pointing at a metadata part that says "this is a dynamic array". Without it, Excel reads the same f t="array" as a legacy array formula, with fixed size, and shows the braces. That is why a formula that spilled when created in Excel can show as {=...} and refuse to resize after a library rewrites the file without the metadata.

An ordinary formula is <c r="C2"><f>SUM(A2:B2)</f><v>7</v></c>. If a program wrote the file and did not calculate, the v element is missing, and so is the cached value.

What importers do with that

Reading values. pandas.read_excel uses openpyxl with data_only=True, which means it reads the cached v elements. For a legacy or dynamic array, the anchor and the spilled cells all have cached values, so a file saved by Excel imports correctly. The values you see are what the formula returned at the last calculation.

Reading formulas. With data_only=False, openpyxl returns the formula for the anchor cell (an ArrayFormula object, with .ref and .text) and the plain cached values for the others, so you get one formula and a column of numbers, which looks inconsistent.

Files with no cached values. A workbook written by openpyxl, XlsxWriter or a similar library stores formulas without calculating them. Reading it with data_only=True returns None for every formula cell, including the array results, and pandas shows NaN. Excel calculates when it opens the file, so people see the numbers and the loader does not (handling Excel formulas and cached values).

Stale cached values. A workbook left in manual calculation mode, or saved before a source cell changed, holds old results. The loader reads them faithfully. Spilled results are especially prone: if the source grew, the cached block may be shorter than the true result.

Rewriting. If a script loads a workbook with openpyxl and saves it, the cached values are dropped for formula cells, because openpyxl writes formulas without values. The array's spilled cells keep their values but the anchor loses its cache, and the dynamic flag may be lost. After such a round trip, an Excel user may see @ added to a formula, or a spill that behaves as a fixed array.

Symptoms and causes

The error strings are real cached values. If Excel computed #N/A, the file stores it as an error cell (t="e"), and the loader returns the string #N/A or a null depending on the library.

Importing safely

Prefer the source data. If the array formula is a filter or a transformation of data that exists elsewhere in the workbook, import the underlying sheet and do the transformation in the database. A pipeline that depends on =FILTER(...) output depends on Excel having calculated it.

Recalculate before reading. For a file generated by a program, open it once in a calculation engine and save. LibreOffice headless does this:

soffice --headless --convert-to xlsx --outdir recalculated/ input.xlsx

The converted file has cached values for formulas that LibreOffice supports. Check functions that LibreOffice lacks, which come back as errors. Then read recalculated/input.xlsx with data_only=True.

Paste as values when you own the file. If you control the workbook, add a step that copies the array result to a values-only sheet, and import that sheet. The simplest durable contract between a workbook and a loader is a sheet of plain values.

Compare counts. After an import, compare the row count with what Excel shows, and compare one control total. A stale or truncated spill shows up there (validating row counts after an Excel import).

Detect arrays on the way in. If you must load many third-party workbooks, scan for them: with openpyxl, a cell whose value is an ArrayFormula instance, or a raw scan of the sheet XML for t="array", tells you to treat that range with care and perhaps reject it.

from openpyxl import load_workbook
from openpyxl.worksheet.formula import ArrayFormula

wb = load_workbook("input.xlsx")          # formulas, not values
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for c in row:
            if isinstance(c.value, ArrayFormula):
                print(ws.title, c.coordinate, c.value.ref, c.value.text)

This lists the anchors, their ranges and their text, so you can decide what to do with each.

Writing array formulas from code

When a library writes a formula for a function that returns an array, there are traps. openpyxl writes legacy array formulas with ArrayFormula("B2:B4", "=TRANSPOSE(D2:F2)") and sets the range explicitly. For a dynamic array function such as FILTER, writing the formula as a plain string causes Excel to open it with an implicit intersection operator, showing =@FILTER(...) and returning a single cell. XlsxWriter has a write_dynamic_array_formula() method that sets the metadata correctly. If your generated workbook must spill, use the library's explicit support and open the result in Excel to confirm. If it only needs the numbers, write values.

Performance and size

An array formula over large ranges recalculates as one unit, so a change to any input recalculates everything. Whole-column references inside array formulas are the common cause of a workbook that takes minutes to open (slow Excel formulas over many rows). From the file's side, the cached values double the data: the formula is small, and the results are stored in full.

When array formulas are not the cause

If a column is blank after loading and the file has no array formulas, the cause is more likely a plain formula without cached values, a hidden sheet, a header offset or a merged cell. Check data_only, the sheet name and the header row first (Excel merged cells and hidden rows). For a file that opens in Excel but not in your library, the problem is probably a format or corruption issue rather than arrays (fixing Excel found unreadable content). The openpyxl documentation on formulas and the XlsxWriter dynamic arrays page describe the writing side.