Google Sheets ARRAYFORMULA lets one formula work across a whole range, so you don't have to copy it into every row. This guide covers how to write one, when Sheets already expands results without it, the export limitation, and when to use MAP with LAMBDA instead.
What ARRAYFORMULA actually does
Google's ARRAYFORMULA help page says the function "enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays."
That gives it two jobs:
- Show a multi-cell result. One formula fills several rows or columns.
- Let ordinary functions accept ranges. Functions that normally expect a single value can work through a range.
The syntax is:
ARRAYFORMULA(array_formula)
According to Google, array_formula can be any of these:
- a range
- a mathematical expression that uses one cell range, or several ranges of the same size
- a function that returns a result larger than one cell
Google's page gives two sample formulas:
=ARRAYFORMULA(A1:C1+A2:C2)
=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))
The first adds two ranges of the same size. The second shows the "non-array functions with arrays" job: IF checks every cell in A1:A10 and SUM totals the results.
How to apply one formula to a whole column with ARRAYFORMULA
- Click the top cell of the column where you want the results.
- Type a formula that refers to ranges instead of single cells. If you combine more than one range, make them the same size, as Google's argument rules require. For example, to multiply column A by column B for rows 2 to 10:
=A2:A10*B2:B10
- While the cursor is still in the formula, press Ctrl+Shift+Enter. Google's help page says this shortcut automatically adds
ARRAYFORMULA(to the start of the formula. You can also type the wrapper yourself:
=ARRAYFORMULA(A2:A10*B2:B10)
- Press Enter.
The formula lives in the first cell only, and its results appear in the cells below. To change the calculation, edit that one cell.
The A2:A10 example above is an illustration of Google's same-size rule. It does not come from Google's page. Check it against your own data before relying on it.
When Sheets already expands the result without ARRAYFORMULA
You often don't need the wrapper. Google's page notes that many array formulas expand automatically into neighbouring cells, so ARRAYFORMULA isn't needed explicitly.
Try this order instead of wrapping by habit:
- Type the formula without ARRAYFORMULA.
- If the results fill the neighbouring cells, you're done.
- If you see only one value, or the function doesn't accept a range, add the wrapper with Ctrl+Shift+Enter.
If your results need to stay within a set size, Google's ARRAYFORMULA page links to a related function, ARRAY_CONSTRAIN, which "constrains an array result to a specified size."
If you also use Excel, it has its own expanding-result behaviour and its own error when the output is blocked. See how to fix the #SPILL! error in Excel.
The export catch
Google's ARRAYFORMULA page states plainly: "Array formulas cannot be exported."
The page doesn't say what happens to those cells in a downloaded file, so don't assume either way. If a sheet will leave Google Sheets, open the exported copy and check the columns that used array formulas before you send it to anyone.
When to use MAP and LAMBDA instead
ARRAYFORMULA gets awkward when you want custom logic applied to each item. For that, Google documents the LAMBDA helper functions. The LAMBDA help page says to use LAMBDA inside a helper function "to perform advanced array-operations," and gives this example:
=MAP(C5:C10, LAMBDA(salary, salary*0.3))
This runs salary*0.3 on each value in C5:C10. The name salary stands for the current item.
The syntax is LAMBDA(name, formula_expression). Google lists these rules for the name:
- It can't be a range such as
A1. - It can't contain spaces or special characters. Dots and underscores are allowed.
- It can't start with a number.
Google names two common errors:
=LAMBDA(3, x+1)(3)fails because3isn't a valid name.=LAMBDA(salary, salary*0.3)on its own errors because nothing calls it. Pass it values, as in=LAMBDA(salary, salary*0.3)(C5), or put it inside a helper such as MAP.
Limits Google's pages don't cover
Neither Google help page mentions limits by plan or platform. This guide doesn't cover how ARRAYFORMULA behaves in the Sheets mobile apps.
If you're building a tracker with tick boxes, you can point a range formula at a checkbox column. See how to insert checkboxes in Google Sheets.