An array constant is a hard-coded set of values provided in an Excel formula. Array constants appear in curly braces {} like this:
{"red","blue","green"}
Array constants are often used in array formulas to create or manipulate several values at once, rather than a single value. For example, the LARGE function can be used with an array constant like this to get the top 3 values in B3:B11:
=LARGE(B3:B11,{1,2,3})
In many cases, formulas that use array constants do not require Ctrl+Shift+Enter, even though they are in fact array formulas.
Multiple results
When you provide an array constant to an Excel function as an argument, you will often receive more than one result in an array. You’ll want to deal with these arrays using another function that handles arrays natively. In the example shown, the LARGE function returns 3 values, which are “caught” by the SUM function, which returns the sum as a final result:
=SUM(LARGE(B3:B11,{1,2,3}))
Array constant limitations
The curly braces surrounding an array constant signify “constant” to Excel’s formula parsing engine. As a result, you can’t include references or functions in an array constant, since doing so would make an array constant variable (i.e. not a constant).
More examples
The formula in the screenshot is explained here .
Another example of array constants inside the COUNTIFS function .
An array formula is a type of formula that 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. For example, the following formula is an array formula that returns the sum of all characters in a range:
{=SUM(LEN(range))}
To work correctly, many (but not all) array formulas need to be entered with control + shift + enter . When you enter with control + shift + enter , you’ll see the formula wrapped in curly braces {} in the formula bar. Do not enter curly braces manually, or the formula won’t work.
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 “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 in 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 formulas are native and do not require control + shift + enter. For a general introduction, see Dynamic Array Formulas in Excel .
