Custom Cell Format (Numbers)

We can do wonders with ‘Custom cell formats’. I am planning few of my future post to throw light on ‘Custom cell formats’ alone.


OK… lets’ get started with ‘Custom Number Format’ … 


Custom Number Format is divided in 4 parts:

1. How to represent the Positive value
2. How to represent the Negative value
3. How to represent the Zero
4. Alert message, if alphabets are entered instead of numbers

Like you see in Bank Statements positive amount suffixed with ‘Dr’ and negative amount suffixed with “Cr’, have doubts about how to add ‘Dr’ and ‘Cr’ as suffix? You can find the solution below 


Step 1:

1. In 3 cells enter values as given in the ’Picture A’
2. In cell  4, sum up all the values (Alt + =)

Picture A

Step 2:

1. Select these cells with values, right click and select format cells
2. Select custom category and enter type as following (as given in Picture B) and click 'OK'

#,## \D\r;#,## \C\r;-;"Enter Number only"

Note: Sample area shows us the result of the custom number format (Picture B)

Picture B

You will get Result as given in ‘Picture C’

Picture C

Let me tell you how it works:

We have used the following symbols:

S.No	Symbol	Meaning
1.	#	It ignores the insignificant zeros
2.	,	Helps to separate thousands.
3.	\	Helps to display the next character (i.e. to print D, we need to put the symbol in front of D)
4.	;	Helps to separate 4 format types (i.e. positive number, negative number, zero, alert text.
5.	-	Just used to represent the ‘Zero’ (we can also enter ‘0’ or text ‘Zero’ to represent the value zero)
6.	""	All the text should be given with in the inverted commas.