Leading the way in Microsoft Office Development

Step 1 - What is a Custom Number Format ?

A custom number format allows you to change and control how your numbers, dates and text are displayed.

When you select "Custom" from the category list all the built-in and custom number formats are displayed on the right hand side.

Custom number or date formats are only available in that particular workbook. You must explicitly add them to any other workbooks.

This list offers a variety of number, date and time formats

Step 2 - Four Defined Categories

Custom number formats can contain up to four sections and you can specify format codes to describe how you want to display your numbers, dates, times and text.

Positive numbers, Negative numbers, Zero, Text

The four sections are positive values, negative values, zero values and text. The sections are separated by semicolons

For each section of the number format enter the symbols to represent how you want the value to look.

The following is an example of a custom number format.

Step 3 - Important Rules

If you have not defined a format for negative values, then the positive format is used.

If you have not defined a format for zero values, then the positive format is used.

The text is only affected if you have defined all four sections.

Step 4 - Formatting Codes

Number Codes

Text Codes

Step 5 - Adding Colours

It is possible to automatically change the colour of the text (or value) depending on the characters (or values) that are in the cells.

You can display different values in different colours. You could have all the values greater or less than a specified value appear in a different colour.

This allows you to emphasis particular values in the worksheet.

Note that any colours that you specify with a number format will take precedence over any manual formatting applied.

Each of the four categories (positive, negative, zero, text) can have its own colour associated with it.

To define a colour place it in square brackets at the start of the section.

You can use colour to help validate whether the data entered is correct.

It is not possible to display different parts of the text in different colours using this method.

Step 6 - Using a Condition

It is possible for a number format to also contain a condition.

These conditions can contain one of the comparison operators ( < > = <= >= <> ).

Step 7 - Things to Remember

Using a lot of custom number formats in a single workbook will use up a lot of memory.

Every time you edit a custom number format it will be added to the list. You should delete any custom number formats that you are not using.

It is not possible to display different parts of the text in different colours using this method.