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

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.

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

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.