Concept: Array Formulas in Workbooks

An array
is a set of data contained in more than 1 spreadsheet cell. An array visually looks like a simple range of cells, but it's actually a special structure that you work with as a unit. From a Worksheets perspective, live data areas and entry areas are examples of arrays. A formula that deals with a single cell, not an array, is a scalar
formula; the formula returns a result in a single cell.

An array can be one-dimensional, containing values in a single row or column; or two-dimensional, containing a rectangular set of values in both columns and rows. You can see horizontal arrays represented in text with braces around them and commas between the values, such as in the formula ={1,2,3,4}. Vertical arrays have semicolons between the values, such as ={5;6;7;8}. Two-dimensional arrays have both, such as ={1,2,3;4,5,6}.

An array formula
is a single formula that does calculations for the values in an array, and then returns an array of results. The array result area is called the spill
area. Array formulas use the same syntax as regular formulas. You can:

Submit a formula to output the results into an array. All the cells in the array are a single unit that you work with as a single entity. This is a multicell array formula.

Submit a formula in a single cell to operate on an array, while containing the result in a single cell. This is a single cell array formula. Single cell array formulas are much less commonly used compared to multicell array formulas.

You can use many common functions, such as SUM and COUNT, in an array formula. Several functions, such as TRANSPOSE, are meaningful only when they operate on an array.

Keep these considerations in mind when using array formulas:

Array formulas perform better. It's faster to use a 10x100 multicell array formula instead of 1,000 separate formulas.

Using a multicell array formula helps prevent you or other users from accidentally overwriting a cell formula.

You can't edit an individual cell of an array, except for the top-left cell (also called the root cell) in the spill area. If you select another cell in the spill area, the formula is visible in the formula bar, but you can't change it. To update the formula, select the root cell, then change it as needed. Worksheets automatically updates the rest of the spill area when you press Enter.

Constrained and Unconstrained Array Formulas

In a formula, if you specify the exact range of cells to put results in, you're using a constrained array formula. Use a constrained array formula only if you know the specific range that should contain the result set. If you add or remove data in the range, you must modify the array formula to account for it.

Worksheets offers a more flexible method of working with array data. If you're working with live data, such as Workday report data or entry area data, the data array can change size whenever you refresh the live data. The array formula needs to adapt to those changes. Worksheets handles variations in array size using unconstrained array formulas. When you submit a formula without specifying the specific array size for the output, allowing Worksheets to determine the array result set dynamically, you're using an unconstrained array formula.

We use the term unconstrained in a few different contexts:

We call a formula such as GROUPBY an unconstrained array formula
because it's intended to have variable output.

A live data array such as a Workday report data set is an unconstrained array
because the array size can grow or shrink when the live data refreshes.

A range reference such as B:B is an unconstrained column reference
because you're not specifying the number of cells containing data in the column.

Submitting Array Formulas

Submit array formulas from either of these locations:

The formula bar.

The cell containing the formula. Before submitting, double-click in the cell to make sure it is active.

Submit array formulas using these keyboard shortcuts:

To submit an unconstrained
array formula, press Ctrl+Alt+Enter (Windows) or Command+Option+Enter (Mac). Results display in all required cells in the range. If there aren't enough empty cells to display the complete results, an error occurs. Make sure that enough empty cells are available to contain the result.

To submit a constrained
array formula, press Ctrl+Shift+Enter (Windows) or Command+Shift+Enter (Mac). Results display only within the selected range. You see the same behavior in other common spreadsheet products.