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.

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.

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.

REFERENCES

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

KBCategory: kbprb KBSubcategory: Additional reference words: 4.00 crosstabs THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: September 12, 1996 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.