Google Sheets ARRAYFORMULA: How It Works in Practice

In Google Sheets, ARRAYFORMULA applies a formula to a range of cells so one formula can return results for many rows or columns. For example, enter =ARRAYFORMULA(IF(A2:A100="","",B2:B100*C2:C100)) in D2 to calculate each row’s quantity times price, leaving the result blank when the corresponding cell in column A is empty.

What ARRAYFORMULA does

Normally, a spreadsheet formula is entered in one cell and calculates a result for that cell. To repeat the calculation down a table, you might copy or fill the formula into each row. ARRAYFORMULA changes that pattern: it lets a formula work with ranges, producing multiple results from a single formula in Google Sheets.

The function’s syntax is ARRAYFORMULA(array_formula). The argument is the expression you want Sheets to evaluate as an array. An array can contain multiple values arranged across rows, columns, or both. Depending on the expression, the formula can return a list of results or a single summary value.

This is useful in tables that grow over time. Instead of remembering to copy a formula when you add a row, you can write one formula that covers a defined range or, where appropriate, an open-ended column range. That can make a sheet easier to maintain, although a single formula is not automatically better for every task.

How array formulas behave in Sheets

When a formula returns more than one value, Sheets places the results into adjacent cells, starting at the cell containing the formula. This is often called an array result or an expanded result. The cells that receive those results must be available; existing data in the way can prevent the output from appearing.

Array behavior depends on the expression. An operation between matching ranges can calculate row by row, while a function such as SUM can combine values into one result. Ranges used together should normally have compatible dimensions. For a row-by-row calculation, for example, the source ranges should cover the same number of rows.

Google Sheets also supports functions that return arrays without being wrapped in ARRAYFORMULA. Functions such as FILTER, SORT, and UNIQUE can return multiple cells of results directly. So ARRAYFORMULA is a useful tool, not a wrapper that every multi-cell result requires.

Practical examples

For a calculated column, assume column A contains an item name, column B contains quantity, and column C contains unit price. Put this formula in D2, and keep D3:D100 clear for the results:

With item names in A2:A100, quantities in B2:B100, and unit prices in C2:C100, enter =ARRAYFORMULA(IF(A2:A100="","",B2:B100*C2:C100)) in D2. For a row with quantity 3 and price 8, the result in that row is 24. Rows without an item name stay blank.

The formula uses IF to avoid displaying a zero or an unwanted calculation for rows that have not been filled in. It also uses the same row limits for all three ranges. If your table extends beyond row 100, increase each range consistently. You can use open-ended ranges such as A2:A when you want to accommodate future rows, but whole-column calculations can make a large or complex sheet slower.

For a text transformation, suppose names are in A2:A100 and you want a cleaned, title-cased version in B2. Keep the output cells below B2 empty, then enter =ARRAYFORMULA(IF(A2:A100="","",PROPER(A2:A100))). A source value such as “morgan lee” becomes “Morgan Lee.” The blank check keeps unused rows from displaying results.

An array expression can also feed a summary calculation rather than spilling results into many cells. With quantities in B2:B100 and prices in C2:C100, enter =SUM(ARRAYFORMULA(B2:B100*C2:C100)) in a single summary cell. If the row products are 24, 15, and 10, with the other rows empty or zero, the total is 49. This is a compact way to sum row-by-row products; use ranges that match the actual data and ensure text or incomplete entries will not disrupt the calculation.

Where to put the formula and prepare the output

Choose the top-left cell where you want the results to begin, and place the formula there. For a calculated column, that is commonly the first data row beneath the header. Before entering it, check that the cells where results should appear do not contain values, formulas, or merged cells that would interfere with the output.

A practical setup looks like this:

Keep headers in the first row and put the array formula in the first relevant row below the header.
Use matching start and end rows for ranges that are calculated together.
Leave the expected output area clear, including rows that may be filled later.
Test with a short range first if the source data has blanks, text, or unusual values.
When the sheet is shared, label the output column so collaborators understand that one formula supplies the results.

If you put a formula in D2 and it is intended to return values through D100, do not put separate formulas in D3:D100. The array formula is meant to supply that output. Individual edits within the expanded result area may be blocked or may conflict with the formula’s results.

ARRAYFORMULA versus copying a formula down

Copying a regular formula down a column remains a good choice when each row needs independent editing, when the table has a fixed size, or when collaborators expect to see a formula in every row. Relative references adjust as a formula is filled down, and each row can be inspected or changed separately.

ARRAYFORMULA is more convenient when the same calculation should apply uniformly across many rows and you want one formula to maintain. It can reduce missed formulas in new rows and make the logic visible in one place. The trade-off is that the output range is controlled by the array formula, so editing one result independently is not the usual workflow.

