Format numbers as billions / millions / thousands, smartly

Question

Any recipes out there for a "smart" number formatting formula that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method?

I would expect to enter a number like: 1,816,724 and see 1.8M (or enter 2,394 and see 2.4K).

Solution

To format conditionally using Billions / Millions / Thousands in Google Spreadsheets or Excel, you can use:

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

To apply this setting in Google sheets, goto Format > Number > Custom Number Format and copy paste the above format string.

To learn more about formatting options, go to https://support.google.com/docs/answer/56470#zippy=%2Ccustom-number-formatting

Comments

[Marcel]

you want only K and M?

[radacina]

[<999950]0.0, "K" [$€-1];[<999950000]0.0,, "M" [$€-1];0.0,,, "B" [$€-1] ;@ improved a bit