Arrays represent any m by n grid of contiguous values, where m is the number of rows and n is the number of columns. Each cell in an array can contain any data type, including mixed types. Conceptually, an array is a rectangular collection of cells.
All rows of an array must share the same number of columns, and all columns must share the same number of rows.
Array Subtypes
Ranges
Arrays fall into two primary categories: ranges and virtual arrays.
A range refers directly to cells in a spreadsheet and retains contextual information such as the row or column from which it originates. Certain functions, such as SUMIF or OFFSET, require range data to function correctly.
Virtual Array
A virtual array is any array that does not reference range data. These arrays are typically produced by constructing an array literal or by passing a range through most spreadsheet functions. Virtual arrays exist only in memory and are not directly tied to spreadsheet cells.
Array Literals
Array literals are constructed arrays defined using curly braces {} to produce a virtual array.
{<item>,<item>;<item>,<item>}The comma (,) separator delimits values within a row and the semicolon (;) separator delimits different rows. Elements are processed horizontally first, then vertically.
All rows within an array literal must contain the same number of elements. To create jagged arrays, use the VSTACK and HSTACK functions instead.
Vectors
Vectors are one-dimensional arrays with dimensions of either (row vector) or (column vector). Many functions specifically operate on vectors, often referring to “a single row or column.” Vectors are conceptually distinct from two-dimensional arrays, even though they share structural similarities.
Jagged Arrays
Jagged arrays consist of rows or columns of varying lengths padded with null values to align dimensions.
In Google Sheets, jagged arrays behave similarly to regular arrays and are especially useful when combined with LAMBDA Helper Functions. In Excel, however, they are generally considered bad practice due to inconsistent behavior across functions.
Notes
Non-vector arrays containing over 2,147,483,647 (2^31 - 1) elements will crash the sheet. Any array containing over 10,000,000 elements will output #VALUE!.