Page 437 - Excel 2013 All-in-One For Dummies

Figure 6-4 illustrates the use of the TRANSPOSE function. The cell range B2:C4 contains the original 3 x 2 array that I showed earlier in Figure 1-9 in Book III, Chapter 1 when discussing how you add array formulas to your worksheet. To convert this 3 x 2 array in the cell range B2:C4 to a 2 x 3 array in the range B6:D7, I followed these steps:
1. Select the blank cell range B6:D7 in the worksheet.
2. Click the Lookup & Reference command button on the Ribbon’s Formulas tab and then choose the TRANSPOSE option from the button’s drop-down menu.
Excel inserts =TRANSPOSE() on the Formula bar and opens the Function Arguments dialog box where the Array argument text box is selected.
3. Drag through the cell range B2:C4 in the worksheet so that the Array argument text box contains B2:C4 and the formula on the Formula bar now reads =TRANSPOSE(B2:C4).
4. Press Ctrl+Shift+Enter to close the Insert Arguments dialog box (don’t click OK) and to insert the TRANSPOSE array formula into the cell range B6:D7 as shown in Figure 6-4.
Clicking the OK button in the Function Arguments dialog box inserts the TRANSPOSE function into the active cell of the current cell selection. Doing this returns the #VALUE! error value to the cell. You must remember to press Ctrl+Shift+Enter to both close the dialog box and put the formula into the entire cell range.
If all you want to do is transpose row and column headings or a simple table of data, you don’t have to go through the rigmarole of creating an array for- mula using the TRANSPOSE function. Simply copy the range of cells to be transposed with the Copy command button on the Home tab of the Ribbon. Position the cell cursor in the first empty cell where the transposed range is to be pasted before you click the Transpose option on the Paste command button’s drop-down menu.
Figure 6-4:
Using the TRANSPOSE function to change the orientation of a simple array.
Lookup and Reference 419
Book III Chapter 6
Lookup, Information, and Text Formulas