Skip to content
AnIntent

How to Use ARRAYFORMULA in Google Sheets (and When You Don't Need It)

Use ARRAYFORMULA to run one formula over whole ranges, learn when Sheets expands results without it, and what to do with MAP and LAMBDA.

AnIntent Editorial · · 4 min read

Hands typing on a laptop displaying a data spreadsheet
Photo by Gorilla ROI Data Connector on Unsplash
In this article
  1. What ARRAYFORMULA actually does
  2. How to apply one formula to a whole column with ARRAYFORMULA
  3. When Sheets already expands the result without ARRAYFORMULA
  4. The export catch
  5. When to use MAP and LAMBDA instead
  6. Limits Google's pages don't cover
Share this article

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:

  1. Show a multi-cell result. One formula fills several rows or columns.
  2. 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

  1. Click the top cell of the column where you want the results.
  2. 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
  1. 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)
  1. 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:

  1. Type the formula without ARRAYFORMULA.
  2. If the results fill the neighbouring cells, you're done.
  3. 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 because 3 isn'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.

Common questions

What does Ctrl+Shift+Enter do in Google Sheets?

When you press it while editing a formula, Google's help page says it automatically adds ARRAYFORMULA( to the start of the formula.

Do I always need ARRAYFORMULA to fill multiple cells?

No. Google's ARRAYFORMULA page notes that many array formulas expand automatically into neighbouring cells, so the wrapper isn't always needed.

Can I export a sheet that uses ARRAYFORMULA?

Google's help page states that array formulas cannot be exported. It doesn't say what happens to those cells, so check the exported file before you rely on it.

When should I use MAP with LAMBDA instead of ARRAYFORMULA?

Use it when you want custom logic applied to each item. Google's LAMBDA page gives =MAP(C5:C10, LAMBDA(salary, salary*0.3)) as an example.

Why does my LAMBDA formula return an error?

Google lists two common causes: the name isn't a valid identifier (for example a number or a range like A1), or the LAMBDA isn't called with values or placed inside a helper function such as MAP.

black and silver laptop computer
How-to

How to Start a New Line in an Excel Cell (Windows, Mac and CHAR(10))

Add a line break inside one Excel cell with Alt+Enter on Windows, Control+Option+Return on Mac, or a CHAR(10) formula with Wrap Text on.

· 3 min

More in Cloud & Productivity Software