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. We use the term scalar
to refer to a single cell value or a formula that 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. 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. In Worksheets, multicell array formulas are either constrained or unconstrained.

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 less commonly used compared to multicell array formulas.

You can use many common functions, such as SUM and COUNT, in an array formula. A few functions, such as TRANSPOSE, are meaningful only when they operate on an array. The Function Reference identifies functions that are intended for use as array functions.

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 the data in an individual cell of an array. You might want to prevent users from making changes here that would affect other parts of the workbook.

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 (on Windows) or Command+Option+Enter (on 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 (on Windows) or Command+Shift+Enter (on Mac). Results display only within the selected range. You see the same behavior in other common spreadsheet products.

Example: Unconstrained Multicell Array Formula

This example describes how to calculate and display total sales for an item over several months. We'll simulate a situation where the data in the workbook is live data from a Workday report, so Worksheets might add or remove rows when we refresh the data. We'll submit our formulas as unconstrained formulas, and Worksheets will automatically adjust the results as needed when the report data changes and Worksheets runs the formula again.

Here's the data we'll work with:

	A	B	C	D
1	Month	Price	# Sold	Total
2	Jan	$5.00	25
3	Feb	$2.00	4
4	Mar	$1.00	15
5	Apr	$3.00	8
6	May	$4.00	10

Follow these steps:

Create a new workbook.

Create a new sheet called

Data

, and add the data from the table above. Set the format of columns B and D to Currency by selecting the column header, then selecting

Format

Number

Currency

Sheet1

, in the formula bar, type this formula in cell A1, submitting it using the unconstrained keyboard shortcut Ctrl+Alt+Enter (on Windows) or Command+Option+Enter (on Mac):

=Data!A:C

Submitting the formula using the unconstrained shortcut, and using unconstrained column reference A:C instead of a cell range, enable the formula to adapt when we add or remove rows in the data later.

In Sheet1, in the formula bar, type this formula in cell D2, submitting it using the unconstrained keyboard shortcut Ctrl+Alt+Enter (on Windows) or Command+Option+Enter (on Mac):

=REMOVEROWS(B:B*C:C)

The REMOVEROWS formula removes the header row from the calculation. B:B*C:C multiplies each value in column B with each value in column C.

Column D shows the results. If you click any cell in the D2:D6 range, you see the same formula. You can't edit individual cells in the range.

	A	B	C	D
1	Month	Price	# Sold	Total
2	Jan	$5.00	25	$125.00
3	Feb	$2.00	4	$8.00
4	Mar	$1.00	15	$15.00
5	Apr	$3.00	8	$24.00
6	May	$4.00	10	$40.00

In the

Data

sheet, add some data for June into Row 7, then view

Sheet1

again. The new data and sales totals display.