An array in Excel is a structure that holds a collection of values. Arrays can be mapped perfectly to ranges in a spreadsheet, which is why they are so important in Excel. An array can be thought of as a row of values, a column of values, or a combination of rows and columns with values. All cell references like A1:A5 and C1:F5 have underlying arrays, though the array structure is invisible in most contexts.
Example
In the example above, the three ranges map to arrays in a “row by column” scheme like this:
B5:D5 // 1 row x 3 columns
B8:B10 // 3 rows x 1 column
B13:D14 // 2 rows x 3 columns
If we display the values in these ranges as arrays, we have:
B5:D5={"red","green","blue"}
B8:B10={"red";"green";"blue"}
B13:D14={10,20,30;40,50,60}
Notice arrays must represent a rectangular structure.
Array syntax
All arrays in Excel are wrapped in curly brackets {} and the delimiters between array elements indicate rows and/or columns. In the US version of Excel, a comma (,) separates columns and a semicolon (;) separates rows. For example, both arrays below contain numbers 1-3, but one is horizontal and one is vertical:
{1,2,3} // columns (horizontal)
{1;2;3} // rows (vertical)
Text values in an array appear in double quotes ("") like this:
{"a","b","c"}
To “see” the array associated with a range, start a formula with an equal sign (=) and select the range. Then use the F9 key to inspect the underlying array.
You can also use the ARRAYTOTEXT function to show how columns and rows are represented. Set format to 1 (strict) to see the complete array. For example, =ARRAYTOTEXT(A1:B3,1)
Delimiters in other languages
In other language versions of Excel, the delimiters for rows and column can vary. For example, the Spanish version of Excel uses a backslash () for columns and a semicolon (;) for rows:
{1\2\3} // columns
{1;2;3} // rows
Arrays in formulas
Since arrays map directly to ranges, all formulas work with arrays in some way, though it isn’t always obvious. A simple example is a formula that uses the SUM function to sum the range A1:A5, which contains 10,15,20,25,30. Inside SUM, the range resolves to an array of values. SUM then sums all values in the array and returns a single result of 100:
=SUM(A1:A5)
=SUM({10;15;20;25;30})
=100
Note: you can use the F9 key to “see” arrays in your Excel formulas. See this video for a demo on using F9 to debug .
Array formulas
Array formulas involve an operation that delivers an array of results. For example, here is a simple array formula that returns the total count of characters in the range A1:A5:
=SUM(LEN(A1:A5))
Inside the LEN function , A1:A5 is expanded to an array of values. The LEN function then generates a character count for each value and returns an array of 5 results. The SUM function then returns the sum of all items in the array.
Dynamic arrays
With the introduction of Dynamic Array formulas in Excel , arrays have become more important, since it is easier than ever to write formulas that work with multiple results at the same time.
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 .
