Exceljet

Quick, clean, and to the point

Array formula

Example array formula in Excel

An array formula performs an operation on multiple values instead of a single value. The result of this operation is an array of results, sometimes called a resultant array. Because array operations often appear inside other larger formulas, the final result of an array formula can be either one item or an array of items. 

What is an Array?

An array is a collection of more than one item. Arrays in Excel appear inside curly brackets. For example, {1;2;3} or {"red","blue","green"}. The reason arrays are so common in Excel is that they map directly to cell ranges.  Vertical ranges are represented as arrays that use semicolons, for example {100;125;150}. Horizontal ranges are represented as arrays that use commas, for example {"small","medium","large"}. A two dimensional range will use both semicolons and commas.

Examples

Array formulas are somewhat difficult to understand, because the terminology is dense and complex. But array formulas themselves can be very simple.

For example, this array formula tests the range A1:A5 for the value "a":

=OR(A1:A5="a")

The array operation is the comparison of each cell in A1:A5 to the string "a". Because the comparison operates on multiple values, it returns multiple results to the OR function:

=OR({FALSE;FALSE;FALSE;TRUE;FALSE})

If any item in the resultant array is TRUE, the OR function returns TRUE.

Sometimes array formulas supply multiple values as a function argument. For example, this array formula returns the total character count in the range B2:B11:

=SUM(LEN(B2:B11))

The LEN function is given multiple values in the range B2:B11 and returns multiple results in an array like this inside SUM:

=SUM({3;3;5;4;5;4;6;5;4;4})

where each item in the array represents the length of one cell value.  The SUM function then sums all items and returns 43 as the final result.

Special syntax

Many array formulas need a special syntax when entered in Excel. Instead of entering with the the "Enter" key, they need to be entered with Control + Shift + Enter. A formula entered this way will automatically appear with curly braces on either side in the formula bar — do not enter the braces manually.

Note: you'll sometimes see control + shift + enter abbreviated as "CSE".

Not all array formulas need to be entered with  Control + Shift + Enter. Certain functions, like SUMPRODUCT, are programmed to handle array operations natively and usually don't require Control + Shift + Enter.  For example, both formulas below are array formulas, but only the SUM version requires Control + Shift + Enter:

={SUM(LEN(A1:A5))}
=SUMPRODUCT(LEN(A1:A5))

Other functions, like the IF function, require Control + Shift + Enter whenever they are used for an array operation, even when they appear inside of SUMPRODUCT.

To learn more about array formulas, see Mike Girvin's book on the topic: Ctrl+Shift+Enter Mastering Excel Array Formulas