Custom Formats in Excel

aat comment

There are two different sets of number formats in Microsoft Excel.

There is a list of built-in number formats that includes formats for financial values, dates, fractions and scientific notation for example, and there is a Custom Formats option that allows you to specify the format you want to use in exact detail. The former are often sufficient, but sometimes you really need the latter, which I’ll show you today.

Method

From the Format Cells dialog box, Number tab, clicking on Custom will display a list of custom format codes that you can use as the basis for creating your own code:

Financial Custom Format

To explain the custom format codes, I’ll look at the codes for a typical format for showing financial figures: round pounds with thousands separators, negatives in red and with brackets, and zeros as dashes. Please note, using dashes rather than zeros can make a big difference when working with large blocks of numbers with many zero values.

This is the format I’ll use:

#,##0_);[Red](#,##0);-?

The format is in three sections, separated by semi-colons. The sections control positive numbers, negative numbers and zeros respectively. There is a fourth section for text, but this is not generally required unless you need to include specific text for all cells with this format.

Section 1 – Positive numbers

#,##0 – the hashes (#) will only display ‘significant’ digits, this means leading zeros will be suppressed. The 0 means display a zero in this position if the number is less than 1.00. An example should make this clearer:

 
Finally the comma makes Excel use the comma as the thousands separator.

Note also the use of the underscore. This causes Excel to leave a space equal to the width of the character that follows the underscore. So in this example we have used _) to include an amount of space at the end of a positive number equal to the width of a closing bracket. This makes sure that negative and positive figures in the same column line up properly.

Section 2 – Negative numbers

[Red](#,##0) – In the second section there are a couple of additional elements:

The name of one of the eight main colours in square brackets causes the numbers to be displayed (and if a suitable printer is available, printed) in that colour. In our case negative numbers will be red. It is perfectly possible to apply colours to positive numbers as well, just precede the first section with the colour in square brackets, e.g. [blue].

Brackets are used to ensure that negative figures are shown bracketed.

Section 3 – Zero values

-? – We have entered a dash followed by a question mark. The question mark inserts a space to move the dash in one character from the right hand edge of the cell.

Section 4 – Text

Not used in this example – If you do not use this section, text will be displayed as it is entered. If the fourth section is blank, any text entered will not be displayed. The use of the ‘at’ character (@) will display any text as it is entered.

You can also combine the text entered with some fixed text:

#,##0_);(#,##0);;”Sales of “@

This will display ‘Sales of ‘ followed by the text entered into the cell, or if a number is entered, the number formatted as shown by the positive and negative sections.

You can include text as part of a number format. If you want to label positives as Assets and negatives as Liabilities for example, you could use the following custom format:

“Assets ” #,##0_);”Liabilities ” (#,##0)

Which would give the results:


Asterisk

An asterisk followed by a character will fill the available space with repetitions of that character. In some of the built in number formats this is used to position the currency symbol at the left of the cell:

_(£* #,##0_);_(£* (#,##0);_(£* “-“_);_(@_)

Because the asterisk characters are followed by spaces, space characters will be repeated between the currency symbol and the first number or bracket, to ‘push’ the currency symbol to the left edge of the cell:

AAT students and professional members can access a wide range of Excel resources and training here.

For more information about studying with AAT, click here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.

Related articles