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, though most of the time arrays are invisible.
In the example shown, the three ranges map to arrays in a "row by column" scheme like this:
B3:D3 // 1 row x 3 columns B5:B7 // 3 rows x 1 column B9:D10 // 2 rows x 3 columns
If we display the values in these ranges as arrays, we have:
B3:D3={"red","green","blue"} B5:B7={"red";"green";"blue"} B9:D10={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.
A comma (,) represents horizontal arrangement in columns, and a semicolon (;) represents vertical arrangement in rows. For example, both arrays below contain numbers 1-3, but one is horizontal and one is vertical:
{1,2,3} // horizontal array (columns) {1;2;3} // vertical array (rows)
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.
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. The SUM function then returns the sum of all items in the array.
