Excel: Transposed Formulas from Paste Special Incorrect

ID: Q121643

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel version 5.0, when you select the Transpose check box in the Paste Special dialog box, if the range of cells that you transpose contains formulas, the formulas that are returned to the transposed range may not be correct.

CAUSE

This behavior occurs when the formulas that you transpose were created by using the Fill command, or by dragging the fill handle across cells. Additionally, this behavior only occurs when the transposed range contains more that two filled formulas.

For example, if you have the following data

A1: 1 B1: =A1*2
A2: 2 B2: =A2*2
A3: 3 B3: =A3*2
A4: 4 B4: =A4*2
A5: 5 B5: =A5*2

where the data in cells B2:B5 is entered by dragging the fill handle in cell B1 to cell B5, the formulas that are returned when you transpose the range are incorrect.

WORKAROUND

To avoid this behavior, use either of the following methods:

Method 1: Instead of using the Paste Special method to transpose the range, select the destination range (for example, you might select the range D1:H2), and enter the following formula as an array

=transpose(A1:B5)

where A1:B5 is the range that you want to transpose.

NOTE: To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER.

Method 2: Use the following procedure before you transpose the data:

Select the data you want to transpose.

From the Edit menu, choose Replace.

In the Find What box, type an equal sign (=). In the Replace With box, type an equal sign (=).

Choose the Replace All button.

With the data highlighted, choose Copy from the Edit menu.

Select the destination cell, and choose Paste Special from the Edit menu. Select the Transpose check box and choose OK.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

REFERENCES

For more information about Filling A Range Of Adjacent Cells, choose the Search button in Help and type:

filling

Additional query words: 5.0 wrong flip

Keywords : xlwin
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type :