internal custom number formatting solution:
sadly, the internal formatting in google sheets is by default able to work with only 3 types of numbers:
- positive (1, 2, 5, 10, …)
- negative (-3, -9, -7, …)
- zero (0)
this can be tweaked to show custom formatting like thousands K, millions M and regular small numbers:
[>999999]0.0,,"M";[>999]0.0,"K";0
or only thousands K, millions M, billions B
[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"
or only negative thousands K, negative millions M, negative billions B
[>-999950]0.0,"K";[>-999950000]0.0,,"M";0.0,,,"B"
or only millions M, billions B, trillions T:
[<999950000]0.0,,"M";[<999950000000]0.0,,,"B";0.0,,,,"T"
or only numbers from negative million M to positive million M:
[>=999950]0.0,,"M";[<=-999950]0.0,,"M";0.0,"K"]