For functions that already return arrays, use the function directly unless there is a reason to combine it with other array logic. For example, a filter can produce a changing list of matching rows on its own. Wrapping every such function in ARRAYFORMULA adds no benefit in many cases. Choose based on what the formula needs to do, not simply because its result occupies multiple cells.

Common errors and how to fix them

Output cells are occupied: Clear the cells where the results need to appear. A value, another formula, or sometimes a merged cell can block an array result. Check the full expected output area, not just the cell next to the formula.
Ranges have different sizes: If a row-by-row operation combines B2:B100 with C2:C99, the arrays do not align. Make their start and end rows match, or deliberately adjust the calculation to handle different dimensions.
Unexpected blank-looking results: A formula may return an empty string, such as "", which looks blank but is still a formula result. This differs from a genuinely empty cell and can matter to other formulas or checks.
Results appear for unused rows: An open-ended calculation may process blank rows too. Add a condition that checks a required input column, or use a sensible bounded range. This is why the examples test whether column A is blank.
One error affects the output: A bad source value or incompatible operation can produce an error in an array calculation. Check the row-level inputs and test the expression on a small range to identify the cause before expanding it.
The formula is rejected: Check the parentheses, quotation marks, and argument separators. Google Sheets uses spreadsheet locale settings, and some locales use semicolons rather than commas between arguments. If a copied example does not parse, check the locale and use the separator expected by that sheet.

Do not try to fix an array formula by typing over only the cell that displays an incorrect result. Find and correct the source data or the formula at its origin. If the output is unexpectedly long, review whether the formula uses an open-ended range and whether its condition correctly identifies populated rows.

Choosing a range and keeping a sheet maintainable

A bounded range such as A2:A1000 makes the intended working area easy to see and avoids processing an entire column. It does require updating the formula if the data grows beyond the limit. An open-ended range such as A2:A can include future entries automatically, but it may calculate across many empty rows and can add work in a sheet with numerous complex formulas.

Use clear assumptions about headers and blank rows. If row 1 contains a heading, start the calculation at row 2; including the heading in a multiplication or text operation can cause misleading results. If a row is considered active only when a particular field is filled in, use that field consistently as the blank-check condition.

For a shared workbook, explain which cell contains the controlling formula and avoid leaving stray values beneath it. A brief note in the header or a protected range can help prevent accidental edits, especially when multiple people enter source data. Keep the formula understandable: a straightforward range-based expression is usually easier to troubleshoot than a highly compressed formula with several nested operations.

How Google Sheets differs from Excel

This page describes Google Sheets, where ARRAYFORMULA is a Sheets function. Excel does not support the Google Sheets ARRAYFORMULA function, so that exact formula cannot be transferred unchanged to Excel. Excel for Microsoft 365 has its own dynamic-array behavior: many formulas that return multiple values spill into adjacent cells, and row-by-row calculations can often be written with ordinary range formulas or functions such as MAP or BYROW, depending on the task and Excel version.

Both spreadsheet programs can display results across multiple cells, but their function sets and array rules are not identical. When moving a workbook between them, check the formulas and results rather than assuming a Sheets formula will work in Excel or vice versa. In particular, the name ARRAYFORMULA does not refer to a universal spreadsheet standard.

Frequently Asked Questions

What is the difference between ARRAYFORMULA and an array formula?
An array formula is a formula that works with or returns an array of values. ARRAYFORMULA is the name of a Google Sheets function that can make an expression operate on ranges and return array results. The terms are related, but they do not mean exactly the same thing.

Do I need ARRAYFORMULA for FILTER or UNIQUE?
Usually not. Google Sheets functions such as FILTER and UNIQUE can return multiple results directly. Use ARRAYFORMULA when it helps make another expression work across a range, rather than adding it automatically to every formula that returns several cells.

Why does ARRAYFORMULA show an error instead of filling the column?
First check that the cells beneath or beside the formula are clear. Then confirm that ranges used together have compatible dimensions and that the source values suit the calculation. If the error remains, test a smaller range to narrow down the problematic row or expression.

Can I type into one result cell produced by ARRAYFORMULA?
Not as an independent value while the controlling formula is supplying that part of the output. Change the source data or the formula instead. If some rows need individually editable values, a copied-down formula or a different sheet design may be a better fit.

Does ARRAYFORMULA automatically include new rows?
Only if the formula’s ranges include them. A bounded range such as A2:A100 stops at row 100; an open-ended range such as A2:A can cover later rows. Consider the size and complexity of the workbook when deciding whether to use open-ended ranges.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.