Feb 08, 2016

While Excel has plenty of standard formats for dates and numbers, if these don’t suit what you need, you can make use of custom number formats to display your data exactly as you would like.

While this blog won’t go into every possible custom option, I’m hoping that through a few examples you’ll see the potential of this feature.

Before we start I’ll just remind you that custom formats are still just formats on cells, which means they don’t really change what is in the cell, only how it looks on the screen (and not how it looks in the formula bar or how it is used in formulas.)

Note: if you want Excel to treat what you type in a cell as a piece of text regardless of what Excel might normally convert it to (a date or a number) use a single talking mark (‘) at the start of the cell, or format the cell to Text format. This is good for mobile phone numbers as Excel won’t get rid of the leading 0.

For example: ‘0412345678

You find custom number formats in the Format Cells menu, simply click on the dialog launcher (diagonal arrow) at the bottom right corner of the Number group in the Home tab.

You don’t have to be an expert at knowing custom format codes, just choose a format that is close, click on the Custom option, and then experiment with changing the code while keeping an eye on the sample preview above.

Here are some examples:

1. Country codes in currency

If you want to make it obvious about whether you are using Australian or US dollars, choose Currency and then add AU or US at the front of the custom code.

Note: The S in US could cause some small problems because it has a special meaning in these custom codes, but putting a backslash character () in front of the S.

2. Leading Zeros

If you have a product code that consists of 6 digits. Instead of typing 000021, change the format code so you can type 21 and Excel will add the leading zeros. Use the following number format code: 000000

3. Large Numbers

Use a single comma to add thousand commas and use two commas (,,) to display millions. Use the following number format code: 0.0,, “M” This will give one decimal place shown and add the letter M on the end.

4. Colours and negative numbers

Certain pre-set formats already add colours to negative numbers. The code for this works like this: positive number format; negative number format; zero value format

Note that each piece is separated with a semicolon (;) in your number format code.

Try the following number format code: [Green]$0.00;[Red]-$0.00;[Blue]$0.00

5. Dates and Times

You can also control date and time formats.

Try the following number format code: dddd, dd mmm yyyy

This gives:Thursday, 01 Jan 2021

Hope that helps you with your number formatting.