What is Excel Array?

In Excel functions and formulas, an array is a collection of data elements in one row, one column, or multiple rows and columns. Array elements can be numeric, text, date, logical and error values.

The dimension of the array is the direction of the rows and columns of the array. An array with one row and multiple columns is a horizontal array, and an array with one column and multiple rows is a vertical array. An array with multiple rows and columns has both vertical and horizontal dimensions.

The dimensionality of an array is the number of different dimensions in the array. An array with only one row or column is called a one-dimensional array; an array with two dimensions with multiple rows and columns is called a two-dimensional array.

The size of an array is expressed by the number of elements in each row and column of the array.

• A one-dimensional horizontal array with 1 rows and N columns has a size of 1xN
• A one-dimensional vertical array with 1 column and N rows has a size of Nx1
• A two-dimensional array with M rows and N columns has a size of MxN

Excel Array Types

Constant Array

Constants arrays are string expressions that are written directly to the array elements in a formula and are identified by curly brackets “{}” at the beginning and end.

Constant arrays do not depend on the cell range, can be directly involved in the calculation of the formula.

Constant array elements can not be functions, formulas or cell references. Numeric constant elements can not contain dollar signs, commas and percent signs.

One-dimensional Array

The elements of a one-dimensional vertical array are separated by a colon “:“, the following is an array of numeric constants of size 5x1.

={10;20;30;40;50}

The elements of a one-dimensional horizontal array are separated by a comma “,”, and the following is an array of numeric constants of size 1×5:

={10,20,30,40,50}

Note: For text-based constant arrays, each element in the array is identified by quotation marks by default.

Two-dimensional Array

The elements of a two-dimensional array are separated by a semicolon “;” on each row and a comma “,” on each column.

The following is a 4×3 two-dimensional array of mixed data types containing numeric, text, date, logical, and error values.

10	20	30
A	B	c
#N/A!	#REF!	#NUM!
TRUE	FALSE	TRUE

={10,20,30;"A","B","c";"#N/A!",#REF!,#NUM!;TRUE,FALSE,TRUE}

The process of manually entering a constant array can be tedious, you can use cell references to simplify the input of constant groups, the steps are as follows:

STEP1# Enter the value of the array element in the cell area, such as A1:A4

STEP2# Enter the formula =A1:A4 in cell A5

STEP3# In the formula bar, select the above formula and press F9, the formula can be converted to a constant array

Array in Excel Range

Range array is actually a formula directly referenced in the cell range, the size of the array and the size of the constant is exactly the same. For example, the following formulas A1:A4 and B1:B4 are range arrays.

=SUMPRODUCT (A1:A4*B1:B4)

Array in Memory

A memory array is an array temporarily formed in memory by multiple values returned by a formula calculation. Memory arrays do not have to be stored in the cell range, and as a group can be directly nested in other formulas to continue to participate in the calculation. For example:

{ =SMALL(A1:A4,{1,2,3})}

In the above formula, {1,2,3} is a constant group, and the entire formula results in a memory array of 1 row and 3 columns consisting of the smallest 3 numbers in the range of cells A1:A4.

Here is the array in memory.

={10,20,30}

The difference between memory array and area array：

• Range array is obtained by cell range reference, memory array is obtained by formula.
• Range Array depends on the referenced cell range, the memory array exists independently in memory.

Name an array constant

A named array is a constant array, a range array, or a memory array defined using a named formula (i.e., a name) that can be called as an array in a formula.

Note: You cannot use constant arrays directly in custom formulas used for data validation and conditional formatting, but you can use named arrays created through the Name Manager.