Number format codes are the string of symbols that define how Excel displays the data you

store in cells. We will

get into the ways to describe the formats in a minute, but first we need to go over how Excel interprets those
symbols. Each number format code is made up of as many as 4 sections separated by a semi-colon (;).
Excel Tactics These sections control formatting for one or more parts of the number line, including positive numbers, negative
numbers, and zeros. They can also control formatting for sub-sets of these parts, like all numbers greater than 100
Learn how to use Excel with tutorials, tips and tricks on functions, formulas, and features. and text-based data. What each section controls depends on how many sections there are in the number format
code. A full number format code will be entered as follows:
The Definitive Guide to Custom Number Formats in Excel
Excel has a lot of built-in number formats, but sometimes you need something specific. Whether you’re Section1;Section2;Section3;Section4
representing a little-used currency, tracking in-stock units, or want to color code profits and losses, you are in
The behavior of different parts of the number line will be as follows:
need of an Excel custom number format. Number formatting in Excel is pretty powerful but that means it is also
somewhat complex. This is the definitive guide to Excel’s custom number formats…
Using Custom Number Formats in Excel
By default, each cell is formatted as “General”, which means it does not have any special formatting rules. When
you enter data in a cell, Excel tries to guess what format it should have. When it doesn’t guess correctly, you need
to change the format. Excel has a few pre-set formatting options attached to buttons in the Home menu, but if
those don’t meet your needs, you need to use the full options available in the Format Cells menu.
To access this menu, look for the Number section of the Home menu tab. Click the arrow in the As indicated above, when there is just one section provided, it describes the format for all numbers. With two, the
lower right corner of the Number section. first section describes the format of positive, zero, and text values, while the second section describes the format
of negative values, etc.
You can choose to skip formatting for any of the middle sections by entering General instead of other format
data. For example, if you only want to affect positive numbers and text, you can enter a number format code with
this arrangement:
It will bring up the Format Cells menu in the Numbers tab:
Section1;General;General;Section4
General strips all formatting from the data entered, so be careful how you use it. Negative numbers with the
General format code will not display the minus sign in front of their number.
Important note: Using a single section number format code does not always have the same result as expanding
the same rules to all sections. For example:

Section1
Underneath the pre-defined number formats for common items like currency and
Is not the same as:
percentage, there is a category called Custom. The format types in this section are different from the pre-set
options. They are filled with symbols and codes:
Section1;Section1;Section1;Section1
Look at the examples below to see examples of the difference…
Now that we understand what a number format code is, what can we do with it?

Changing Font Color with Number Format Codes

One of the simplest things you can do with number format codes is change the color of the font in the affected
cells. The syntax for doing so is simple:

[Color Name]
Just choose the section that corresponds to the part of the number line you want to change color, and provide the
color in brackets. The color options are as follows (the background is gray for contrast in the table, but

A number format code is entered into the Type field in the Custom category. These codes are the key to
creating any custom number format in Excel. First, however, we need to understand how they work…
backgrounds are not affected by the number format code):
Understanding the Number Format Codes
As an example, we can provide a separate color code for each part of a number format code:
[Red]General;[Blue]General;[Magenta]General;[Cyan]General Results in the following output:
The General message just tells Excel to represent the numbers as entered by the user. The output of this number
format code looks like this:

Once again, this is different from:

General" units";General" units";General" units";General" units"

Note that the negative number in row 3 does not automatically get a negative sign (-) in front of it. We are Which results in:
overriding the default format of negative numbers in the cell. Also notice that the color format is not affecting
anything about the presentation; the number of decimal places stays the same, as does the alignment of the data to
the left or right of the cells.

Adding Text with Number Format Codes

You can add text around numbers with number format codes by inserting the text in a section one of
two ways:
Special Characters
Single Characters
In addition to these two methods, there is a set of special symbol characters that do not need a leading
For single characters (like an @ symbol before a number), type a backslash (\) followed by the symbol. backslash or quotes to be included in the number format code. The list is as follows:
The number format code:

\@General
Results in the following output:

Note that the minus sign still precedes the negative number. Also note that the Text value is not affected
by the @ symbol addition.
Importantly, this is different from the result if we expanded our format guideline to each section of the
number format code:
Excel will also accept most other non-mathematical symbols, such a non-dollar currency symbols,
\@General;\@General;\@General;\@General
copyright/trademark symbols, and Greek letters. These symbols are not available on most standard
Results in the following output: keyboards, but they can be entered by holding down the ALT key while typing in a four-digit number.
Some of the most useful ones are below:

Note that the minus sign is gone from the negative number and the Text value now receives the @
symbol.

Text Strings
To add an entire text string to a number (like adding “units” to the end of a number), we surround the
text string in quotation marks (” “). The number format code:
A full list of ANSI character codes can be found on Wikipedia here.
General" units"
Changing Decimal Places, Significant Digits, and Commas Here is an example of the pound sign code in action. The following examples are using this number
format code:
Adding symbols and colors is useful, but most of the work you’ll likely need to do with custom number
formats is change the way Excel displays the numbers it stores. Number format codes use a set of #.##
symbols to represent how the data should appear in the cell. Here is a summary of the symbols:

