Google Sheets ARRAYFORMULA: How It Works and When to Use It

In Google Sheets, ARRAYFORMULA lets one formula calculate across a range and return results into multiple cells. With the numbers 3, 4, and 5 in A2:A4 and the formula entered in B2, =ARRAYFORMULA(A2:A4*2) returns 6, 8, and 10 in B2:B4.

What ARRAYFORMULA Does

ARRAYFORMULA is a Google Sheets function that applies an array-capable expression to a range of values, or allows an expression to return multiple results. Instead of placing a separate formula in every row, you can put one formula in the first output cell and have its results fill the cells needed for the calculation.

Its general syntax is =ARRAYFORMULA(array_formula). The word array_formula is a placeholder for an expression, not something to type literally. A formula can use ranges, arithmetic, and functions that can work with arrays. ARRAYFORMULA is especially useful for repetitive column calculations, such as multiplying quantities by prices or applying the same text cleanup to many entries.

The function does not mean that every Google Sheets function automatically becomes suitable for every range. It helps array-capable expressions return or process multiple values, but the expression still has to make sense for array calculation. It is also different from a function such as FILTER, which is designed to return a set of matching rows by itself.

How Array Results Fill the Sheet

Most often, you enter an array formula in the top-left cell of the intended result area. If its calculation produces several values, Sheets places those results in the cells below or beside that formula cell. Those cells are sometimes described as the formula’s output range. The formula itself remains in its original cell; the other displayed values are results, not separately copied formulas.

For example, with source data in A2:A4, entering a formula in B2 can return three results in B2:B4. If a cell in that output area already contains data, the array result cannot overwrite it and Sheets shows an error. Clear the obstructing cell or move the formula to an empty area. Avoid typing into the output cells: change the formula in its original cell instead.

Array formulas can also work across multiple input ranges. The ranges generally need compatible dimensions: multiplying three values in one range by three values in another produces three paired calculations. If the ranges have different sizes or orientations, the expression may not produce the intended result.

Practical ARRAYFORMULA Examples

One common use is calculating a value for every row without filling a formula down manually. With product names in A2:A4, quantities in B2:B4, unit prices in C2:C4, and the formula entered in D2, use =ARRAYFORMULA(IF(A2:A4="",,B2:B4*C2:C4)). If the quantities are 3, 2, and 5 and the prices are 1.50, 4.00, and 0.80, the results are 4.50, 8.00, and 4.00. The IF test leaves the result blank where the corresponding product-name cell is blank.

You can also apply a text function to a range. With names in A2:A4 and the formula entered in B2, =ARRAYFORMULA(IF(A2:A4="",,LOWER(A2:A4))) converts each nonblank name to lowercase while leaving rows with no name blank. For example, North, EAST, and West become north, east, and west.

Another option is to apply the same test to every score. With scores 82, 45, 50, and 68 in B2:B5 and the formula in C2, =ARRAYFORMULA(IF(B2:B5>=50,"Pass","Review")) returns Pass, Review, Pass, and Pass. The threshold is inclusive: a score of exactly 50 passes.

These examples use bounded ranges rather than entire columns. That keeps the output to the rows in the example and can make a sheet easier to understand. In a live sheet, extend the range as needed or choose a suitable range size for the expected data.

When to Use It—and When Not To

ARRAYFORMULA is most helpful when the same calculation or transformation should apply consistently to a column of records. It can reduce repetitive formula entry, make a single source formula easier to audit, and ensure new rows within the referenced range use the same logic. It is useful for recurring imports, status labels, calculated columns, and simple text transformations.

Use it for row-by-row calculations based on matching ranges, such as quantity multiplied by unit price.
Use it when applying a compatible function or condition to a range is clearer than copying formulas down.
Consider a regular single-cell formula when only one result is needed or when different rows require different logic.
Use a purpose-built array function when it more directly describes the task; for example, FILTER returns rows that meet a condition.

It is not automatically the best choice for every spreadsheet. A long, complicated expression that combines many array operations can be harder for other people to maintain than straightforward formulas in individual rows. Also, if users need to edit each row’s calculation independently, a single formula controlling the whole output may not suit that workflow.

Common Problems and Fixes

A blocked output area is a frequent source of confusion. If the formula should return several rows but one of those cells contains a value, a space, or another formula, Sheets cannot place the full result. Check the cells where the result should appear, clear any content that should not be there, and confirm that the formula is in the intended starting cell.

Unexpected zeros or results in blank rows can happen when an arithmetic expression operates on empty cells. Add a condition that checks a relevant input column and returns a blank for rows without data, as in the examples above. Choose a column that reliably indicates whether the row is in use; checking a price field, for instance, may hide a legitimate row if that field is temporarily empty.

If only one result appears, check whether the expression actually produces an array and whether the referenced ranges have the dimensions you expect. ARRAYFORMULA cannot correct a range that points to the wrong cells. If the output shows an error, read its message and inspect both the formula and its target area before changing the calculation.

Finally, spreadsheet locale settings can affect formula punctuation. The examples here use commas between function arguments, as in many Sheets locales. If your sheet expects semicolons, use the separator required by that locale. Do not confuse argument separators inside a function with the data values being processed.

ARRAYFORMULA Compared with Excel

ARRAYFORMULA is a Google Sheets function; Excel does not have a function with that name. In current Microsoft 365 versions of Excel, many formulas that return multiple results spill into neighboring cells without being wrapped in an equivalent function. Excel also has its own array behavior and function set, so a Sheets formula may need to be adapted rather than copied unchanged.

The basic idea—one formula producing results for several cells—exists in both spreadsheet programs, but the details differ. For example, the examples in this article use ARRAYFORMULA, which is not valid Excel syntax. If you share a workbook between Sheets and Excel, verify the formulas after importing or exporting it, especially when it uses program-specific functions. Do not assume that a formula that works in one application will calculate identically in the other.

Tips for Reliable, Readable Formulas

Put the formula in the first cell of a clear output area and reserve the cells where results will appear.
Use ranges with matching row counts when applying paired calculations.
Exclude headers from the input range unless the formula is intentionally meant to process them.
Add a blank-row condition when calculations on empty inputs would otherwise show unwanted values.
Use clear headings to distinguish input columns from formula-generated output.
Keep a formula as simple as possible, and test it on a small set of known values before expanding the range.

These habits make it easier to spot a mistaken range, explain the output to collaborators, and extend the sheet without accidentally overwriting results. They also help prevent a common misconception: ARRAYFORMULA is not a command that fills arbitrary formulas into every cell. It returns an array result from the expression you supply.

Frequently Asked Questions

What is the ARRAYFORMULA function in Google Sheets?
It lets an array-capable expression process a range or return multiple values from one formula. The result can fill several cells from the formula’s starting cell.

Do I need ARRAYFORMULA for every formula that returns multiple results?
No. Some Google Sheets functions, including FILTER, already return arrays. Use ARRAYFORMULA when it is useful for the expression you are writing, not as a required wrapper around every formula.

Why does ARRAYFORMULA show an error?
Check whether existing content blocks the output, whether the formula refers to the intended cells, and whether input ranges have compatible sizes. The error message can help identify the specific issue.

Can I type into the cells below an ARRAYFORMULA?
Not if those cells are occupied by results from the array formula. Edit the formula in its starting cell, or move it to an empty area if you need separate values in those cells.

Does ARRAYFORMULA work in Microsoft Excel?
No. ARRAYFORMULA is specific to Google Sheets. Excel has different array behavior, and many Microsoft 365 formulas spill results without this function, so adapt Sheets formulas when moving between programs.

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.