Google Sheets ARRAYFORMULA: How It Works and Examples

In Google Sheets, ARRAYFORMULA lets one formula return results for multiple rows or columns, rather than calculating for just one cell. Enter it in the first cell of the output range, for example =ARRAYFORMULA(A2:A10*B2:B10), and Sheets fills the results into the cells below.

What ARRAYFORMULA means in Google Sheets

An array is a collection of values arranged in one or more rows or columns. A regular formula such as =A2*B2 calculates one result. An array formula can apply the same operation to a range, returning a set of results from a single formula.

In Sheets, ARRAYFORMULA is a function that enables array results to appear across multiple cells. It is particularly useful when you want to apply a row-by-row calculation to a whole column without copying a formula down. The formula lives in one cell, usually at the top of a results column, while the results occupy the cells underneath it.

It does not mean that every formula needs to be wrapped in ARRAYFORMULA. Functions such as FILTER, SORT, UNIQUE, and SEQUENCE already return arrays in Google Sheets. A plain range operation can also produce an array result in many cases. The function is most helpful when you need to make a formula that normally returns one value operate on ranges.

How an array formula works

The general structure is =ARRAYFORMULA(array_expression). The expression inside the parentheses can refer to ranges, perform arithmetic, join text, or use functions that can handle array inputs. Sheets evaluates the expression across the supplied values and places the resulting array in the grid, starting at the formula cell.

For example, multiplication between two equally sized ranges pairs values in corresponding positions: the first value in one range is multiplied by the first in the other, the second by the second, and so on. The ranges should line up in size and shape. Multiplying a 20-row range by another 20-row range is a natural row-by-row calculation; mismatched dimensions may produce an error or an unintended result.

When an array formula returns several values, the formula cell is the anchor. The cells it needs to fill must be available. If a value, another formula, or a merged cell occupies part of the output area, Sheets cannot place the full result and reports an expansion error. Clear the obstructing cells or move the formula to an open area.

When to use ARRAYFORMULA

Use it when the same calculation should apply to many records and you want a single formula to maintain. This can make a sheet easier to update: adding a new row within the referenced range may include it in the calculation without copying a formula into a new output cell. It is useful for calculated prices, labels, text transformations, and simple row-level checks.

Repeated arithmetic: calculate a line total from a quantity and a unit price for every row.
Text operations: convert a list of names to uppercase or combine first and last names.
Row-by-row conditions: return a status based on a value in each record.
Reducing formula maintenance: keep one formula at the top of a results column instead of filling individual formulas down.

It is not the right tool for every task. If you need one summary number, such as the total of a column, use an ordinary aggregate formula such as SUM. If a function already returns the precise array you need, wrapping it in ARRAYFORMULA may be unnecessary.

Examples you can adapt

Calculate line totals. Suppose quantities are in A2:A4, unit prices are in B2:B4, and you want totals in column C. Enter this in C2, leaving C3:C4 empty for the results:

=ARRAYFORMULA(IF(A2:A4="", "", A2:A4*B2:B4))

If A2:A4 contains 3, 5, and 2, while B2:B4 contains 4, 2.5, and 10, the result is 12, 12.5, and 20. The IF test leaves a result blank when the quantity cell is blank, instead of displaying an unwanted zero for an unused row. In a live sheet, adjust the range to cover the rows you expect to use.

Join names. With first names in A2:A4 and last names in B2:B4, put this in C2 to create a full name for each row:

=ARRAYFORMULA(IF(A2:A4="", "", A2:A4&" "&B2:B4))

The ampersand joins text, and the quoted space separates the names. If the first-name cells contain Maya, Luis, and Noor and the corresponding last names are Chen, Patel, and Ali, the output is Maya Chen, Luis Patel, and Noor Ali. This example checks the first-name column for blanks; if that column can be empty while the last name is present, adjust the blank test to suit your data.

Return a status for each score. With student names in A2:A4 and scores in B2:B4, enter this in C2:

=ARRAYFORMULA(IF(A2:A4="", "", IF(B2:B4>=70, "Pass", "Review")))

For scores of 82, 64, and 70 beside three student names, the results are Pass, Review, and Pass. The example treats 70 as a passing score; change the threshold or labels to match your own rules.

Entering and editing the formula

