Quick, clean, and to the point


Examples of arrays in Excel

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.

In the example above, 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:


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:


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.