Leading the way in Microsoft Office Development

Step 1 - What is an array constant ?

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.

Step 2 - 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"}

Step 3 - Two Dimensional arrays

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

Step 4 - 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.

Step 5 - 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.

Step 6 - Things to Remember

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.