In Excel, you aren't limited to using the built-in number formats. You can define your own custom number formats to do things like display values as thousands or millions (like 23K or 95.3M), add leading zeros, display " - " for zero values, or make negative values red. This feature is very well documented in Excel's help system. Just search for "custom number format" and look for "create or delete a custom number format".

To get started, it is often easiest to start from a pre-existing number format. Open the Format Cells dialog box (Ctrl+1 > Number tab) and choose Number, Currency, Accounting, etc. from the Category list and one of the common formats. Then choose Custom from the Category list, and you'll see what the format string looks like for that built-in format. The image below shows the format string for one of the Currency formats (see the Type field, which I've highlighted red).

Custom Number Formats
Fig 1. Format cells dialog box showing the definition of a custom number format.

Number Format Codes

A number format is specified using up to 4 different codes, separated by semicolons, as shown in the image below (from the Excel 2003 help file). For more information on the syntax, see the Help documentation.

Custom Number Formats
Fig 2. Syntax for a number format (from Microsoft Excel 2003 Help documentation "Create or delete a custom number format")

Custom Number Formats

Listed below are some examples of number formats that you may find useful. You can find many other examples by viewing the help document on this feature within Excel.

Format CodeValueDisplayed
As
Description
00050.8051Display values with leading zeros. This does not convert the value to text - it is only a display format.
#,##0.03543.463,543.5Display values using commas to separate thousands, millions, etc. The # sign is used as a placeholder, meaning that if there are no 10's, 100's, or 1000's, don't display them.
0,K
0.0,K
0.0, "Thousand"
2354324K
23.5K
23.5 Thousand
Display values in thousands, using the letter K to indicate thousands. The "K" is just a displayed character - it has no special meaning in the number format string. If you want to display more than one letter, you need to enclose the characters in quotes, like the "Thousand" example.
0,,M
0.0,,M
0.0,, "Million"
2354300024M
23.5M
23.5 Million
Display values in millons, using the letter M to indicate millions.Note that in this case, you need two commas.
[=1]# "person";[>1]# "people"
 1
5
0
1 person
5 people
0
Display a number and text in the same cell using the conditions [=1] and [>1]. The value is stored as a number, so you can still do calculations on the number of people.
0.0 "ft"
0.0 "kg"
# #/## "in"
2.2
4.5
6.25
2.2 ft
4.5 kg
6 1/4 in
Display a number and text in the same cell using the conditions [=1] and [>1]. The value is stored as a number, so you can still do calculations on the number of people.
#.#"°"98.798.7°Display temperature in degrees with the ° symbol.
# ??/125.755 9/12Display a decimal number of feet as feet and inches (rounded to the nearest inch).
;;;"•" @Eggs
Bacon
• Eggs
• Bacon
Create a bulleted list using a special symbol for the bullet. When you enter text, the bullet will be displayed. Numbers and zero values will not be displayed.
# ??/100

?/2
5.2

5.2
5 20/100

10/2
Fractions. Using ??/100 will help line up values in a column (as opposed to just using ?/100). Note that values are automatically rounded.
0 "AD";0 "BC"247

-600
247 AD

600 BC
AD and BC Years. Use negative numbers for BC years and positive numbers for AD years.
d/m/yy
h:mm AM/PM
2/12/11
3:23 PM
Add a Carriage Return within the custom number format (e.g. between d/m/yy and h:mm AM/PM) by pressing Ctrl+j.

References

Cite This Article

To reference this article from your website or blog, please use something similar to the following citation:

- Wittwer, J.W., "Custom Number Formats in Excel" at Vertex42.com

Like This Page?

Become a Fan

       
Master Excel - Spreadsheet Tips Workbook

Create Dashboard Reports in Excel!