Excel: Differences Between 0 and # Symbols in Number Formats

SUMMARY

The symbols 0 and # represent digit placeholders in the number formats specified in the Number command on the Format menu. When either symbol is specified in a format, Excel rounds extra digits to the right of a decimal point and displays any extra digits to the left of the decimal.

For example, if your format specifies digits to the left of the decimal and your entry contains three digits, Excel displays all three numbers. However, if your format specifies two digits to the right of the decimal and your entry contains three digits to the right of the decimal, Excel rounds the display to two numbers.

The difference between the # and 0 placeholders becomes apparent when you enter a number with fewer digits than are specified in your format. In this case, the 0 forces the display of a digit. The following table illustrates how the # and 0 symbols affect the display of a digit and illustrates how the same symbols affect the display of different entries:

   User-Defined Format    Your Entry    Worksheet Display
   -------------------    ----------    -----------------

   ###.##                 12.345        12.35
                          44412.3       44412.3
   000.00                 12.345        021.35
                          44412.3       44412.30