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

418 Lookup and Reference
If you want to know how many rows this range uses, you then enter this formula:
=ROWS(product_mix)
As indicated in the previous chapter, you can use the COLUMNS and ROWS functions together to calculate the total number of cells in a particular range. For example, if you want to know the exact number of cells used in the product_mix cell range, you create the following simple multiplication formula by using the COLUMNS and ROWS functions:
=COLUMNS(product_mix)*ROWS(product_mix)
Don’t confuse the COLUMNS (plural) function with the COLUMN (singular) function and the ROWS (plural) function with the ROW (singular) function. The COLUMN function returns the number of the column (as though Excel were using the R1C1 reference system) for the cell reference that you specify as its sole argument. Likewise, the ROW function returns the number of the row for the cell reference that you specify as its argument.
Transposing cell ranges
The TRANSPOSE function enables you to change the orientation of a cell range (or an array — see the section on entering array formulas in Book III, Chapter 1 for details). You can use this function to transpose a vertical cell range where the data runs down the rows of adjacent columns to one where the data runs across the columns of adjacent rows and vice versa. To suc- cessfully use the TRANSPOSE function, not only must you select a range that has an opposite number of columns and rows, but you must also enter it as an array formula.
For example, if you’re using the TRANSPOSE function to transpose a 2 x 5 cell range (that is, a range that takes up two adjacent rows and five adjacent columns), you must select a blank 5 x 2 cell range (that is, a range that takes five adjacent rows and two adjacent columns) in the worksheet before you use the Insert Function button to insert the TRANSPOSE function in the first cell. Then, after selecting the 2 x 5 cell range that contains the data that you want to transpose in the Array text box of the Function Arguments dialog box, you need to press Ctrl+Shift+Enter to close this dialog box and enter the TRANSPOSE function into the entire selected cell range as an array formula (enclosed in curly braces).
Suppose that you want to transpose the data entered into the cell range A10:C11 (a 2 x 3 array) to the blank cell range E10:F12 (a 3 x 2 array) of the worksheet. When you press Ctrl+Shift+Enter to complete the array formula, after selecting the cell range A10:C11 as the array argument, Excel puts the following array formula in every cell of the range:
{=TRANSPOSE(A10:C11)}