Custom Number Format - Examples

We have been seeing about different formatting available in Excel. In the last post, we saw about the codes used for custom number formats. Let’s see few examples now on how to use the code we learnt.

Display Whole Positive numbers ONLY

Yeah, weird, but I want it. I want to display the contents of the cells only if they are positive whole numbers like 1, 2, 3...and so on. Any others like negative numbers, text or decimals should not be shown in the cell. Let’s see how to code them.

I will break down our logic in parts.

• We want positive numbers to display but no decimals
• We don’t want to show negative numbers
• We don’t want to show zeros
• No text either

From what we learnt from the code in last post,

• Every cell can have 4 parts of display.
• If we don’t want a part to display, we can just enter a semi colon in that place.
• To display numbers we can use any one of 0, # and ? depending on your need. I will use 0.

Following the above logic, I can deduce the below code.

0;;;

Select a cell and apply above custom format. Enter different types of data in the cell and see how Excel will display or hide them.

Similarly you can code to show only negative numbers (use ;-0;;) note the minus sign before zero;, display zeros only (use ;;0;) and to display text only (;;;@). Needless to say, you can use combination of two or more criteria, such as show only positive and negative numbers and hide zeros & text (use 0;-0;;).

To display numbers with up to 2 decimals, you will replace 0 in above codes with 0.00 and so on based on your requirement.

Show Positive Numbers in Green, Negative in Red, Zeros in Blue and Text in Yellow

[Green]0;[Red]-0;[Blue]0;[Yellow]@

Display Nothing

;;;

Always Display X Number of Digits (Leading Zeros)

Bank account numbers for example will have fixed number of digits. Account numbers that doesn’t have those many digits will be prefixed with leading zeros. Like, if fixed length is 10 digits and you enter 12345, Excel can show it as 0000123545. If that is your need, you can use below code.

0000000000;;;

Above code should contain those many zeros as the number of digits you want. It is coded not to show negative, zeros & text.

Below is the picture showing how positive, negative, zeros & text appearing under different format codes as given above.

Display Telephone Number Style 1-800-333-3333

0-000-000-0000

Entering 1800333333 in this cell will show the number as 1-800-333-3333.

Display Numbers in Thousands / Millions

Round Off To Nearest Millions / Thousands / Numbers Based On the Number Entered

[>=1000000]0,, ;[>=1000]0, ;0

Above code displays values greater than or equal to 1 million rounded to nearest one million. If the entered number is not greater than or equal to 1 million, but is greater than or equal to 1000, then the value is rounded to nearest thousand. If neither condition is met then the figure will be displayed rounded to the nearest whole number.

Try different combinations in the code and explore it for yourself. Remember, you will learn excel better by doing trial and error yourself. But if you tried and couldn't get some format you needed, let me know. Happy Learning!!