XL: Formula That Transposes Linked Data

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SUMMARY

In Microsoft Excel, the Paste Special dialog box does not offer an option to simultaneously link and transpose a copied range. To do this, use the TRANSPOSE() function. When you enter the TRANSPOSE() function as an array, you can create a transposed link to an area that contains data.

For example, this article explains how to link and transpose the following sample data:

   A1: 1   B1: 5
   A2: 2   B2: 6
   A3: 3   B3: 7
   A4: 4   B4: 8

1. Select a range equivalent in size to the range containing the data, but with rows and columns reversed. In this case, the original data range is 4 rows by 2 columns in size; therefore, the range you select should be 2 rows by 4 columns.

2. In the formula box, type the following formula:

         =TRANSPOSE(A1:B4)
   

The transposed data appears in the selected cells.

You can create a transposed link between different worksheets by including the worksheet name in the TRANSPOSE() formula. For example, the following formula

   =TRANSPOSE(Sheet1!A1:B4)

MORE INFORMATION

In Microsoft Excel versions 5.0 and later, the Paste Special dialog box allows you to link data. This creates a link between the copied data range and the new range in which you are creating the link. Data entered into one cell automatically appears in the other.

The Paste Special dialog box provides an option to transpose data when you paste it. Transposed data appears "rotated," so that columns become rows and vice versa. For example, if you copy this range

   A   E
   B   F
   C   G
   D   H

   A   B   C   D
   E   F   G   H