# XL: Formula That Transposes Linked Data

Last reviewed: September 3, 1998
Article ID: Q118484

###

The information in this article applies to:

- 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.

## MORE INFORMATION

For example, to link and transpose the following sample data

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

follow these steps:

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 four rows by two columns in size; therefore, the range you select should be two rows by four columns.

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

             =TRANSPOSE(A1:B4)

   NOTE: You must enter this formula as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

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)

creates a transposed link to cells A1:B4 on Sheet1.

> Additional query words: 5.00 5.00a 5.00c 7.00 7.00a 97 XL97 XL7 XL5
> Keywords : xlformula kbdta
> Version : WINDOWS:4.0,4.0a,5.0,5.0c,7.0,7.0a,97; MACINTOSH:4.0,5.0,5.0a
> Platform : MACINTOSH WINDOWS
> Issue type : kbhowto
> THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 3, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.