Custom number formats - Formatting decimals

Customizing number formats can be very useful in helping you control the appearance of numbers, dates, and text in cells.

This is the second tutorial in our series, which focuses on the presentation of numbers in Excel.

Digit placeholders and decimal points

Number format codes rely on the following symbols:

• Decimal points are represented with dots (.)
• Digits are represented by either zeroes (0), hashes (#) or question marks (?)

Even though any of 0, #, or ? can be used to display digits, there are subtle differences in the way that they deal with significant versus insignificant figures, and the way they deal with decimal-point alignment. We will look at these differences in detail.

Understanding the ‘0’

The most commonly used number format is 0.00 and it is quite likely that you have encountered this number format before. This digit placeholder displays insignificant zeroes. What does this mean? Below, we show the effect of using the number format 0.00.

Leading digits are always displayed

• .68 --> 0.68

Notice the addition of the ‘0’ before the decimal point.

‘Excess’ digits after the decimal are hidden

• 2.169412251 --> 2.17

Notice the truncation of ‘excess’ digits. The number format 0.00 requires exactly 2 digits to be displayed after the decimal point. Also note that, when the truncation occurs, the number is rounded (for display only).

Trailing zeroes are inserted if there are ‘too few’ digits are the decimal

• 301 --> 301.00
• 301.8 --> 301.80

Notice the addition of trailing zeroes to ensure that exactly two digits are displayed after the decimal point.

Usage

Formats such as 0.0 or 0.00 or 0.000 are intuitive to understand and they are commonly used.

Understanding the ‘#’

The symbol # is used to display digits, just like the symbol 0. However, unlike 0, the symbol # does NOT display insignificant figures. Leading digits (i.e. digits before the decimal point) are displayed only if they are non-zero, and trailing zeroes are not automatically inserted. Below, we will show the effect of using the number format #.##.

Leading digits are displayed only if they are non-zero

• Non-zero leading digit: 486.15 --> 486.15
• Zero leading digit: 0.15 -> .15

The first example is what we would ‘normally’ expect, and is identical to what would be achieved with the format 0.00.

However, in the second example, we can see the major difference between the 0.00 format and the #.## format. If we used 0.00, then 0.15 would have been displayed as 0.15 (the leading zero is retained), but when we use #.##, 0.15 is displayed as .15 (the leading zero is hidden).

‘Excess’ digits after the decimal are hidden

• 2.169412251 --> 2.17

Just like 0.00, the format #.## allows the display of up to two digits after the decimal, and no more.

Trailing zeroes are NOT inserted if there are ‘too few’ digits are the decimal

• 301.8 --> 301.8
• 301 --> 301.

The format #.## allows us to have up to two digits to be displayed after the decimal point, but it does not force them to appear. You may contrast this with 0.00 which inserts additional zeroes to fill up space so that 301.2 would be displayed as 301.20. The second example (with the integer) is problematic, since trailing zeroes are not automatically inserted.

Usage and caveats

Generally, formats such as #.# or #.## or #.### should not be used because integers are not displayed properly and numbers in columns do not line up properly, because they may have a different number of digits after the decimal point
However, the # is extremely useful when it is used in combination with other symbols (see later).

Understanding the ‘?’

The symbol ? follows exactly the same rules as #. However, it has a very important additional feature – it ensures that decimal points line up correctly.

In this diagram, we can see that the format ?.?? ensures that up to two digits are displayed after the decimal point and leading zeroes are not displayed (same as using #.##), but it also ensures proper alignment. However, as was the case with #.##, integers are displayed incorrectly see how ‘90’ is shown).

Usage and caveats

Generally,?.? or ?.?? or ?.??? should not be used in that form because integers are not displayed properly.

However, their ability to correctly align numbers is extremely useful when we use ? in combination with 0 and #.

Useful combinations

Formats with 0

Formatting with # and 0

Formatting with #, 0, and ?

Formatting with 0 and ?

COMMENTS