Microsoft Knowledge Base

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

WORKAROUND

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.

MORE INFORMATION

Steps to Reproduce Problem

The following example illustrates this behavior:

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

2. Select cells A1:C7, and choose Set Database from the Data menu.

3. From the Data Menu, choose Crosstab.

4. Choose the Create A New Crosstab button.

5. For Row Categories, select Part Number and choose the Add button, and then choose the Next button.

6. For Column Categories, choose the Next button.

7. For Values Fields, select Actual and Current and choose the Add button, and then choose the Next button.

8. Choose the Create Crosstab button.

REFERENCES

"User's Guide 1," version 4.0, pages 346-356