The Only Spreadsheet Custom Number Format You’ll Ever Need

As something of a spreadsheet afficionado, I occasionally find myself wishing there was an easy way…

Excel and Google Sheets both offer scientific notation, of course, but I prefer something a bit more “skimmable”. So, I went googling, and found a StackExchange post on unit abbreviation which put me on the right path — although the formula it gave only worked into the millions, not billions (and would occasionally produce quirky formatting).

So I did a little reading and a little tweaking, and am proud to present… The One True Spreadsheet Custom Number Format:

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

’Tis a thing of beauty, is it not? It will do exactly what’s shown in the screenshot above. Magic! And/or Science!

(For the curious — using <999950 as the threshold instead of <1000000 prevents the confusing “1000.0K” from showing up instead of “1.0M” when a number is being rounded up to 1.0M)

You can use it in Google Sheets by selecting the cells you’re interested in, then clicking Format -> Number -> More Formats -> Custom Number Format, and then pasting in the above formula. Voila! If you’re using Excel, the steps for setting a custom number format are given in that StackExchange post.

(PS. The candy consumption numbers used in the example above are purely for illustrative purposes. I promise.)

Update: the above formula will only work as expected with positive numbers. For negative numbers, you can use an alternate version of the formula:

[>-999950]0.0,"K";[>-999950000]0.0,,"M";0.0,,,"B"

Or for a field that might be negative or positive, you can still format correctly into the millions (ie. between -999.9M and 999.9M) with a single number format:

[>=999950]0.0,,"M";[<=-999950]0.0,,"M";0.0,"K"

Cheers!

If you enjoyed this bit of nonsense, feel free to recommend it. If you like this kind of nonsense in general, feel free to follow. If you mostly just agree that it was nonsense, well, I appreciate that you scrolled to the end anyways.