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: 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:
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. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
