Array in Google Sheets: How It Works

In Google Sheets, an array is a set of values arranged in rows or columns, and an array formula can calculate across several values at once. With quantities in B2:B and prices in C2:C, enter =ARRAYFORMULA(B2:B*C2:C) in D2 to calculate a total for every row.

What an array means in Google Sheets

The word “array” describes a collection of values, not one particular function. A range such as A2:A5 is a vertical array of four cells; A2:C2 is a horizontal array; and A2:C5 is a two-dimensional array with rows and columns. Formulas can take these groups as inputs and, in some cases, return a group of results.

This is useful when the same calculation or transformation applies to many records. Instead of writing a separate formula beside every row, a single formula may process an entire range. Arrays also appear when a function returns multiple matches, a sorted list, or a set of unique values.

It helps to distinguish an array from an array formula. An array is the collection of values. An array formula is a formula that works with an array, or produces an array as its result. Google Sheets also uses the term ARRAYFORMULA for a function that enables array-style evaluation in formulas that might otherwise operate on one value at a time.

How array results appear

When a formula produces several results, Google Sheets generally places them in neighboring cells, starting at the cell containing the formula. This is often called an expanding or spilling result. For example, if the formula is entered in D2 and returns five values vertically, those values occupy D2:D6. The formula itself remains in D2; the other cells display its results.

The destination cells need to be available. If text, a formula, or another entry is in the way, Sheets cannot place the complete result and displays an expansion error. You typically do not type separate formulas into the result cells: edit the formula in its starting cell, or clear that formula to remove its output.

Array results can expand down a column or across a row. The direction and size depend on the formula and its inputs. A function that returns a table can produce results in both directions, so check the surrounding cells before entering it. This behavior makes one-formula calculations convenient, but it also means a formula can affect cells beyond the one where it was typed.

Using ARRAYFORMULA

ARRAYFORMULA tells Google Sheets to apply an operation to an array of values and return array results where supported. It is especially useful for calculations that would otherwise need to be copied down each row. In a browser, type the formula in the top cell of the intended output column and press Enter; no special menu command is needed.

With item names in A2:A, quantities in B2:B, and unit prices in C2:C, this formula entered in D2 returns a line total for each populated record:

=ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C))

The IF test leaves the result blank when the corresponding item-name cell is blank, which helps avoid a long column of apparent zeroes. The multiplication operates row by row: B2 is multiplied by C2, B3 by C3, and so on. The open-ended ranges include future rows, so new records can be included without copying the formula. In a very large sheet, however, using ranges limited to the expected data area can make formulas easier to manage.

Not every formula needs the wrapper. Several Google Sheets functions already return arrays, and some range calculations expand without it. Use ARRAYFORMULA when it makes a formula work across a range, not simply because the formula refers to multiple cells. If a formula returns only one value, wrapping it may not change the result.

Practical array formula examples

Arrays can shorten repetitive work in a table. For example, with first names in A2:A and last names in B2:B, enter this in C2 to combine corresponding names while leaving rows blank when the first-name cell is empty:

=ARRAYFORMULA(IF(A2:A="", "", A2:A&" "&B2:B))

The ampersand joins text, and the quoted space separates the names. If the source ranges have gaps, the formula still evaluates row by row; the blank check determines whether a result appears.

With product names in A2:A, this formula returns a sorted list of distinct names:

=SORT(UNIQUE(A2:A))

UNIQUE removes repeated values, and SORT orders the resulting list. Because this result is generated by those functions, an ARRAYFORMULA wrapper is not needed. Depending on the range, blank cells may also appear in the result; use a filtering condition if blanks should be excluded.

These examples illustrate two different patterns: applying a calculation to corresponding rows, and generating a new list from existing data. Knowing which pattern you need helps you choose a straightforward formula rather than adding wrappers unnecessarily.

Array literals and separators

An array can also be written directly inside a formula using braces. This is called an array literal: it lets you combine fixed values or ranges into a small in-formula table. In many Google Sheets locales, commas separate columns and semicolons separate rows. The exact separators can vary with spreadsheet locale, so a pasted example may need adjustment.

No worksheet cells are required for this standalone example, which creates a two-column, three-row array:

={"Item","Qty";"Pens",4;"Books",2}

The first row contains headings, and the following rows contain item-and-quantity pairs. Entered in an empty cell, it fills the cells to its right and below. Array literals are handy for small lookup tables, labels, or combining compatible pieces of data, but they are less convenient than a normal sheet range when the information changes frequently. For changing data, keeping values in cells is usually easier to maintain.

Common problems and how to fix them

Array result cannot expand: Clear any existing content in the cells where the result needs to go. Check for entries farther down or to the right, including a single space that is not visually obvious.
Ranges have different sizes: Row-by-row operations generally need corresponding ranges with matching dimensions. If one range starts on row 2 and another starts on row 3, results may be misaligned or produce an error.
Unexpected blanks or zeroes: Whole-column references include empty rows. Add a condition that checks a required field, or use a bounded range that matches the data.
Formula copied into every row: An array formula normally belongs in its starting cell only. Remove duplicate formulas from the output area, then let the single formula return its results.
Unexpected separator error: Check the spreadsheet’s locale and use the separator style expected by that file. Commas and semicolons can serve different roles in array literals and may differ across locales.

When troubleshooting, read the error message and identify the formula’s intended output range. Then inspect that area for obstructions and verify that each input range lines up row by row. If the result is hard to inspect, test the formula on a few rows first, using bounded references, before extending it to an entire column.

Google Sheets arrays compared with Excel

Google Sheets and modern Microsoft Excel both support formulas that return multiple results into neighboring cells, but their array features are not identical. Google Sheets has an ARRAYFORMULA function; Excel does not. Excel for Microsoft 365 uses dynamic-array behavior for many formulas, while older Excel versions may require different methods for some array calculations.

A formula written for one spreadsheet program may therefore need changes in the other, even if both use familiar functions such as UNIQUE or SORT. Array-literal separators and function availability can also differ by program, version, and locale. If a workbook is shared between Sheets and Excel, check the formulas after conversion rather than assuming that every array formula will behave the same way.

Frequently Asked Questions

What does ARRAYFORMULA do in Google Sheets?

It lets supported formulas work with arrays of values and return multiple results. It can replace repeated row-by-row formulas, although many Sheets functions already return arrays without an ARRAYFORMULA wrapper.

Where should I put an array formula?

Enter it in the first cell where you want the result to appear, usually at the top of an empty output column or table. Leave enough room for the result to expand in every direction it needs.

Can I edit one cell in an array result?

Usually not while that cell is controlled by the formula in the starting cell. Change the source data or the original formula instead. To replace the generated values with independent entries, copy the results and paste values where appropriate.

Why does my array formula show an expansion error?

One or more cells needed for the output are occupied, or the formula cannot return a consistent result for its inputs. Clear the destination area and check that corresponding ranges have compatible sizes.

Is ARRAYFORMULA the same as an array literal?

No. ARRAYFORMULA is a Google Sheets function that enables array calculations. An array literal is a group of values written inside braces, with separators between its rows or columns.

Choosing the simplest approach

For repeated row calculations, use one array formula when it makes the sheet easier to update. For filtered, sorted, or distinct lists, start with the function designed to return that result. Use an array literal for small fixed sets of values, not as a substitute for a maintained data table. In all cases, leave clear space for generated results and make the input ranges align. Those habits keep array formulas readable and reduce surprises as the sheet grows.

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.