Displaying Values in 100s/1000s - Cell (custom) Format

Dear friends, if you don’t know how to apply ‘Custom format’, please refer Custom cell format (Numbers)


Displaying Values in 100s


Use this formatting to display values in hundreds with two decimal places. Meaning, the value you enter will be displayed as if it is divided by 100 and rounded to two decimal places.

0”.”00

Few examples:

Table 1.1 – How to display values in hundreds?

Entered Value	Format	Display
25	0”.”00	0.25
1234567	0”.”00	12345.67
12370	0”.”00	123.70
-12370	0”.”00	-123.70

Displaying values in 1000s

To show values in thousands, use this formatting #,###.00,. A value with this number format appears as if it’s divided by 1,000 and rounded to two decimal places. Meaning,  if you enter 1200500 excel will display 1,200.50.

If you use 2 commas without zeros at the end (i.e. #,###,),  this will avoid zeros after the last comma. Meaning, the value appears as if it’s divided by 1,000 and rounded to nearest whole number.  For example, if you enter 1200000, excel will display 1,200.

Few examples:

Table 1.2 - How to display values in thousands?

Entered Value	Format	Display
1200000	#,###,	1,200
120000	#,###,	120
-120000	#,###,	-120
120000.123	#,###,	120
499	#,###,	(blank)
500	#,###,	1
123456	#,###.00,	123.46
1234567	#,###.00,	1,234.57
499	#,###.00,	.50
500	#,###.00,	.50
123456	#,###.00	123,456.00
1234567	#,###.00	1,234,567.00
499	#,###.00	499.00
500	#,###.00	500.00
25	0”.”000	0.025
1234567	0”.”000	1234.567
12370	0”.”000	12.370

For better understanding the difference between each of them,

Table 1.3 - Difference

Value	Format
	0”.000	#,###,	#,###.00,	#,###.00
25	0.025	(blank)	.03	25.00
1234567	1234.567	1,235	1,234.57	1,234,567.00
12370	12.370	12	12.37	12,370.00
1246962	1246.962	1,247	1,246.96	1,246,962.00

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.