BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Using Custom Format for Numbers, Dates and Text

What is 'Custom Format' ?: Microsoft Office Excel provides many built-in number formats, but in some cases they do not meet our needs, we can customize a built-in number format to create our own.

Why 'Custom Format' and not 'Conditional Formatting'?: because to cure 'Common Cold' we go to doctors not surgeons. Exactly, when there is a simple and robust way then why to go otherwise. There are many benefits of using custom format including these:
1. Less overhead than Conditional Formatting.
2. Values can be used easily in formulas (Less manipulation required).
3. Charts looks better with custom colored labels/axis.
4. Works on all versions of Excel.
and many more which you can figure out using your innovative mind after reading this article.

How to apply 'Custom Format'?
Step 1: Open 'Format Cells' dialog box using any of your favorite options:
        i)   Ctrl + 1
        ii)  Right Click --> 'Format Cells'
        iii) Home --> Numbers (Bottom right arrow) - See the pic

Step 2: Now click on 'Custom' and start typing your 'Custom Format' code.

'Custom Format' coding: A number format can be divided in 4 parts by semicolons. Each part is an individual number format. The first applies to positive numbers, second to negative numbers, third to zeros, and the fourth to text (This is by default or we can define this too).
So we can represent the custom format like this.

Positive Numbers ; Negative Numbers ; Zeroes ; Text
Of course we can skip whatever we want to as per the requirements and use it like any of this:
Positive Numbers;;Zeroes;Text
Positive Numbers;Negative Numbers;;Text
Positive Numbers;Negative Numbers;Zeroes
etc etc.
Even we don't have to include all semicolons. If we specify only 2 code sections for our custom number format, the first section is used for +ve numbers and zeros, and the second section is used for -ve numbers. If we specify only one code section then it is used for all numbers.
Whenever we skip custom format for a particular type, General format is assumed for the same. This technique comes very handy when we want to hide the values with no data in charts. We can use <any format>;;; to achieve this. For e.g.
#.##;;; etc. etc.

Let's move with some examples of various data. Essentially, examples makes this subject simple.
Leading Zeros 26 0026 0000
Phone Number 9999404843 999-940-4843 000-000-0000
Day of the Date 26/05/1984 Saturday dddd
Month of the Date 26/05/1984 May mmmm
Comma Place holder 23456789 23,456,789 #, ###
Currency 2605.5 € 2,605.50 € #,###.00

Decimal 2.1 02.10 00.00
Positive (Green) 2605 2,605 [Green]$#,##0;[Red]$#,##0
Negative (Red) -1984 -1,984
Trailing Dots eXceLitems eXceLiTems.......... @*.
Scientific Notation 123450 12.345E+04 ##.000E+00
123.450E+03 ###.000E+00
1.235E+05 0.000E+00
Round Off to 1000th 26051984 26052 #,
Align Decimals 26.051   26.051 ???.???
512.65 512.65 
3.6    3.6  

Prefix Text 12 EMP0012 "EMP" 0000
Suffix Text 125 1,250 lbs. #,##0 "lbs."
Textual Currency $5.75 5 dollars and .75 cents 0 "dollars and" .#0" cents"
Positive (Green) 512 512.00 [Green]#,##0.00; [Red](#,##0.00);[Blue]"Zero"
0 as Zero (Blue) 0 Zero
Negative (Red) -652 (652)
Varying Decimal Digits .023 .023 [RED][<0.1]0.000; [Blue][<1]0.00;[Green]0.0,
.23 .23
2.3 2.3
Elapsed Time
(Hours & Minutes)
Formula with Time difference 1:02 [h]:mm
Elapsed Time
(Mins. & Secs.)
62:05 [mm]:ss
Elapsed Time
3725.62 [ss].00
Calculate Kilo(103) 100000 100K [>=1000]0,"K"
Calculate Mega(106) 5000000000 5000K [>=1000000]0,,"M";[>=1000]0,"K";0
Align Fractions 6.25 6 1/4 # ???/???
5.43 5 43/100
7.75 7 3/4

By now either you have completely understood the Number/Date/Text Formatting using CUSTOM Format or not. In any case I'm sure that you'll be looking for more detailed information. So we will now move on to theory of 'Custom' format.

Before that, I've some exercise questions for you - if you can do them you are genius dude. Try on your own and devote sometime before you google them. This will also help you learn limited theory (which you actually need rather than whole post).

Negative Number -123000 (123K) ?
Date 26/05/1984 Sat, May '84
Currency 6.56 £7
Labels 25 25 feet
Round off to 1000000th 987654321 988

I'm waiting for your responses in comments ;)

So here is some theory for you:
Character Used as/for
0 (Zero) This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 2.6, and you want it to be displayed as 2.60, use the format #.00
# (Hash) This digit placeholder follows the same rules as the 0 (zero). However, Excel does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.3 in the cell, the number 8.3 is displayed and 2.657 is displayed as 2.66
? (Ques. Mark) This digit placeholder follows the same rules as the 0 (zero). However, Excel adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, see above(Last Intermediate Example)
. (dot/period) This digit placeholder displays the decimal point in a number.
Color Codes These are the only approved colour codes. [BLACK] or [Green] or [White] or [Blue] or [Magenta] or [Yellow] or [Cyan] or [Red]
Specify Conditions To specify number formats that will be applied only if a number meets a condition that you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, see 2nd intermediate or 5th, 6th, 10th and 11th advanced examples.
E (E+/E-) Exponential Notation/Scientific Notation: Displays a number in scientific (exponential) format. Excel displays a number to the right of the "E" or "e" that corresponds to the number of places that the decimal point was moved. For example see 5th Intermediate example.

kemas said...


illnino said...


ddd, mmmm, ‘yy


I couldnt figure it out


BTW, I have another question for you.

Is there any chance I could reformat "32187.56892" into 32200?

Any other answer instead of #,"200"?

Ashish Jain said...

Perfect illnino :)
instead of "$"0, you need to use £0 (£ can be obtained using ALT+0163)

and the fourth is 25" feet".

and I like your question, unfortunately I don't have answer for the same. I would be happy, if you have :)

illnino said...

What if I need to show '25 "feet"' instead of '25 feet'? I dont know how.

Ashish Jain said...

Try --> #,##0 '"feet"';