Microsoft Office Tutorials and References

In Depth Information

**Using the TRANSPOSE function**

If the original range contains formulas, the formulas will be adjusted so they continue to refer to the

correct cells.

If the original range is in a table (created with Insert
Tables
Table), this technique has
➜

a few caveats. The original selection cannot include the Total Row or columns that

contain a formula. You can still paste the transposed data, but you must choose the Values

option in the Paste Special dialog box. The transposed range will include the values (but

not the formulas).

Note

Using the TRANSPOSE function

In some cases, you may want the transposed range to be linked to the original range. In such a

situation, changes made to the original range also appear in the transposed range. Here’s how to set up a

transposed range that’s linked to the original source. Refer to Figure 63-2.

Figure 63-2:
A13:J17 contains a multicell array formula, linked to the source range.

1.
Make a note of the number of rows and columns in the source range.

In this example, the source range (A1:E10) has 10 rows and 5 columns.

2.
Select a range of blank cells that has the same number of rows as source range columns, and

the same number of column as source range rows.

In this example, the selection should be 5 rows and 10 columns. For example, you can put

the transposed range in A13:J17.