Excel Custom Number Format Secrets.

Make Your Own Excel Magic.¹

Many of us use Excel every day, but few know about secret codes that let us format numbers exactly the way we want. They are not really secret, years ago they were they only way to format numbers. But like hieroglyphics, their existence and meaning have been lost through years of disuse.

How Do the Secret Codes Work? First let’s look at the difference between a cell’s contents and its format. The contents are the characters we type — they can be text, a number, a date or a formula.

Number format determines how non-text entries are displayed. The amount 1000, for example, can be displayed as 1000; 1,000.00; $1,000 or $1,000.00. The entries look different, but their values are the same.

Formatting Numbers. Normally Excel displays all digits that affect value, but nothing else. Thus the entry 01000.00 is displayed as 1000 since the value is the same with or without the leftmost zero and the zeros to the right of the decimal.

Number formatting lets us vary this behavior. Excel has built-in formats that cover most situations. The secret codes (above) let us write instructions for those they don’t.

The examples on the left show how different codes display various cell values. With no format the exact value is displayed. The codes #.00 and 0.00 both round to two decimals, but with 0.00 there is always a digit in the ones position, while with #.00 it only shows when it has value. The code #,##0 inserts commas. The last example shows a code that always displays five digits even where the leftmost zero has no value.

The built-in options provide all these formats. The samples are given to show how the codes will work in situations they do not.

Adding Labels. Number formats can also include labels such as currency symbols or units of measure. It’s easy, just add the characters to the code. Here are the rules:

• Single char­acter or space. Type char­acter where you want it. If it normally is a format code, put a “\” to its left.
• Multi-character entry. Put text inside quotation marks.

The first example is like the built-in currency format. But some people don’t like the dollar sign so close to the first digit, so the second sample shows how we can insert a space between them. The abbreviation “cm” is in quotation marks because it’s more than one character. And in the final sample there is a “\” to the left of the "m" because by itself “m” is a time format code.

Custom Rounding. Excel’s built-in formats round when you specify decimal places. For example, 6.125 formatted with two decimals displays as 6.13. But rounding to the left of the decimal, for example to nearest thousand or million, requires the secret codes.

The comma format code not only separates thousands, it also divides a cell’s value by 1000. Two commas divide by a million and so on. The samples show the results. These formats are useful for displaying large numbers in a small space, such as data labels on a chart.

Add Condi­tions. Sometimes we only want custom formatting in special cases. To do this put a condition in square brackets to the left of the format — the format then applies only when the condition is true. When it’s false, Excel uses the default format unless we provide our own alternative by putting a semicolon at the end of the conditional format and entering the custom alternative to its right.

The samples show how the condition only rounds by thousands when the value is 1000 or more. The second and third examples show different alternatives for values under 1000.

Right Click for Menu.

Add Color. To make color part of a custom format enter its name in square brackets at the beginning of the code.² It must be the first element in its section. The following code makes numbers green unless their value is 1000 or more: [Blue][>=1000]0,.0 K;[Green]0.

Where Are These Magic Codes? The secret codes are entered through the Number Format dialogue box. To get to it:

Click Number in Format Cell Dialogue.

1. Select (high­light) the cells you want to format.
2. Right click (Control + click on a Mac).
3. Choose “Format Cells” from the menu.
4. Click the “Number” tab at the top of the dialogue box.
5. Click “Custom” in the list of categories at the left.
6. Enter the codes in the box under the word “Type.”

* * * * *

Try it. You’ll be amazed how easy it is to make your own magic.

-----

1. Billy Rose Theatre Division, The New York Public Library. (1900). Kellar: Levitation.
2. Excel recognizes eight color names: black, blue, cyan, green, magenta, red, white and yellow.

Custom number formatting is covered in our Excel Charts and Excel Dashboards classes.

Visit our Excel Productivity Guides page for more helpful articles.

This article originally appeared in our free twice-monthly e-mail newsletter. To receive future issues, please add your name to the subscription list.

Want to learn more? We offer seven different Excel classes: Mastering Excel, Advanced Excel, Excel Charts, Excel Dashboards, Macros and Pivot Tables and Power Pivot. Click any class name for a detailed description.

We can also create a private Personalized Excel Class for your group, or provide individual training or project consulting services.