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 in a cell, and via a formula, display the number in another cell formatted as: 1.8M (or display 2,394 as 2.4K, etc.).

  • 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, 2015 at 18:06

5 Answers 5


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


      Million and Thousand number formats

  • 2
    How do you add the K notation for negatives as well?
    – tsujp
    Dec 10, 2017 at 5:36
  • 3
    You can add up to 3 conditions and have one default.
    – user82329
    Dec 10, 2017 at 6:22
  • 1
    Ah, so you cannot have M, K, as well as negative M and negative K in the same one formatting rule.
    – tsujp
    Dec 10, 2017 at 6:47
  • 1
    Any workarround for the negative numbers? Mar 5, 2018 at 16:48
  • 6
    [<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? Mar 13, 2019 at 15:53

This was WAY too hard to find. Thanks for posting. Google changed the "prefix" and "suffix" settings without warning.

If you want "$" added to the beginning and 0 is 0 instead of 0.0K, you can use:


changes $20,000,000 into $20M

Works for charts, which is where Google changed the feature.


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

  • 3
    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. Feb 11, 2019 at 3:58

0.0,"K" one comma gives you a number in thousands $0.0,,"M" two commas gives you a number in thousands It is the number of commas that matters here, not "M" or "K".


To display both negative and positive, use the following formula in Format > Numbers > Custom number format:


  • Hi Diego, this answer can be improved by addressing the full question ("K" & "M"). You addressed negatives which was done in another answer. Combining everything in one clear answer would at that point make it valuable to answer this 7 year old question.
    – Blindspots
    Oct 8, 2022 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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