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 final result of an array formula can be either one item or an array of items, depending on how the formula is constructed. To work correctly, many array formulas need to be entered with control + shift + enter. When you enter a formula this way, you'll see the formula wrapped in curly braces {} in the formula bar. 

In Excel 365, array formulas are native and don't require control + shift + enter 

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

In all versions of Excel except Excel 365, many array formulas need to be entered in a special way to work correctly. Instead of entering with the the "Enter" key, they need to be entered with Control + Shift + Enter. You'll sometimes see Control + Shift + Enter abbreviated as "CSE", as in "CSE formula". A formula entered in this way will appear with curly braces on either side:

={formula}

These braces are displayed automatically by Excel. Make sure you do not enter the curly braces manually.

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 that return the same result, but only the SUM version requires Control + Shift + Enter:

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

Excel 365

In Excel 365, array formula are native and do not require control + shift + enter. For a general introduction, see Dynamic Array Formulas in Excel.