Table of Contents

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

## Leave a Reply

You must be logged in to post a comment.