using 0 instead of # in custom number formatting

[lezawang]

Hi
I just watched a video on youtube about number formatting. I took screen shot of all the codes he was using, please see below. He only used 0 instead of #. I thought that is good idea because with 0 you can represent both the number and the zeros while with # you would only represent number not zeros. What do you think. Thank you so much.


https://drive.google.com/file/d/1p_aa-Yg0CYQ75DcstmEWSiT9agqjL0lc/view?usp=sharing

 

[Scott Huish]

It depends what you are trying to do. # can display zeros but basically it only displays significant digits.

So if your cell contained 12350 and was formatted as # it would still display 12350 (zero being displayed).
However if it was formatted as #.0 it would display as 12350.0, but #.# would display as 12345. because the 0 after the decimal point is not significant to the number (it doesn't change its value)

 

[lezawang]

Thank you for your reply. #.# should display 0 because it is significant here.
So 123450 would be 123450.

Am I right? Thanks again.

 

[lezawang]

Can you please tell me for the code 0,0.00 when excel will display 1 as 01.00
Why the 0 before 1? Thank you so much

 

[Scott Huish]

Because it's forcing a 0. With the 0 in the format.
That's the difference, as I said it depends on what you want.

If you don't want it, format it like this: #,#0.00

 

[lezawang]

Thanks for your reply. I wanted to know why for example for that code 0,0.00 if i enter 20 then I will get 20.00 (no zero before 2) and if type 300 then I will get 300.00 (no zero infront of 3) but when I type 1 then I will get 01.00

 

[Scott Huish]

Because there is a leading 0 before the single digit number.

In a simpler format, if you format the cell as 00 and type 1 you will get 01, but if you enter 11 you will get 11 because there is only 2 digits in the format. It is basically filling the empty spots with 0 in this format.

 

[lezawang]

Thank you. It is clear now. thanks again