Selecting the Range in Excel: TRANSPOSE
It’s not enough to know the size of the range required by the function you’re entering via an array formula. You also have to understand its purpose.
A good example is the TRANSPOSE function. From time to time it happens that you want to turn an array of values by 90 degrees. See the following figure for an example.
Plenty of more technical situations (for example, various calculations in matrix algebra) require the transposition of an array of values, but the need probably arises more often when you’re dealing with worksheet layout issues. The figure shows some month name abbreviations in A1:F1 and some currency values in A2:F2. If you want to put those values into a pivot table, you probably also want to reorient them as shown in C4:D9.
When using an array formula to copy the figure’s A1:E2 into C5:D9, it might occur to you to make a table of the range C4:D9. Unfortunately, Excel tables cannot include multiple-cell array formulas.
One approach is to select A1:F2, copy it with your method of choice, and select C4. Then choose Paste from the Clipboard group on the Ribbon’s Home tab and click the Transpose icon in the first group of Paste commands.
The result is to switch the row-by-column orientation of A1:F2 into C4:D9. This is often exactly what you want, particularly if your purpose was to accommodate the existing page layout in a report.
But suppose that the information in A1:F2 might change from time to time. As more months go by, results from earlier months might be revised. In that sort of case, you would probably want the data in C4:D9 to be updated along with the data in A1:F2. That’s one useful aspect of Excel’s TRANSPOSE function.
Don’t bother to copy A1:F2. Instead, begin by selecting C4:D9. Then array-enter this formula:
The result looks just like what you see in the figure, but instead of values in C4:D9, that range contains an array formula. Therefore, if the data in A1:F2 changes, the changes are reflected in C4:D9.
The more general point to take from this section is that you need to know what TRANSPOSE does in order to select the range that will contain it, prior to array-entering the function. With a function such as LINEST, you need to know to select a range five rows high, with a number of columns that depends on the number of variables you have to analyze. With a function such as TRANSPOSE, you need to derive the rows and columns and their orientation from the rows and columns of the original array.