XL98: Formula That Transposes Linked Data (192282)

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:

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

REFERENCES

For more information about the TRANSPOSE function, click the Office Assistant, type "transpose," click Search, and then click to view "TRANSPOSE."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

179216 OFF98: How to Use the Microsoft Office Installer Program