Excel Custom Number Format: Writing Rules, Code, and Examples

Attention! This guide does not just discuss the meaning of Custom Number Format in Excel or its uses. Instead, I will explain the basic rules of custom number format, how it works, to the meaning of each code, and examples of how it is arranged in depth.

As we discussed in the previous sub-chapter, Excel has many types of built-in number formats such as general, text, currency, accounting, and special.

If the default number formatting doesn’t meet your needs, it’s time to use a custom number format.

Before continuing, brew your coffee first. Because this is going to be a very long guide. Then, to make your practice easier, please download the following Excel Custom Number Format file:

What is Custom Number Format in Excel?

Custom Number Format Excel is an advanced cell formatting feature (which contains numbers or dates and times) that is useful for changing the appearance of data as desired without changing the data values.

For example, you can abbreviate a large number like 2,360,000,000 (Two Billion Three Hundred and Sixty Million) to 2M or 2.36M without changing the value.

Excel still recognizes the “valid” number 2.36M as 2,360,000,000, so you don’t need to be afraid of errors in formulas/functions when processing your data.

Not only insert units, you can also format numbers, date & time, change colors, and apply formatting criteria as desired.

All of this can be applied as long as there are no errors in writing the custom number format.

Custom Excel Formatting Writing Rules

Not many people know that the custom number format in Excel also has writing rules like other Excel formulas/functions.

For example, the VLOOKUP formula consists of 4 arguments, namely lookup_value, table_array, col_index_num, and [range_lookup].

Likewise, the Custom Number Format has its arguments, namely:

Rules for Writing Custom Number Format in Excel:
POSITIVE; NEGATIVE; ZERO; TEXT

All arguments are optional. This means it can be used or not. So if you want to use a custom number format, you only need to use at least 1 argument as needed.

Note: In formulas/functions, Excel uses different formula separator operators for certain regions (i.e. semicolon “;” and comma “,”). Meanwhile, Excel’s custom number format only uses semicolons “;”. So, whatever your Excel regional format, use the semicolon “;”.

How to Create a Custom Number Format in Excel

To create a custom number format in Excel, please follow these steps:

1. Select the data range that will use the custom number format.
2. Click the Home Tab on the Ribbon.
3. In the Number Group, click Format Number Option (or press Ctrl + 1 ).
4. Click Custom Category.
5. Select an available built-in custom format, or
6. Enter the custom code in the Type section. For example #,##0.00;[Red]-#,##0.00;"-";[Blue]@.
7. Click OK to apply. This is the result:

I hope that at least you understand stages 1 to 4. Next, there are many things you can explore starting from stage 5.

So I’ll start first with how the Excel Custom Number Format works.

How Custom Number Formats Work in Excel

Let’s try to retrieve the code used previously.

Custom Number Format in Excel with 4 Arguments:
#,##0.00; [Red]-#,##0.00; “-“; [Blue]@

This Custom Excel Formatting will change the appearance of values in cells in the following ways:

1. #,##0.00 (POSITIVE argument), this code will display the numbers in the cell using the thousands format with the thousands separator in the form of a comma #,##0, and has 2 decimal numbers with a dot separator #,##0.00.
2. [Red]-#,##0.00 (NEGATIVE argument). The only difference with the first argument is the color code and the negative symbol [Red]-. This means that this code will display negative numbers using thousands format with 2 decimal places and using red font.
3. "-" (ZERO argument), this code is to format cells containing the number 0 (zero) into a hyphen/minus symbol “-“.
4. [Blue]@ (TEXT argument), this code is to format cells containing text and change the font color to blue.

Note: Each argument has its code such as pound #, [Red], @, and so on. Relax, I will explain this in the next section.

What you need to know is that you aren’t required to use the four Custom Number Format arguments.

For example, suppose you only use a POSITIVE argument such as: #,##0.00. In that case, it will change cells containing positive, negative, and 0 (zero) numbers as follows:

