Format numbers as thousands,millions,billions with Zero

Question

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

How do I solve this problem?

Answer 1

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!