![]() |
Leading the way in Microsoft Office Development |
| Home | | | Excel | | | Word | | | PowerPoint | | | Consultancy | | | Feedback | | | Contact |
| Microsoft Excel > Formatting Numbers > Custom Number Formats | < Previous | Next > |
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. |
| Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved. | < Previous | Top | Next > |