Custom Number Formats

Details

Category: Formatting

Published on Wednesday, 15 February 2012 16:18

Written by Dave Green

By default excel displays numbers in the 'General' style, which is ok, but more often or not you will probably want to change the format to something such as a financial format (thousand separated by commas and two decimal places for example).
Fortunately Excel had many many formats built in and you can select these by using the buttons on the ribbon bar (Home tab in Excel 2007 and 10) or for more options you can call up the number formatting dialog as shown below,

Or you can use the following keyboard shortcut to bring up the same. Ctrl + 1

As many formats that Excel has built in as standard for finance, dates and scientific notation there are always requirements for more. For example many years ago I worked as a Quantity Surveyor and it was common practice that the bills of quantities noted negative numbers in Red ink in parentheses. It should be noted this was before computers were common place in offices and most taking off was carried out by hand and then typed up by a secretary. However the Red ink in parentheses did serve a purpose as it ensured that any one reading the documents were immediately aware of negative numbers and it was a lot harder to miss the normal subtraction sign (-), even when the document was photo-copied. This is still in use in many places and is good practice as it helps eliminate problems. I have also seen accountants use this notation - probably for the same reasons.

I have many times over the years been requested on how to produce this in a spread sheet, and therefore have the following on hand:

#,##0.00_);[RED](#,##0.00);-_)

All you have to do is copy paste this into the custom number dialog (see screenshot)

Note: this dialog can be called by using the keyboard short cut of Ctrl + 1.

The explanation for the above formatting is quite straight forward. The format is made up of sections which follow the order

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

Each section is optional (i.e. you don’t have to apply a format to each section) but you need to remember that they are applied left to right, therefore if you only specify one format it is applied to all sections, but two formats will apply to the positive and negative sections. However you should note - unless specified the Positive section is also applied to the Zero section. The last section is for text - I have to admit - I have never seen this used and am struggling to think of a real life use for it. Basically what it does is applies the text you specify in double quotation marks, but must either be preceded or followed by the @ symbol, which then specifies whether the Text specified precedes of follows text entered into the cell. My only thought on how this can be used is to put a word such a "TEXT!" in the cell if the user accidentally enters text in a cell that requires a number.

Talking of Text, you can include text in any of the format sections, for example you could use #,##0.00_)" Surplus"; in the Positive section and any value entered in the cell will appear with the word Surplus appended to the number (e.g. 123.00 Surplus) - this may be handy in cells that display the result of a calculation.

As you can see from the example given above we colour negative numbers RED - you can apply a colour (Green, White, Blue, Magenta, Yellow, Cyan or Red) to any of the sections by starting the section with [Colour].

There is a lot more to explain on this subject, and I will cover more under separate articles on Conditional Number Formats (this is not the same as conditional formatting), Date and Time formats and how to display numbers as thousands or millions i.e. £1,000,000.00 as £1M.