XL5: Formulas Incorrect When Transposing Excel 4.0 Data

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel version 5.0, if you copy and transpose data on a Microsoft Excel version 4.0 worksheet using the Transpose option in the Paste Special dialog box (from the Edit menu choose Paste Special), groups of similar formulas in the transposed data may contain incorrect references or #REF! error values.

The severity of this problem depends on the number of shared formulas contained in the range being transposed. Shared formulas are columns or rows of cells that contain the same basic formula, the only difference being the cells referenced in those formulas. This problem does not result in the loss of data (it only affects cells that contain formulas).

Note that this behavior occurs even if you created the version 4.0 worksheet in Microsoft Excel version 5.0 by saving the file as a Microsoft Excel version 4.0 worksheet.

WORKAROUND

To work around this problem, do the following before you transpose the data:

   1. Select the data you want to transpose.

   2. From the Edit menu, choose Replace.

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

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

   5. Choose the Replace All button.

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

   7. Select the destination cell, and choose Paste Special from the
      Edit menu.

   8. 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 version 5.0c.