= Excel Arrays =

'''Excel arrays''' are a data type representing multiple cells worth of data. 

<<TableOfContents>>

----



== Description ==

The Excel data model is oriented around cells. However, there is an advanced data type in Excel arrays.

Most commonly, arrays are instantiated by referencing a range of cells, e.g. `A1:A10`.

An array of literals can be instantiated like:

{{{
={1,2,3,4,5}
}}}

Some Excel formulas also return an array, e.g. [[Excel/Filter|FILTER]].

----



== Spilling ==

If a cell's value is set to an array, especially when using Excel functions, it will '''spill''' across rows into multiple cells. This is sometimes intended, and sometimes not.



=== Preventing Spills ===

To prevent a spill, use the [[Excel/Index|INDEX]] function to select the number of values desired. The `row_num` and `column_num` arguments select the array members to return.

{{{
=INDEX($A$1:$Z$100, {1;2;3}, {1,4})
}}}

`{1;2;3}` is a vertical array literal, communicating that the first three rows should be selected. Likewise, `{1,4}` is a horizontal array literal, communicating that the first and fourth columns should be selected. As demonstrated, the selections '''do ''not''''' need to be contiguous.

To select the first value in an array, just use `{1}` as the vertical array literal.



----
CategoryRicottone