Array

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. All Excel formulas that use a worksheet reference are working with arrays. In the example shown, the three ranges map to arrays in a "row by column" scheme like this:

B3:D3 // 1 x 3 array
B5:B7 // 3 x 1 array
B9:D10  // 2 x 3 array

Notice arrays must represent a rectangular structure.

Array syntax

All arrays in Excel are wrapped in curly brackets {} and the delimiter between array elements determines whether elements are horizontal or vertical. A comma (,) represents horizontal arrangement, and a semi-colon (;) represents vertical arrangement. For example, both arrays below contain numbers 1-3, but one is horizontal and one is vertical:

{1,2,3} // horizontal array
{1;2;3} // vertical array

Text values in an array appear in double quotes ("") like this:

{"a","b","c"}

As mentioned, arrays are a convenient structure in spreadsheets because they can easily be mapped to the values in a range of cells. The ranges in the example shown map to arrays as follows:

B3:D7={"red","green","blue"}
B5:B7={"red";"green";"blue"} 
B9:D10={10,20,30;40,50,60}

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. SUM then returns the sum of all items in the array.