Understanding the Dimensions of an Array

As stated previously, an array can be either one-dimensional or two-dimensional. A one- dimensional array's orientation can be either vertical or horizontal.

One-Dimensional Horizontal Arrays

The elements in a one-dimensional horizontal array are separated by commas. The following example is a one-dimensional horizontal array constant:

 {1,2,3,4,5} 

To display this array in a range requires five consecutive cells in a single row. To enter this array into a range, select a range of cells that consists of one row and five columns. Then enter ={1,2,3,4,5} and press Ctrl+Shift+Enter.

If you enter this array into a horizontal range that consists of more than five cells, the extra cells will contain #N/A (which denotes unavailable values). If you enter this array into a vertical range of cells, only the first item (1) will appear in each cell.

The following example is another horizontal array; it has seven elements and is made up of text strings:

 {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"} 

To enter this array, select seven cells in one row and then type the following (followed by pressing Ctrl+Shift+Enter):

 ={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"} 

One-Dimensional Vertical Arrays

The elements in a one-dimensional vertical array are separated by semicolons. The following is a six-element vertical array constant:

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

Displaying this array in a range requires six cells in a single column. To enter this array into a range, select a range of cells that consists of six rows and one column. Then enter the following formula, followed by pressing Ctrl+Shift+Enter:

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

The following is another example of a vertical array; this one has four elements:

 {"Widgets";"Sprockets";"Do-Dads";"Thing-A-Majigs"} 

To enter this array into a range, select four cells in a column, enter the following formula and then press Ctrl+Shift+Enter:

 ={"Widgets";"Sprockets";"Do-Dads";"Thing-A-Majigs"} 

Two-Dimensional Arrays

A two-dimensional array uses commas to separate its horizontal elements, and semicolons to separate its vertical elements. The following example shows a 3 x 4 array constant:

 {1,2,3,4;5,6,7,8;9,10,11,12} 

To display this array in a range requires 12 cells. To enter this array into a range, select a range of cells that consists of three rows and four columns. Then type the following formula, followed by pressing Ctrl+Shift+Enter:

 ={1,2,3,4;5,6,7,8;9,10,11,12} 

Figure 14-2 shows how this array appears when entered into a range (in this case, B3:E5).

Figure 14-2: A 3 x 4 array, entered into a range of cells.

If you enter an array into a range that has more cells than array elements, Excel displays #N/A in the extra cells. Figure 14-3 shows a 3 x 4 array entered into a 10 x 5 cell range.

Figure 14-3: A 3 x 4 array, entered into a 10 x 5 cell range.

Each row of a two-dimensional array must contain the same number of items. The array that follows, for example, is not valid because the third row contains only three items:

 {1,2,3,4;5,6,7,8;9,10,11} 

Excel will not allow you to enter a formula that contains an invalid array.