Excel allows you to format numbers in several different ways. Some of the most common are:
- Currency format: Insert a dollar figure (or any other currency symbol) before a number
- Fraction: expresses a decimal as a fraction
- Percentage: expresses a fraction as a percentage
- Number: Displays the raw number (with or without a comma splitting thousands)
But what if you want to a different format, not provided by these standard options? Designing a custom format is the answer.
For example finance people often put brackets around negative numbers, rather than the more traditional minus sign.
To design such a format we’d right click a number to be formatted, click on ‘Custom’ and then enter ‘#,##0;(#,##0)’ under ‘Type’:
This would format the number as required, and be available in the list of custom formats for any other number.
The key, of course, is to be able to turn whatever format you require into this code of #’s, semi-colons etc. That’s quite a complex subject – but there’s an article with the info you need below, but in summary the code is of the form:
[Positive Format];[Negative Format];[Zero Format (optional)] where:
[Positive Format]: the format for positive numbers and is expressed in #’s for numbers and any other symbol (eg currency, brackets, commas for thousands etc)
[Negative Format]: the format for negative numbers and is expressed in the same way as positive formats. It’s where we put our brackets in the above example.
[Zero Format]: The format for zero. Usually ‘0’ or ‘-‘.
Anyway, here is a more detailed review of writing cutom formats:
CUSTOM FORMAT CODES
When you create custom number formats, you can specify up to four sections of format code. These sections of code define the formats for positive numbers, negative numbers, zero values, and text, in that order.