| On the Web and on Usenet, there are various forums and newsgroups devoted to the asking and answering of questions about the use of Excel. And there are plenty of FAQ lists that summarize the answers to the most popular of those questions. One common question goes something like this: "I have a range of data that I imported from a database into Excel. I want to print it, but even in Landscape mode the range is too wide for the paper. I'd be okay if I could print the rows as columns and the columns as rows--that is, if I could reverse the orientation. Is there a way to do that?" That question has lots of variations. Sometimes it's convenient to key in the data with one orientation, but it turns out that it's the wrong setup for charting the data, or analyzing the data with a pivot table, or some other activity that won't cooperate with the data's layout. There are a couple of good ways of dealing with this situation, both involving the term transpose (see Figure 2.13). Figure 2.13. Transposing values has various uses in numeric analysis, but it's also helpful for managing data. 
In Figure 2.13, the values in cells B2:D2 have been transposed into the range F2:F4. But transposing data isn't limited to ranges that have only one row, or only one column: The range B7:E9 has been transposed into G7:I10. In both cases, the rows of the original range become the columns of the transposed range. Transposing with Paste Special Here's one way to transpose a range: - Select the source range. In Figure 2.13, that might be B7:E9.
- Choose Edit, Copy.
- Select the cell that will be the upper-left cell of the transposed range. In Figure 2.13, that might be G7.
- Choose Edit, Paste Special (see Figure 2.14). Fill the Transpose check box, and click OK.
Figure 2.14. The Paste Special window has several special uses; for example, convert text values to numeric by adding zeros to cells. 
If your source range (B7:E9 in Figure 2.13) contains formulas that depend on the contents of other cells, you should also make sure that the Values option button is filled in the Paste Special dialog box. This option converts formulas to values in the range that you're pasting into. If you don't select the Values option, the result depends on where the source range formulas point, and is generally unpredictable, but it probably isn't what you want. One predictable result is shown in Figure 2.15. Cells B1:D4 are the original range. In cells B7:D10 are formulas that point at B1:D4, as shown in cells F7:H10. Cells A12:D14 show the result of using copying B7:D10, selecting A12, and choosing Edit, Paste Special, Transpose. The effect is to force the formulas to point at columns that are to the left of Column A. Those columns are undefined, and Excel returns the #REF! error value. In contrast, cells A16:D18 show the result of choosing the Value option: Edit, Paste Special, Transpose and Values. Figure 2.15. This is similar to copying the formula =A1 from B2 and pasting it into A2. 
To replicate the problem (and the solution) shown in Figure 2.15, take these steps: - Enter a range of values in B1:D4.
- In B7, enter this formula: =B1.
- Copy cell B7 and paste it into C7:D7.
- Now select B7:D7, copy it, and paste into cells B8:D10. You now have a replica of B1:D4, but made entirely of formulas.
- Select B7:D10, and copy it.
- Select cell A12, choose Edit, Paste Special, fill the Transpose checkbox, and click OK.
Your transposed range consists of nothing but #REF! errors. You can avoid this effect by filling the Values option button as well as the Transpose check box to convert the formulas to values before transposing the range. The result is shown in A16:D18 of Figure 2.15. Transposing with the Worksheet Function Another way to avoid the problem of pivoting off the worksheet is to use the TRANSPOSE worksheet function, which you can use whether your source range consists of formulas or values. Here's the sequence, based on Figure 2.13: - Select the range G7:I10. This is the range that you want to contain the transposed cells.
- Type this formula: =TRANSPOSE(B7:E9).
- Then instead of simply entering the formula, array-enter it. (Array-entry was described earlier in the section titled "Array-Entering OFFSET to Return Several Cells"; use Ctrl+Shift+Enter instead of just Enter.)
You wind up with a transposed range that looks as though you had used Edit, Copy, and Edit, Paste Special, but there are two important differences: You get the transposition of the source range, no matter whether it contains values or formulas that point to other cells. You have a volatile formula, not static values. So, if a value in the source range changes, it will also change in the transposed range. If you have tried out the preceding three-step example, try changing any cell in the source range; you'll see that the change is reflected in the transposed range.
TIP If you use the TRANSPOSE worksheet function as described to change the orientation of a source range, remember that you need to start by selecting a range of cells. The range should have as many rows as the source range has columns, and as many columns as the source range has rows. (This will sound all too familiar to you if you ever took matrix algebra.)
|
