Excel Custom Number Formats

Over the coming weeks we are going to show you how to create custom number formats in Excel. In this first blog we will explain the structure of the Excel number formats.

This is important as you can have up to four arguments in an Excel number format and depending on how many of these you use, together with the characters used will determine the visual presentation of your data.

The standard formatting used in Excel is 'General' and this will display numbers as they are entered, with the following rules:

The number of decimal entered will be shown, whether it is 2 or 10 (excluding any zeros at the end).

Negatives will be preceded by a minus sign "-"

Zero (0.00) will be displayed as 0

The results of General formatted numbers is shown below:

The structure of the Excel Number format consists of four sections, separated by semicolons as follows:

POSITIVE; NEGATIVE; ZERO; TEXT

An example format could be:

#,##0; [Red](#,##0); - ; "Not a Number"

This number format would display numbers with:

No decimal places and include the comma as a thousands separator

Displays negative in red and in brackets

Formats 0 (Zero) as a dash (but it has to be exactly zero!)

Display "Not a Number" if text is entered in a cell

A comparison of the"General" and new formatting is shown below and the revised formatting is certainly easier to view..

However it is strange that 0.0101 is displayed as 0 and -0.0202 as (0), this is because Excel still treats 0.0101 as a positive and applies the positive formatting and -0.0202 as negative and applies the negative formatting (We will address these formatting issues in more detail over the coming weeks)

If a custom format does not include all four sections listed, the following rules are applied:

If only one section is listed, then that format applies to all number types (positive, negative and zeros)

If two sections are listed, then the first section applies to positive numbers and zeros, whilst the second section to negative numbers

If three sections are listed, the first section applies to positive numbers, the second section to negatives and the third to zeros

Finally all 4 sections must be specified for a text format, with the fourth section relating to text formatting.

This is an overview of the structure of Excel Number formatting. In the next blog we will look at some additional number formatting and alignments, specifically looking at the difference between 0, # and ? when formatting numbers.