Any recipes out there for a "smart" number formatting formula for Google Sheets 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).

  • It appears that you'd like to enter a number into a cell and see it formatted differently in the same cell. This is not something that formulas can do; a formula can't be based on the value of a cell, and would be overwritten by that value anyway. A script could replace your number with a formatted string, but they you won't have the original number there, and will not be able to use the value in calculations. So, I think the best you can do is to format the cells to use scientific notation: 1.8E+6 instead of 1.8M. – user79865 May 12 '15 at 18:06

The following custom number format mask will produce the desired results without losing the accuracy of the underlying value.


      Million and Thousand number formats

  • 1
    How do you add the K notation for negatives as well? – tsujp Dec 10 '17 at 5:36
  • You can add up to 3 conditions and have one default. – Jeeped Dec 10 '17 at 6:22
  • Ah, so you cannot have M, K, as well as negative M and negative K in the same one formatting rule. – tsujp Dec 10 '17 at 6:47
  • Any workarround for the negative numbers? – Emilio Nicolás Mar 5 '18 at 16:48
  • [<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B" this custom formatting is almost perfect. How can numbers up to 999 can be excluded to be represented as is? – Elad Ratson Mar 13 at 15:53

found it!

Same formula but with K, M and B!!! And also for negatives.

Article on the matter here: https://medium.com/@brookinc/the-only-spreadsheet-custom-number-format-you-ll-ever-need-7cc356a7ca3e


  • 2
    This is almost a link only answer. Please add the most relevant parts of the referred link or describe how it help you to do what the OP is looking for. – Rubén Feb 11 at 3:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.