Part II: Working with Formulas and Functions
Transposing an array
When you transpose an array, you essentially convert rows to columns and columns to
rows. In other words, you can convert a horizontal array to a vertical array (and vice versa).
Use the TRANSPOSE function to transpose an array.
Consider the following one-dimensional horizontal array constant:
{1,2,3,4,5}
You can enter this array into a vertical range of cells by using the TRANSPOSE function.
To do so, select a range of five cells that occupy five rows and one column. Then enter the
following formula and press Ctrl+Shift+Enter:
=TRANSPOSE({1,2,3,4,5})
The horizontal array is transposed, and the array elements appear in the vertical range.
Transposing a two-dimensional array works in a similar manner. Figure 18.12 shows a two-
dimensional array entered into a range normally and entered into a range by using the
TRANSPOSE function. The formula in A1:D3 is as follows:
{={1,2,3,4;5,6,7,8;9,10,11,12}}
FIGURE 18.12
Using the TRANSPOSE function to transpose a rectangular array
The formula in A6:C9 is as follows:
{=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})}
You can, of course, use the TRANSPOSE function to transpose an array stored in a range.
The following formula, for example, uses an array stored in A1:D3 (three rows, four
