Mouse Training London Ltd: Microsoft Excel - Creating a Custom Number Format

Wednesday 31 October 2018

Microsoft Excel - Creating a Custom Number Format

Creating a Custom Number Format

Excel has several built-in number formats and you can create your own specialised formats using format codes.

Custom number formats are stored at the workbook level and you will have to incorporate them into a template or style to use them in another workbook.

The cell number format never affects the cell's underlying true value.

Number format codes have up to four sections, each separated by a semicolon:

Positive Numbers; Negative Numbers; Zero Values; Text Values

You do not have to include all four sections, should you use only one section then all number types will use that format. Text is only affected by a number format when the last section is used.

Use the semicolon as a positional marker if you wish to leave out particular sections, for example:

Positive Numbers;;; Text Values

Here is an example of a number format code that uses all four sections: #,##0.00_); (#,##0.00); "-"; "Enter a number"

The code shows positive numbers with a comma separator for the thousands, two decimal places and aligns the number in the cell to allow for the width of a closing parenthesis so that positive and negative numbers in a column will line up precisely at the decimal point.

Negative values have a comma separator, two decimal places and are enclosed in parentheses. Zero values are shown as a dash. Any text entered into the cell is displayed as "Enter a number", regardless of the text entered.

In the illustration, the code being created rounds the display of all numbers to round millions to one decimal place and places the text "mil" at the end of the number.

The precision of the number in the cell is not changed in any way. The value in the cell is five hundred million but it appears as "500.0 mil".

Excel number formats are very flexible and it is usually possible to display a value in a cell in any way that is required by using the format codes in various combinations.

However, numeric values that are contained in formulas that return a text value to the cell are not affected by the number format of the cell and in these cases the formatting code has to be applied in the formula itself.