Excel: Transposed Formulas from Paste Special Incorrect

- 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

WORKAROUNDS

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.

          a. Select the data you want to transpose.

          b. From the Edit menu, choose Replace.

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

          d. Choose the Replace All button.

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

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