Chapter 18: Understanding and Using Array Formulas
18
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 a row and type the following (and then press
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 fol-
lowing 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 separate its vertical elements. If you use a non-English-language version of Excel, the
item-separator character may be a semicolon (for horizontal elements) and a backslash (for
vertical elements). If you are not sure, open the example file for this chapter and examine
a two-dimensional array. The item-separator characters are translated automatically to your
language version.
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 for-
mula, and press Ctrl+Shift+Enter:
={1,2,3,4;5,6,7,8;9,10,11,12}Figure 18.3 shows how this array appears when entered into a range (in this case, B3:E5).