Insert array formulas

The Spreadsheet Editor allows you to use array formulas. Array formulas ensure consistency among formulas in a spreadsheet, as you can enter a single array formula instead of several usual formulas, they simplify working with large amount of data, allow you to quickly fill a sheet with data, and much more.

You can enter formulas and built-in functions as array formulas in order to:

• perform multiple calculations at once and display a single result, or
• return a range of values displayed in multiple rows or/and columns.

There are also specially designated functions which can return multiple values. If you enter them by pressing Enter, they return a single value. If you select an output range of cells to display the results and then enter a function by pressing Ctrl + Shift + Enter, it returns a range of values (the number of returned values depends on the size of the previously selected output range). The list below contains links to detailed descriptions of these functions.

Array functions

• CELL
• CHOOSEROWS
• CHOOSECOLS
• COLUMN
• DROP
• EXPAND
• FILTER
• FORMULATEXT
• FREQUENCY
• GROWTH
• HSTACK
• HYPERLINLK
• INDIRECT
• INDEX
• ISFORMULA
• LINEST
• LOGEST
• MINVERSE
• MMULT
• MUNIT
• OFFSET
• RANDARRAY
• ROW
• SEQUENCE
• SORT
• SORTBY
• TAKE
• TEXTSPLIT
• TOCOL
• TOROW
• TRANSPOSE
• TREND
• WRAPCOLS
• WRAPROWS
• UNIQUE
• VSTACK
• XMATCH
• XLOOKUP

Insert array formulas

To insert an array formula,

1. Select a range of cells where you wish to display results.

   

2. Enter the formula you want to use in the formula bar, specifying necessary arguments within parentheses ().

   

3. Press the Ctrl + Shift + Enter key combination.

   

The results will be displayed in the selected range of cells, and the formula in the formula bar will be automatically enclosed in the curly braces { } to indicate that it is an array formula. For example, {=UNIQUE(B2:D6)}. These braces cannot be entered manually.

Create a single-cell array formula

The following example illustrates the result of the array formula displayed in a single cell. Select a cell, enter =SUM(C2:C11*D2:D11), and press Ctrl + Shift + Enter.

Create a multi-cell array formula

The following example illustrates the results of the array formula displayed in a range of cells. Select a range of cells, enter =C2:C11*D2:D11, and press Ctrl + Shift + Enter.

Edit array formulas

Every time you edit an entered array formula (e.g., change arguments), you need to press the Ctrl + Shift + Enter key combination to save the changes.

The following example explains how to expand a multi-cell array formula when you add new data. Select all the cells which contain an array formula, as well as empty cells next to new data, edit arguments in the formula bar so that they include new data, and press Ctrl + Shift + Enter.

If you want to apply a multi-cell array formula to a smaller range of cells, you need to delete the current array formula and then enter a new array formula.

A part of the array cannot be modified or deleted. If you try to edit, move, or delete a single cell within the array, or insert a new cell to the array, you get the following warning: You cannot change part of an array.

To delete an array formula, select all the cells which contain the array formula and press Delete. Alternatively, select the array formula in the formula bar, press Delete and then press Ctrl + Shift + Enter.

Examples of array formula usage

This section provides some examples on how to use array formulas to perform certain tasks.

Count a number of characters in a range of cells

You can use the following array formula, replacing the cell range in the argument with your own one: =SUM(LEN(B2:B11)). The LEN function calculates the length of each text string in the range of cells. The SUM function adds the values together.

To get the average number of characters, replace SUM with AVERAGE.

Find the longest string in a range of cells

You can use the following array formula, replacing cell ranges in arguments with your own ones: =INDEX(B2:B11,MATCH(MAX(LEN(B2:B11)),LEN(B2:B11),0),1). The LEN function calculates the length of each text string in the range of cells. The MAX function calculates the largest value. The MATCH function finds the address of the cell with the longest string. The INDEX function returns the value from the found cell.

To find the shortest string, replace MAX with MIN.

Sum values based on conditions

To sum values greater than a specified number (2 in this example), you can use the following array formula, replacing cell ranges in arguments with your own ones: =SUM(IF(C2:C11>2,C2:C11)). The IF function creates an array of positive and false values. The SUM function ignores false values and adds the positive values together.

Volver a la página anterior