Microsoft KB Archive/104909

= Microsoft Knowledge Base =

Excel: Crosstab Returns Scientific Notation
Last reviewed: September 12, 1996

Article ID: Q104909

The information in this article applies to:


 * Microsoft Excel for Windows, version 4.0
 * Microsoft Excel for the Macintosh, version 4.0

SYMPTOMS
In certain situations, the Crosstab ReportWizard may produce output with numbers displayed in scientific notation.

CAUSE
This problem occurs because the CROSSTAB function automatically applies the number format to all numbers.

STATUS
Microsoft has confirmed this to be a problem in Microsoft Excel version 4.0 for Windows and Microsoft Excel version 4.0 for the Macintosh.

This problem was corrected in Microsoft Excel for Windows, version 4.0a.

For Microsoft Excel for Windows and for the Macintosh
Format cell A2 on the sheet that contains your database to '1107514000 (note the leading appostrophe). This forces the entire field to be treated as text.

For Microsoft Excel for Windows
Update your version of Microsoft Excel to version 4.0a.

Steps to Reproduce Problem
The following example illustrates this behavior:

  Enter the following data in a worksheet in cells A1:C7: A1: Part Number   B1: Actual    C1: Current A2: 1107514000    B2: 100       C2: 150 A3: 1107514000    B3: 100       C3: 150 A4: 2162016000    B4: 300       C4: 400 A5: 2162016000    B5: 300       C5: 400 A6: 3163456000    B6: 200       C6: 500 A7: 3163456000    B7: 200       C7: 500  Select cells A1:C7, and choose Set Database from the Data menu. From the Data Menu, choose Crosstab. Choose the Create A New Crosstab button. For Row Categories, select Part Number and choose the Add button, and then choose the Next button. For Column Categories, choose the Next button. For Values Fields, select Actual and Current and choose the Add button, and then choose the Next button. Choose the Create Crosstab button.</li></ol>

When the Crosstab is created, notice that the first part number is correctly displayed, however the part numbers that follow are displayed in scientific notation.