Meanwhile, if you only use 2 arguments (POSITIVE & NEGATIVE), the POSITIVE argument will format positive numbers and 0 (zero), while the NEGATIVE argument will format only negative numbers as follows:

If you use the POSITIVE; NEGATIVE; ZERO argument, then the POSITIVE argument only formats positive numbers. Meanwhile, the ZERO argument takes on its task (for cells containing the number zero).

Likewise, the TEXT argument will only format cells that contain text.

You can also use just one argument by leaving the previous argument blank.

For example, if you only use the NEGATIVE argument, then the code structure is as follows:

NEGATIVE Arguments Only:
;[Red]-#,##0.00

ZERO Arguments Only:
;;“-“

TEXT Arguments Only:
;;; [Blue]@

The most important thing: every time you empty an argument, you are still required to enter the argument separation operator (semicolon “;”).

Understanding the Meaning of Each Code in Custom Number Format Excel

When writing a custom number format, you cannot use code carelessly. Because each code has its use.

There are codes used for numbers and dates, changing colors, and inserting text.

#1 Decimal Code, Thousands, etc…

1. The number zero (0) displays the decimal number and optionally enter the number 0 (in decimal). One number 0 represents 1 decimal number.

For example, the code 0.0 is applied to the numbers 22000 and 22000.55, then the data will always be displayed in 1 decimal digit, namely 22000.0 and 22000.6. See the following example:

Important! This code 0.0 is used in Excel’s default number (decimal) format.

