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 representing a little-used currency, tracking in-stock units, or want to color code profits and losses, you are in need of a 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 lower right corner of the Number section.

It will bring up the Format Cells menu in the Numbers tab:

Format Cells Dialog

Underneath the pre-defined number formats for common items like currency and 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:

Custom Formats Dialog

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…

Understanding the Number Format Codes

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 (;).

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 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:

Section1;Section2;Section3;Section4

The behavior of different parts of the number line will be as follows:

Section Format Behavior

As indicated above, when there is just one section provided, it describes the format for all numbers. With two, the 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:

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

Is not the same as:

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 backgrounds are not affected by the number format code):

Color Format Table

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

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:

Color Format Output

Note that the negative number in row 3 does not automatically get a negative sign (-) in front of it. We are 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:

Single Characters

For single characters (like an @ symbol before a number), type a backslash (\) followed by the symbol. The number format code:

\@General

Results in the following output:

Leading Backslash 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:

\@General;\@General;\@General;\@General

Results in the following output:

Leading Backslash Output Expanded

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:

General" units"

Results in the following output:

Quote String Output

Once again, this is different from:

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

Which results in:

Quote String Output Expanded

Special Characters

In addition to these two methods, there is a set of special symbol characters that do not need a leading backslash or quotes to be included in the number format code. The list is as follows:

Free Symbols Table

Excel will also accept most other non-mathematical symbols, such a non-dollar currency symbols, copyright/trademark symbols, and Greek letters. These symbols are not available on most standard 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:

ANSI Free Symbols

A full list of ANSI character codes can be found on Wikipedia here.

Changing Decimal Places, Significant Digits, and Commas

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:

Format Modifiers Table

Let’s review them each in turn…

Zero (0)

Zeros in the number format code represent a forced digit. That means that whether or not the digit is relevant to the value, it will be shown. A great example of this is the standard dollars and cents notation 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.

Here is an example of the zero code in action. The following examples are using this number format code:

0.00

Zero Behavior

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 used to align decimal and comma places for more easy ranking of values, etc.

Here is an example of the question mark code in action. The following examples are using this number format code:

0.??

Question Mark Behavior

Pound Sign (#)

Sometimes called a hash mark, the pound sign in the number format code represents an optional digit. This means that when the number being shown doesn’t need the digit in question, it will be omitted from the displayed number. This is most often used to represent numbers in their most easily readable form.

Here is an example of the pound sign code in action. The following examples are using this number format code:

#.##

Pound Sign Behavior

Period (.)

The period in the number format code represents the location of the decimal point in the number being 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 displayed when it is part of the number code, even when number being displayed does not straddle the decimal point. See the comma, pound sign and question mark examples above for useful illustrations of the period in use.

Comma (,)

The comma in the number format code represents the thousands separators in the number being 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 code:

$??,???.00

Comma Behavior

Asterisk (*)

The asterisk in the number format code represents the repeating character modifier. It is used along with a character to display a repeating digit that fills the empty space in a cell.

Here is an example of the asterisk code in action. The following examples are using this number format code:

*=0.##

Asterisk Behavior

Underscore (_)

The underscore in the number code represents the space character modifier. It is used along with a 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:

_(#.##_);(#.##)

Underscore Behavior

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:

Notation Symbol Table

We’ll examine each in detail…

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 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 remainder. The representation for this is relatively straightforward and can be done with pound signs and question marks to slightly different effect…

Using question mark (?) notation, the following number format code:

# ???/???

Produces a fraction remainder with up to three digits:

Fraction Remainder Question Mark Behavior

The alignment of the fraction bar is preserved regardless of the number of digits in use. If we limit the number of digits on each side of the fraction to 2, Excel will round the number to the nearest fraction value. The following number code format:

# ??/??

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:

Fraction Remainder Pound Sign Behavior

Improper Fractions

If you’d rather bundle the whole number portion of a value into the fraction itself, you can specify as much in the number format code.

Using pound sign notation, the following number format code:

###/###

Produces an improper fraction with up to three digits:

Improper Fraction Pound SignBehavior

Fixed Base Fractions

It is also possible to force Excel to round fractions to a specific denominator by specifying it in the number format code.

Here is an example of a fixed base code in action. The following examples are using this number format code:

# ##/15

Fraction Fixed Base Behavior

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

Percentages (%)

Much like fractions, percentages are controlled by the number format codes that accompany them. A basic percentage can be achieved with a pound sign symbol in the number format code:

#%

Results in the following output:

Percent Behavior

You can also specify fractional percentages, as shown with this number format code:

# #/#%

Results in single-digit fractions in the percentages where needed:

Percent Fraction Behavior

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

#.0%

Results in a 10th place aligned decimal:

Percent Decimal Behavior

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+#

Scientific Notation Behavior

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

0.00E+00

Scientific Notation Zero Behavior

Note that in this case, the decimal and exponent are both constrained to 2 significant digits, regardless of whether they are necessary. The trade-off is, it keeps the output far more consistent, with a predictable string length.

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.