Period (.)
The period in the number format code represents the location of the decimal point in the number being
Let’s review them each in turn…
displayed. When paired with the comma code, it can show numbers in thousands or millions,
changing 1,200 to 1.2, for example. It is similar to the text format codes above in that it is always
Zero (0) displayed when it is part of the number code, even when number being displayed does not straddle the
Zeros in the number format code represent a forced digit. That means that whether or not the digit is decimal point. See the comma, pound sign and question mark examples above for useful illustrations of
relevant to the value, it will be shown. A great example of this is the standard dollars and cents notation the period in use.
that is used to represent prices in the United States: $0.00. Even if there are no extra cents in the
amount, the two zeroes are still shown in the notation. Comma (,)
Here is an example of the zero code in action. The following examples are using this number format The comma in the number format code represents the thousands separators in the number being
code: displayed. It allows you to describe the behavior of digits in relation to the thousands or millions digits.
Here is an example of the comma code in action. The following examples are using this number format
0.00
code:

$??,???.00

Question Mark (?)

Question marks in the number format code represent an alignment digit. This means that when the
number being shown doesn’t need the digit in question, a blank space of the same size is used. This is Asterisk (*)
used to align decimal and comma places for more easy ranking of values, etc.
The asterisk in the number format code represents the repeating character modifier. It is used along with
Here is an example of the question mark code in action. The following examples are using this number a character to display a repeating digit that fills the empty space in a cell.
format code:
Here is an example of the asterisk code in action. The following examples are using this number format
0.?? code:

*=0.##

Pound Sign (#)

Sometimes called a hash mark, the pound sign in the number format code represents an optional digit. Underscore (_)
This means that when the number being shown doesn’t need the digit in question, it will be omitted from The underscore in the number code represents the space character modifier. It is used along with a
the displayed number. This is most often used to represent numbers in their most easily readable form. character to display a blank space equal in size to the specified character. It can be used, for example, to
properly align positive and negative numbers when parentheses are used in only the negative case.
Here is an example of the underscore code in action. The following examples are using this number # ??/??
format code: Changes the representation of 0.23 from 23/100 to 3/13.
_(#.##_);(#.##) If you don’t wish to preserve the alignment around the fraction bar, you can use a similar fraction number
format code that uses pound signs.
Using pound sign notation, the following number format code:

# ###/###
Produces a more readable fraction remainder that can be justified or centered in the cell:

Using Fractions, Percentages, and Scientific Notation

Certain types of notation require that symbols be used to indicate the format change, including fractions,
percentages, and scientific notation. Here is a summary of the symbols for each:

Improper Fractions
If you’d rather bundle the whole number portion of a value into the fraction itself, you can specify as
We’ll examine each in detail… much in the number format code.
Using pound sign notation, the following number format code:
Fractions (/)
Fractions are special, since they require a change in units. The number 0.23 is represented as 23/100, ###/###
but 0.25 can be simplified t0 1/4 or shown as 25/100. Similarly, 1.25 can be shown as 1 1/4 or the Produces an improper fraction with up to three digits:
improper fraction 5/4. Which way Excel displays the number depends on how you construct the number
format code.
Fractions effectively round values to the nearest possible fraction. They also take the guidelines of the
pound sign and question mark symbols they are paired with.

Integer with Reduced Fractions

A fairly typical representation for fractions is to keep the whole numbers independent from the fraction Fixed Base Fractions
remainder. The representation for this is relatively straightforward and can be done with pound signs and
It is also possible to force Excel to round fractions to a specific denominator by specifying it in the
question marks to slightly different effect…
number format code.
Using question mark (?) notation, the following number format code:
Here is an example of a fixed base code in action. The following examples are using this number format
code:
# ???/???
Produces a fraction remainder with up to three digits: # ##/15

The result is a rounded fraction remainder that goes to the nearest number of 15ths.

Percentages (%)
The alignment of the fraction bar is preserved regardless of the number of digits in use. If we limit the
Much like fractions, percentages are controlled by the number format codes that accompany them. A
number of digits on each side of the fraction to 2, Excel will round the number to the nearest fraction
basic percentage can be achieved with a pound sign symbol in the number format code:
value. The following number code format:
#%
Results in the following output:

Note that in this case, the decimal and exponent are both constrained to 2 significant digits, regardless of
You can also specify fractional percentages, as shown with this number format code: whether they are necessary. The trade-off is, it keeps the output far more consistent, with a predictable
string length.
# #/#%
Results in single-digit fractions in the percentages where needed: Dates and Times
Dates and times in Excel are a special case. For a detailed discussion of how Excel uses them, please
review the Definitive Guide to Using Dates and Times in Excel. The number format codes work
identically to the format_text input for the TEXT command, and they can be reviewed here.

Finally, as always, you can specify the number of significant digits with decimal places:

#.0%
Results in a 10th place aligned decimal:

Scientific Notation (E)

It’s difficult to read extremely small and extremely large numbers conventionally because of all the leading and
trailing zeroes. Scientific notation fixes that by moving the decimal to the relevant digits, so 0.0000001 can
become 1 x 10-7. Excel uses the E notation for this, so that same number would be 1E-07. So, as you’d expect, the
capital letter E signals scientific notation in number format code.
Otherwise, scientific notation in Excel is controlled by the same number codes as percentages and fractions. It
needs a number format code in front of the E to describe the relevant digits and a plus (+) and another number
format code behind to describe the handling of the exponential digit.
Here is an example of a scientific notation code in action. The following examples are using this number format
code:

#E+#

You can also achieve more consistent notation with zeros. The following examples are using this number
format code:

0.00E+00