In Google Sheets in a web browser, select the first cell where results should appear, type the formula, and press Enter. The formula is visible in that anchor cell; the other cells display its output. To change the calculation, edit the anchor formula rather than trying to edit one of the generated results.

Keep the output range clear before entering the formula. If existing data occupies cells the array needs, move or remove that data first. You can still format the output cells, such as setting a number format, but do not type separate values into the formula’s spill area while expecting the array to continue expanding.

For a long-running sheet, decide whether to use a bounded range such as A2:A500 or an open-ended range such as A2:A. Bounded ranges are easier to reason about and can avoid processing many unused rows. Open-ended ranges can automatically include later entries, but calculations over entire columns may make a complex sheet slower. Choose based on how the data is maintained and how much room the sheet needs to grow.

ARRAYFORMULA versus copying formulas down

Copying a regular formula down gives each row its own formula. This can be useful when different rows need different calculations, or when you want to inspect and edit each formula independently. The trade-off is that new rows may not inherit the formula, and a copied formula can be accidentally changed in one row.

An array formula centralizes the calculation. That reduces repeated formulas and makes a consistent rule easier to maintain, but it also means the output is controlled from one cell. A user who expects to type into an individual result cell may overwrite the array’s output area or encounter an error. Consider labeling the output column and protecting the formula cell if several people edit the sheet.

Google Sheets also has functions that expand their results automatically, so the distinction is not simply “array formulas expand, regular formulas do not.” For instance, FILTER returns matching rows as an array without requiring an ARRAYFORMULA wrapper. Use the function that expresses the task most clearly; do not add a wrapper by habit.

Common problems and how to fix them

The result will not expand: check the cells beneath or beside the formula for existing content, merged cells, or other obstacles. Clear the blocked output area or move the formula.
Only one result appears: confirm that the expression actually refers to a range, not just one cell. Also check whether the function inside the formula returns a single value by design.
Rows do not match: verify that the input ranges start and end on corresponding rows and have compatible dimensions.
Blank rows show zeros or labels: add a blank-row test, as in the examples, and choose which input column should determine whether a row is in use.
The formula gives a parse error: check parentheses, quotation marks, and separators. Sheets locales may use semicolons instead of commas between function arguments; the correct separator depends on the spreadsheet’s locale.

A common misconception is that ARRAYFORMULA automatically detects the intended end of a dataset. It evaluates the ranges in the expression you provide. A reference such as A2:A4 covers three rows; it will not extend itself to row 5. An open-ended reference can include future rows, but formulas that do so should handle empty rows appropriately.

Google Sheets and Excel differences

ARRAYFORMULA is a Google Sheets function, not the standard way to write array calculations in Microsoft Excel. Current Excel versions with dynamic arrays can spill results from supported formulas, and many operations use ranges directly, but the exact functions and behavior differ. A formula copied from Sheets may therefore need to be rewritten for Excel rather than pasted unchanged.

In older Excel versions, some array formulas required selecting an output range and confirming with Ctrl+Shift+Enter. That is different from entering a Google Sheets ARRAYFORMULA in one anchor cell. If a file moves between the two applications, check the formulas and resulting data instead of assuming that array behavior is identical.

Frequently Asked Questions

Do I need ARRAYFORMULA for every formula that uses a range?

No. Some Google Sheets functions and expressions already return arrays. Use ARRAYFORMULA when it helps a formula apply an operation across a range; skip it when the function already produces the desired multi-cell result.

Can I put ARRAYFORMULA anywhere in the output column?

Put it in the first cell where you want the output array to begin, with enough empty cells available for the results. A formula entered lower down starts its output there; it does not fill earlier rows.

Can I edit one result produced by ARRAYFORMULA?

Not independently while it is part of the array output. Edit the formula in its anchor cell or change the source data. If a particular row needs a different result, redesign the formula to include that exception or use separate formulas.

Why does ARRAYFORMULA return an error?

Common causes include blocked output cells, incompatible range sizes, invalid formula syntax, or a function that does not accept the supplied array input as written. Check the error message, clear the intended output area, and test the expression on a small range first.

Does ARRAYFORMULA automatically include new rows?

Only if the formula references those rows. A bounded range stops at its stated last row; an open-ended range such as A2:A can include later rows. Open-ended ranges should be designed to handle blanks and may add calculation work in a large sheet.

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.