ARRAYFORMULA is the secret weapon that turns a one-cell formula into a whole-column formula. This tutorial walks through wrapping TEXTJOIN in ARRAYFORMULA so a single cell handles every row at once — including the gotchas that cause it to silently return only the first row.

What ARRAYFORMULA actually does

ARRAYFORMULA tells Google Sheets to evaluate the inner expression once for the whole input range and spill the results down (or across). Instead of pasting =TEXTJOIN(A2) into 1,000 cells, you write =ARRAYFORMULA(TEXTJOIN(A2:A1000)) in row 2 and the answers appear in rows 2–1000. Update the formula in one cell and the entire column updates.

Wrapping TEXTJOIN the right way

=ARRAYFORMULA(IF(LEN(A2:A), TEXTJOIN(A2:A, ...), ""))

Two pieces matter. First, refer to a range (A2:A) rather than a single cell. Second, gate the formula on whether the input row has data — without the IF/LEN guard, ARRAYFORMULA happily produces hundreds of thousands of empty rows of output, slowing the sheet to a crawl. The LEN(A2:A) check returns 0 for empty cells, so IF emits the calculation only when there is something to calculate.

Functions that do and do not array-evaluate

Most arithmetic, text, and lookup functions array-evaluate cleanly inside ARRAYFORMULA. SUMIF, COUNTIF, and many statistical functions do not — wrapping them in ARRAYFORMULA returns just the first cell's result. For those, switch to MMULT, SUMPRODUCT, or BYROW. SORT, FILTER, UNIQUE, and QUERY are already array-aware on their own, so wrapping them in ARRAYFORMULA is unnecessary (and occasionally harmful).

Header row pattern

The cleanest production pattern adds the header inline: =ARRAYFORMULA(IF(ROW(A:A)=1, "Result", IF(LEN(A:A), TEXTJOIN(A:A, ...), ""))). This emits a header in row 1, blanks in empty rows, and the calculation everywhere else. Put it once in cell B1 and the whole B column is owned by that single formula.

Worked examples

Apply TEXTJOIN down a whole column

=ARRAYFORMULA(TEXTJOIN(A2:A))

Use only when TEXTJOIN array-evaluates. Test on a small range first.

Guarded version with empty-row handling

=ARRAYFORMULA(IF(LEN(A2:A), TEXTJOIN(A2:A), ""))

Stops the formula from filling thousands of empty rows.

Header + body in a single cell

=ARRAYFORMULA(IF(ROW(A:A)=1, "Computed", IF(LEN(A:A), TEXTJOIN(A:A), "")))

One cell owns the entire column including the header.

Common mistakes (and how to avoid them)

  • Referencing a single cell (A2) instead of a range (A2:A) — the formula "works" but only fills one row.
  • Wrapping a non-array-aware function: SUMIF/COUNTIF return only the first cell; use SUMPRODUCT or MMULT instead.
  • No empty-row guard, so the spilled formula stretches to row 1,000,000 and the sheet becomes sluggish.
  • Conflicting writes: another cell already owns part of the spill range. ARRAYFORMULA throws #REF! until the conflicting cell is cleared.

Tips that pay off later

  • Use BYROW or BYCOL for functions that resist ARRAYFORMULA — they evaluate the lambda per row.
  • For dependent calculations down a column, SCAN is the array-friendly answer (running totals, running concatenation).
  • Keep ARRAYFORMULA cells in row 1 or row 2 of the column, never deep in the sheet — collaborators expect to find the controlling formula at the top.

When to reach for something else

If your column needs different formulas in different rows, do not force ARRAYFORMULA — copy a normal formula down, or split the column in two.

Functions used in this tutorial

ARRAYFORMULA → TEXTJOIN → BYROW → BYCOL → MAP →