Custom Number Formats

Number Formats

To understand how custom number formats work, you must

• appreciate that a wide variety of formats for numbers, percentages, times, dates, etc. are incorporated into the Format Cells Number dialog box
• understand the codes that are presented when you examine the list that is displayed when you select <Custom>.

To make it easy for you to scan this list, Excel will automatically select the appropriate section of the scrollable list based upon how the cell(s) are already formatted. For example, if a cell, or ranges of cells, is already formatted for numbers, then when you select <Custom>, the dialog box will automatically display the custom number formats. If, instead, the cell, or ranges of cells, is already formatted for percentages, then when you select <Custom>, the dialog box will automatically display the custom percent formats.

Suppose, for example, that you wanted a custom format for the numbers in range B2 through E9 in the gradesheet after we set that range to numbers. The codes shown in the figure would be displayed (note the list is scrollable and more numeric formats are possible). What do the codes mean?

Number codes

Code Symbol	Means What
0	a required digit
#	use this digit if necessary (if the number is that large)
,	insert a comma if necessary
.	insert a decimal point if necessary
_	add an extra space in this position
()	display negative numbers in parentheses
[Red]	display negative numbers in red
;	separates alternate forms of the same format

For example, what does the format #,##0.00_);[Red](#,##0.00) mean?

• since there are two zeroes to the right of the decimal point, all numbers will be displayed with 2 decimal places
• since a comma is present in the format, if the number is greater than 999, then commas will be inserted in the appropriate position(s)
• since an underscore comes after the second decimal place, positive numbers will be displayed with one extra space after the rightmost digit
• the semicolon in this example separates the positive format from the negative format
  • since parentheses and [Red] are present in the format, if a number is negative it will be enclosed in parentheses and displayed in red

Using these codes and those for the other formats (such as time and dates), you can create your own custom format!

Comparing Accounting, Currency, Number Formats

Accounting

• dollar signs, if present, are always left aligned in the cell with the number itself right aligned in the cell
• an extra space always follows the rightmost digit
• negative numbers are enclosed in parentheses
• zero values are always displayed as -, not with a zero

Whenever you have column of dollar values, always follow the “first/last rule” of accounting, that is, only the first and last values in the column have dollar signs, the values in the middle do not. Never use the Currency format.

Currency

• dollar signs, if present, are placed immediately to the left of the leftmost digit
• negative numbers are in red
• zero values are displayed with a zero

Number

• you can add an extra space(s) after the rightmost digit only if you use a custom Number format

Review the custom formats for each of these formatting styles to see the full range of formatting styles.

What About that Extra Space?

By now you should have noted that some numeric formats have an extra space after the rightmost digit. How/why should that be used? You should recall that the rules for adjusting column widths and aligning labels are used to ensure the readability of your spreadsheet. If you use a numeric format with an extra space, you make sure that sufficient white space is available between columns. In addition, by using such a format, you can center align text labels over columns of numeric values. Try this in the gradesheet and see which you like better!

Copyright©2003
last updated August 12, 2003