0

i want to use format number with 00K for thousands & 00M for millions & 00B for billions & - for zero I used the following format [<999999] #, "K";[<999999999]#,, "M";#,,, "B" but the problem is in cells that contain zeros it shows K As you can see in the yellow cells

open photo

How do I solve this problem?

Milanosap
  • 3
  • 3
  • Conditional formatting for cells equal to 0 and format the number to display a "-". How do you want to handle numbers less than 499? – I like Excel very much Oct 01 '20 at 22:17
  • I tried to change the format , but to no avail Please write the format number that returns all letters K & M & B , keeping zero "-" – Milanosap Oct 02 '20 at 16:16
  • The explanation is a little long for the comments section, see my answer posted. That takes care of 0, which was the question asked, but how do you want to handle negatives, values between 1 and 499, and numbers greater than a trillion? – I like Excel very much Oct 02 '20 at 17:44

1 Answers1

0

The basic issue that you're running into is that the custom formatting can only handle two conditionals at once (see Exceljet for a good breakdown of how custom number formatting works). You already have two conditions; less than 999,999 and less than 999,999,999.

To get a third condition of equal to zero we need to turn to conditional formatting. Select the array you would like to format, open conditional formatting and select "new rule", select "format only cells that contain" and set it to "cell value equal to 0". You can than click "format" and in that menu set custom number formatting to just "-".

Let me know if that helps!

I like Excel very much
  • 730
  • 6
  • 13
  • Yes, I already tried it before, but I wanted it in one format, but it is clear, as you said, that it don't accept more than two conditions Thank you – Milanosap Oct 03 '20 at 07:33