XL: Default Category Number Formats Different than Previous

ID: Q131394

The information in this article applies to:

SYMPTOMS

In Microsoft Excel versions 7.0 and 97, when you apply a number format to a value in a cell, the appearance of the value may be different from that in earlier versions of Microsoft Excel. For example, when you apply the Currency format to the value 2.34 (without selecting a specific format code) the value appears as $2.34 in Microsoft Excel versions 7.0 and 97, where the value appears as $2 in earlier versions of Microsoft Excel.

CAUSE

This behavior occurs when you apply a number format by selecting a category, but you do not select a specific format code. Some of these "default" number formats in Microsoft Excel versions 7.0 and 97 are custom formats, and are different from the defaults applied by default in earlier versions of Microsoft Excel.

Note that Microsoft Excel versions 7.0 and 97 contain all of the formatting codes that are available in Microsoft Excel version 5.0, with the addition of several special formatting codes.

The following table contains the number format category, the category default format codes applied in Microsoft Excel version 5.0, and the default category format codes applied in versions 7.0 and 97. 

 Category      Version 5.0 Default          Versions 7.0/97 Default
              Category Format Codes          Category Format Codes
 ------------------------------------------------------------------

 Custom             General                        General

 Number                0                            0.00

 Accounting   _($* #,##0_);_($* (#,##0);   _($* #,##0.00_);_($*
(#,##0.00); 
                  _($* "-"_);_(@_)           _($* "-"??_);_(@_)

 Date               m/d/yy                           m/d

 Time             h:mm AM/PM                         h:mm

 Percentage           0%                             0.00%

 Fraction            # ?/?                           # ?/?

 Scientific         0.00E+01                        0.00E+01

 Text                  @                              @

 Currency         $#,##0_);($#,##0)               $#,##0.00
The following table contains the number format category, and the difference in appearance between the default formatting in Microsoft Excel version 5.0 and versions 7.0 and 97. 

 Category          Appearance In        Appearance In
                    version 5.0        versions 7.0/97
 -----------------------------------------------------

 Custom                 34                  34

 Number                  5                5.00

 Accounting         $4,500           $4,500.00

 Date               3/4/95                 3/4

 Time             12:00 AM                12:00

 Percentage            34%              34.00%

 Fraction              4.5                 4.5

 Scientific       8.59E+12            8.59E+12

 Text             8                   8

 Currency               $2               $2.34

WORKAROUND

To work around this behavior in Microsoft Excel versions 7.0 and 97, select the formatting options in the Format Cells dialog box until the sample appears the way you want.

REFERENCES

For more information about how to create custom number formats in Microsoft Excel, click the Index tab in Microsoft Excel Help, type the following text 

   formatting cells, n
double-click the selected text, then double-click Create Custom Number Formats to go to the "Create Custom Number Formats" topic.

Additional query words: 7.00 8.00 97 

Keywords          : kbualink97 xlformat xlwin 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS

Last Reviewed: January 8, 1999