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

I've listed some examples of number formats that you may find useful. You can find many other examples by viewing the help document for this feature within Excel.

IMPORTANT: Using a custom number format affects the displayed value. A formula that references the cell will use the stored value however it is displayed. That is why some of these examples are so cool - you can still use a formula to refer to the value even though the number might be displayed as "12 ft."

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.
;;;anythingDisplay nothing, regardless of the value.
# ??/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.
[Color10]▲0;[Red]▼-05

-5
▲5

▼-5
Display special symbols for positive and negative, combined with colors.
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.
@*-✁------------Trailing characters
*.@pg#............pg#Leading characters

Custom Number Format Color Codes

By using color format codes such as [Red] or [Blue] or [Color10], you have a limited ability to alter the color of the font via custom number formats. The most common use I've seen is to color negative values red. However, one of the examples above also shows how you might want to use a green arrow.

Even though a new color palette was introduced in Excel 2007, the color codes for custom number formats are still based on the old color palette for the Excel 97-2003 format. I created the graphic below to provide a quick reference.

Custom Number Format Color Codes
Color codes for custom number formats (based on the default color palette)

Define Your Own Color: You can modify the color palette in newer versions of Excel by going to File > Options > Save > Colors. This allows you to change the color associated with the Color1 through Color56 codes. This means that Color10 might not always be a dark green. If you purposely change the color palette (or somebody else does), Color10 might be some other color.

I made the above color code reference match the layout of the old color palette because there is not a consistent pattern to the numbering.

Custom Number Format Color Codes
Excel 2016: File > Options > Save > Colors

Note: The Color1-56 codes in Google Sheets are fixed colors and aren't changed when you upload an Excel file with a customized color palette.

References

Cite This Article

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

"Custom Number Formats in Excel" by Jon Wittwer, at Vertex42.com