Array Constants

Array constants are a list of values that can be used as arguments in your array formulas.
Arrays can be either 1-dimensional or 2-dimensional depending on the number of rows and columns.
A 1-dimensional array can exist in a single row or a single column.
You can create an array constant and you can give it a name that can then be used in your formulas.

Array constants are a list of values that can be used as arguments in your array formulas.
Arrays can be either 1-dimensional or 2-dimensional depending on the number of rows and columns.
A 1-dimensional array can exist in a single row or a single column.
One dimensional array {1, 2, 3, 4}

A 2-dimensional array can exist in a block of cells, made up of multiple rows and columns.
Two dimensional array {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}
Notice that vertical elements are separated by a semi-colon (;) as opposed to a comma.

An array constant can contain numbers, text, logical values as well as error values. Any text must be enclosed in double quotation marks (i.e. "some text").
An array constant cannot contain any numbers with commas, dollars, parentheses, percent signs, worksheet functions or any other arrays.
Arrays do not actually need to be stored in cells and can alternatively be stored in memory during a calculation.
When an array is stored in memory during a calculation the formula is executed slightly faster.

One Dimensional arrays

{"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}

This array has commas separating the elements which means that the array is a horizontal array.
You could replace the commas with semi-colon's to define a vertical array, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}

Two Dimensional arrays

{"Sales","Sales","Sales","Profit","Profit","Profit";2000,2001,2002,2000,2001,2002}

Naming an Array Constant.

If you find yourself entering the same list again and again you can save a list as an array constant.
Saving your list as an array constant will allow to quickly enter the list into cells.
Select (Insert > Name > Define) to display the Define Name dialog box.
Enter the name of your array constant and enter the array in the Refers to box.
Notice that the array must have an equal sign before it. Without this equal sign the name will just refer to a text string.

You must remember to enter the curly brackets as well. They will not be entered automatically in this case.

Using the Array Constant

Once the array constant has been defined you can enter it quickly into cells and also use it directly in your formulas.
You can then enter the array constant directly in your cells {=Array_Months}

The array constant can also be used as an argument to a lot of the worksheet functions.
You can obtain individual items from the array constant using the INDEX function.

Important

A Named Array is sometimes referred to as a Named Formula.
Instead of defining another named constant which will define the months as a vertical array, you could use the TRANSPOSE function, e.g. {=TRANSPOSE(Array_Months)}.
If you accidentally enter an array formula by not pressing (Ctrl + Shift + Enter) then the formula will either return an incorrect value or #VALUE! will be returned.