Chapter 16: Introducing Array Formulas
361
To enter this array, select seven cells in a row and type the following (followed by
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, and the array can be
displayed in a column of cells. 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 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 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”;”Doodads”;”Thingamajigs”}Two-dimensional arrays ..........................................................................................
A two-dimensional array uses commas to separate its horizontal elements and semicolons to sepa-
rate its vertical elements. The following example shows a 3 × 4 array constant:{1,2,3,4;5,6,7,8;9,10,11,12}Displaying 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
Ctrl+Shift+Enter:={1,2,3,4;5,6,7,8;9,10,11,12}Figure 16.3 shows how this array appears when entered into a range (in this case, B3:E5).If you enter an array into a range that has more cells than array elements, Excel displays #N/A in
the extra cells. Figure 16.4 shows a 3 × 4 array entered into a 10 × 5 cell range.
Each row of a two-dimensional array must contain the same number of items. The array that fol-
lows, for example, isn’t valid, because the third row contains only three items:
{1,2,3,4;5,6,7,8;9,10,11}Excel doesn’t allow you to enter a formula that contains an invalid array.