ARRAYFORMULA patterns for reporting sheets

ARRAYFORMULA evaluates an expression across a range and returns the results as one block. A single formula in the second row can fill a calculated column to the bottom of the sheet, and it applies to new rows inside the range. There is one definition to review instead of a column of near-identical copies.

The constraints matter. Arrays in the same expression must line up row for row, or the result returns an error. A full-column reference processes every row, including the thousands that hold no data. And the output needs empty space: if anything sits in the spill area, the formula returns #REF.

The basic pattern applies an operation to every row and leaves blanks blank: =ARRAYFORMULA(IF(ISBLANK(A2:A), , B2:B * C2:C)). Without the guard, empty rows return zero, and those zeros pull down averages and inflate counts.

Wrap per-row errors inside the array so one bad row does not break the column: =ARRAYFORMULA(IFERROR(B2:B / C2:C)). IFERROR is applied element by element, so a division by zero becomes a blank cell while the rest of the column calculates.

Choose the range deliberately. An open range grows with the sheet but scans to the last row; a closed range is faster but needs extension. For an append-only sheet, open ranges are the right trade; for a large import, bound the range.

VLOOKUP accepts an array of keys and returns an array of results: =ARRAYFORMULA(IFNA(VLOOKUP(A2:A, Lookup!A:C, 3, FALSE))). IFNA turns a missing key into a blank without hiding a broken range reference, which IFERROR would do.

XLOOKUP spills on its own and needs no wrapper: =XLOOKUP(A2:A, Lookup!A:A, Lookup!C:C). It is easier to read because the return column is explicit and it does not depend on a column count that shifts when someone inserts a column.

Duplicate keys are a silent problem. Both functions return the first match, so a duplicated lookup table produces a plausible but wrong result. Enforce uniqueness with a COUNTIF validation rule on the key column.

A count per key is straightforward: =ARRAYFORMULA(COUNTIF(Data!B:B, A2:A)). A sum per key is less predictable because SUMIF with an array of criteria does not always behave like a filled-down formula. MAP with LAMBDA is easier to reason about: =MAP(A2:A, LAMBDA(key, SUMIF(Data!B:B, key, Data!F:F))).

Running totals replace the expanding-anchor trick: =SCAN(0, C2:C, LAMBDA(total, value, total + value)). It spills one value per row and does not need a different formula in every cell.

These formulas do repeated work. A MAP that runs a SUMIF for every row scans the source each time, so a few thousand rows can become slow. For large data, group once with QUERY and join the result, or move the aggregation to a pivot.

Month starts and ends are reliable as real dates: =ARRAYFORMULA(EOMONTH(A2:A, -1) + 1) returns the first day of each row's month, and =ARRAYFORMULA(EOMONTH(A2:A, 0)) returns the last. Real dates sort, filter, and join correctly; text labels such as Jan-25 do not.

Keep dates as dates and apply a custom number format for display. A TEXT formula produces text that looks like a date, so downstream comparisons depend on the exact string. Store a date-only value when the report is daily.

Test the boundaries: an end-of-month date, a leap day, and a blank date. EOMONTH handles the calendar, but a blank input returns a date near the epoch unless you guard it with ISBLANK.

The common error is Array result was not expanded because it would overwrite data. Something occupies the output block: a note, a header, a leftover formula. Clear the block rather than shrinking the formula until it fits.

A mismatched range size produces an error or an unexpected result. Check that every referenced range has the same number of rows and that none is a single cell where an array is expected. A single value broadcasts; a range of a different height does not.