Custom number formats

custom number formats

This lesson's saved the Custom number format for last because it's the most complex and powerful. With it, you can create your own number formatting rules.

First, the basic procedure:

1. Select the cell(s), choose Format > Cells, and click the Number tab.
2. Click Custom as the category, and then edit one of the existing custom formats or create your own.
3. Click OK.

The key to custom number formats is not the simple process just described, of course. The important part is to know how to construct the coding for various custom formats. There are different symbols for numbers, colors, currency symbols, dates, and other uses.

TIP
For a complete education on custom number formats, look up Number Format Codes in Excel's Help system. We present an abbreviated lesson on them here.

The following tables show the characters you need for custom number formats. You may want to print these for later reference.

Table 4-2: Numeric symbols in custom number formats.

Table 4-3: Colors and spacing in custom number formats.

Table 4-4: Symbols in custom number formats.

 Table 4-5: Dates and times in custom number formats.

You can also use math comparison operators in the custom number formats, to set up an 'if-then' condition. For example, you could use this to format a number in red if it is less than or equal to 100, or in blue if it is greater than 100:

[Red][<=100];[Blue][>100]

custom number formatting examples

Suppose you want to show numbers with a comma separator and exactly three decimal places, and you want negative numbers to appear in green and to have parentheses around them. You would write a custom number format with this syntax:

Positive numbers;negative numbers

For the positive portion, we'd enter this:

#0.000

This tells Excel that we want at least one digit to the left of the decimal point and at least three to the right of it. The 0s stand for those required digits. The # signs stand for digits that will appear only if they're significant. The # signs would not be necessary except that we need them as placeholders to show where the comma goes.

For the negative portion, the same thing but with a color code, from Table 4-3, and the parentheses:

[Green](#,##0.000)

If we want the positive and negative numbers to align neatly in the column, we should add a spacer to the positive numbers of the same width as the parenthesis (again see Table 4.3), so the positive number would change to this:

#0.000_)

The complete number format would look like this:

#0.000_);[Green](#,##0.000)

Now let's try one with a date. Suppose you want the date to appear like this: 08/05/1964, and you want the time to appear as 4:07 AM.

The date code would be:

mm/dd/yyyy

The time code would be:

H:mm AM/PM

Putting the two together, we get:

Mm/dd/yyyy h:mm AM/PM

This shows the time even if you do not input a time. If there is no time input, it shows 12:00 AM.