Clever Geek Handbook

Excel number format for abbreviation

Here's what I want to do:

Value           Display
1               1
11              11
111             111
1111            1.11k
11111           11.11k
111111          111.11k
1111111         1.11M
11111111        11.11M
111111111       111.11M
1111111111      1.11B
11111111111     11.11B
111111111111    111.11B

This is the format used by Bloomberg to display currency digits. The rooms don't pass by B

.

Here's what I've tried so far:

[>999999999.999]#,,,"B";[>999999.999]#,,"M";#,##0_M

I stole it from here and I can't find any documentation that shows how to improve it.

+3


source to share


1 answer


When using conditions in custom number format, you can have a maximum of two conditions with one standard number and one default text format.

[>=1000000000]#0.0#,,, \B;[>=1000000]#0.0#,, \M;#0.0#, K

enter image description here

To achieve the fourth criterion, you need something that changes the format of the custom number. The conditional formatting rule seems appropriate here. Create it based on the following formula:



=$b2<10^3

Select "Format", then the "Number" tab, and specify a custom number format for 0_)

.

enter image description here

Of course, the CFR number format could potentially be two more conditions and the default, but you only need one additional format.

+5


source






More articles:


All Articles