2. The pound (#) character displays a decimal number without entering an additional 0.

Unlike the number code 0, the # character will not display decimal numbers for numbers that don’t have a decimal value other than 0 (zero).

For example, if the code #.# is applied to the numbers 0.5 and 5, it will be displayed as .5 and 5.

Note: You can also combine the character # and the number 0. For example, the code #.0 for the number 5.55 will display as 5.6. So the value will always be displayed in 1 decimal place. And Excel will round the number if necessary as in the following example:

3. The question mark (?) character is slightly similar to 0.

The difference is, that this character will be inserted with a space (not the number 0). So code #.??? for the numbers 22000 and 22000.555 it becomes 22000. and 22000.555 as follows:

This character inserts a blank space and aligns the decimal separator position for all results.

4. The comma (,) character: In the US region is used as a thousand separator. Meanwhile, in other regions such as Indonesia, it is used as a decimal separator.

5. The dot (.) character in the US region is a decimal separator. Meanwhile, in other regions such as Indonesia, it divides thousands.

In practice, you can combine the comma (,) and period (.) characters together to display thousands and decimal separators.

For example, applying the code #,##0.00 to the numbers 22000 and 22000.555 will display 22,000.00 and 22,000.56 as follows:

One Question… How to Determine the Number of Decimal Digits?

The decimal placement in the custom number format code is after the dot character (.) for the US region and the comma character (,) for other regions such as Indonesia.

Meanwhile, the number of decimal digits is determined by how many zeros (0) there are after the decimal separator in the code.

So, the code 0 or # alone willn’t display a single decimal digit. Please replace it with 0.0 or #.0, this code will display 1 decimal digit.

To add decimal digits, please add a zero code such as 0.00 or #.00 for 2 digits, code 0.000 or #.000 for 3 decimal digits, and so on.

#2 Code for Text and Spaces

With Custom Number Format, you can also insert text or spaces into cells without changing the cell value.

Note: There is 1 special character to represent the text in the cell to be formatted and 4 characters to insert text or spaces.

1. The et (@) character represents the text to be formatted but doesn’t apply to data in numbers. The @ character only applies to TEXT arguments in the code layout.

For example, like the first example above, I use the code #,##0.00;[Red]-#,##0.00;"-";[Blue]@, this code only changes the font color of cells that contain text.

You can also use the general format for POSITIVE, NEGATIVE, and ZERO arguments while using [Blue]@ for TEXT arguments as follows:

2. Backslash (\) character to display the character typed after this backslash character. For example, the code #.0\g will display the number 36.2 as 36.2g.

3. Underscore (_) character to make characters typed after this underscore invisible.

Usually, the underscore character is often used to hide the opening bracket “(” and closing bracket “)” so that positive and negative numbers appear parallel as follows:

So with the code #,##0_);(#,##0) it will insert a closing parenthesis after the positive number, but not display it. Meanwhile, negative numbers appear inside the closing brackets.

So this is what makes positive numbers appear parallel to negative numbers.

4. The asterisk (*) character makes the next character appear repeatedly until it fills the cell. For example, by using the code #*-, the minus symbol will appear in full after the cell contents.

5. The double quote (” “) character displays text and spaces typed in double-quotes. You can insert text right before or after the number code.

For example, using the code "Up: "#,##0.00 for the number 20500 will display Up: 20,500.00.

And of course, you can always apply it to NEGATIVE arguments too. For example, the code "Up: "#,##0.00;"Down: "-#,##0.00;"-" for data that varies as follows:

Note: To insert a space, Insert a space at the beginning or end of the text in double quotes. This will depend on whether the text is at the beginning or end.

Special Symbol Codes: Trade Mark (™), Copyright (©), Degree (°), etc.

You can also apply special symbols such as Trade Mark (™), Copyright (©), Degree (°), etc. into a custom number format. All of these special symbols must also be enclosed in double-quotes.

For example, the code arrangement #.0"°C" will insert a degree symbol as follows:

As a reminder, if the data in the cell is text and you want to display the Trade Mark (™), then use the following code ;;;[Blue]@"™" in the TEXT argument. Resulst:

By leaving out the POSITIVE, NEGATIVE, and ZERO arguments, Excel will only execute the TEXT argument.

Special Characters Without Needing to be Enclosed in Two Quotes

By the way, to add text into a custom number format, you have to enclose it in double quotes ” “.

However, this rule can be excluded for the following characters:

Characters	Name
$	Dollar sign
+	Plus sign
:	Colon
=	Equal sign
–	Minus sign
/	Slash mark
^	Circumflex accent (caret)
‘	Apostrophe
(	Left parenthesis
)	Right parenthesis
{	Left curly bracket
}	Right curly bracket
<	Less-than sign
>	Greater-than sign
&	Ampersand
!	Exclamation point
~	Tilde
	Space character

So, you may use the code sequence >#,##0.00 to add symbols larger than the number 2000 to >2,000.00.

#3 Code for Currency

As I mentioned above, the currency symbol $ (dollar) is a symbol that doesn’t need to be enclosed in double-quotes.

So, if you want to display the dollar currency symbol, then type the symbol directly into the code as follows: $#,##0.0.

Meanwhile, to display currency symbols for other countries such as Rp (Rupiah), please enclose them in double quotation marks as follows: "Rp"#,##0.0.

If you want to insert special currency symbols such as Euro (€), Pound Sterling (£), or other currencies that aren’t on the keyboard, then you can use keyboard shortcuts.

To do this, activate Num Lock on the keyboard and press the following code combination using the numeric keys.

Symbol	Currency	Code
€	Euro	ALT + 0128
£	Poundsterling (Inggris)	ALT + 0163
¥	Yen (Jepang)	ALT + 0165
¢	Cent Sign	ALT + 0162

For example, the code ALT + 0128 means press and hold the ALT key on the keyboard while pressing the number keys 0 1 2 8 sequentially.

#4 Percentage, Fractional and Exponential Codes

1. The percentage (%) character is used to display numbers in percentage. You can insert the % symbol directly into the code layout.

For example, this code #% will display the number 0.025 to be 3%. While the code #.0% will display 2.5%.

Note: With the percent code (%), Excel will display the original number multiplied by 100%. For example, cell C1 = 0.025 x 100% = 2.5%.

So if you want to display 25%, then the original value should be 0.25 (not 25). If you apply the code #% to the number 25, then Excel will display it as 25 x 100% = 2500%.

2. The slash (/) character to display numbers in fraction form. The slash sign is useful as a separator between the denominator and the numerator.

The simplest example, try using #/# various decimal numbers. And see the results:

As you can see, you can use a variety of formats to determine the number of digits of a fraction.

Note: Please note that Excel provides special treatment for fractional values.

For example, the result in cell E6 is ⁶⁹/₈ with the original value being 8.6. Try calculating using an Online Calculator 69 / 8 = 8,625. And this result isn’t the same as the original value, namely 8.6.

This isn’t a wrong result. But Excel applies special treatment to fractional numbers, that Excel will display the closest value of the fraction format you use.

Apart from using the pound sign (#), you can also use the question mark (?) like this code # ?/?, and see the results:

You can judge for yourself which result is neater.

3. The letter E (exponent) displays numbers in Scientific Notations (Exponential Format). To use it, you only need to add the letter E to the code arrangement.

For example, if you use the code 0.00E+00 to number 25000, it will appear as 2.50E+04 as follows:

#5 Color Codes and Certain Conditions

To change the font color using a custom number format, you can use the color code enclosed in square brackets [ ].

Currently, Excel supports 8 main colors, namely:

Code	Colors
[Black]	Black
[Green]	Green
[White]	White
[Blue]	Blue
[Magenta]	Magenta
[Yellow]	Yellow
[Cyan]	Cyan
[Red]	Red

Note: The color code must be at the beginning of each argument. For example, to change the color of positive numbers to blue, you can use the following code: [Blue]#_);(#);"-";@.

Apart from being used to change colors, square brackets [ ] are also used to enter logic testing requirements into Excel format custom number codes. For example, I’ve data like the following image:

Then, I applied the following Excel format custom number code snippet:

Custom Number Format Excel with Criteria:
[Red][>25]#.00

It means:
Change Color to Red. Semua nilai yang >25 dan gunakan format #.00

Results:

As you can see, Excel will only change the display to values that meet the >25 criteria. That’s why cells B4 and B5 do not change at all according to the #.00 format.

#6 Code for Date and Time

Custom Number Format Excel can also be used for data in the form of valid dates or times.

For dates, 3 general codes can be used, namely:

1. d (day) for day.
2. m (month) for month.
3. y (year) for year.

When using it, you must use a certain format as follows:

Code	Results
d	Day digits (1-31).
dd	2-digit day number (01-31).
ddd	Day abbreviations (Mon, Tue, Wed, to Sun).
dddd	Full name of the day (Monday, Tuesday, Wednesday, to Sunday).
m	Month digits (1-12).
mm	2-digit month number (01-12).
mmm	Month abbreviations (Jan, Feb, Mar, to Dec).
mmmm	Full name of the month (January, February, March, to December).
mmmmm	The first letter of the month (J, F, M to D).
yy	The last 2 digits of the year.
yyyy	4-digit year number.

For a time, here are several types of codes that can be used, namely:

1. hour (hour) for hours.
2. m (minutes) for several minutes.
3. s (seconds) for a few seconds.
4. AM/PM for 12 hour format.

Meanwhile, the combination can use a code like the following:

Code	Results
h	Hour digits (0-23).
hh	2-digit hour numbers (00-23).
m	Minute digits (0-59).
mm	2-digit minute number (00-59).
s	Second digits (0-59).
ss	2-digit seconds number (00-59).
[h] atau [m] atau [s]	Especially for numbers that are more than 24 hours, 60 minutes, or 60 seconds (can be the result of a formula) without rounding.
AM/PM	12 hour time: AM for midnight to noon. PM for midday to midnight.

In practice, you can also use am/pm, A/P, or a/p instead of AM/PM.

Note: Just one guide is not enough to understand the breadth of the Custom Number Format feature in Excel. For a complete example of how to use a custom number in Excel format and a detailed explanation, I will discuss it in the next sub-chapter.