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