Exceljet

Quick, clean, and to the point

Array constant

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 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.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables