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