36

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.).

Improve this question
1
  • 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
    Commented May 12, 2015 at 18:06

5 Answers 5

Reset to default
50

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

[>999999]0.0,,\M;[>999]0.0,\K;0

      Million and Thousand number formats

Improve this answer
7
  • 2
    How do you add the K notation for negatives as well?
    – tsujp
    Commented Dec 10, 2017 at 5:36
  • 3
    You can add up to 3 conditions and have one default.
    – user82329
    Commented 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
    Commented Dec 10, 2017 at 6:47
  • 1
    Any workarround for the negative numbers?
    – Emilio Nicolás
    Commented 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?
    – Elad Ratson
    Commented Mar 13, 2019 at 15:53
5

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:

[>=999950]$0,,"M";[<=-999950]$0,,"M";$0

changes $20,000,000 into $20M

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

Improve this answer
3

found it!

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

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

Improve this answer
1
  • 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.
    – Rubén - Volunteer Moderator - ♦
    Commented Feb 11, 2019 at 3:58
0

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".

Improve this answer
0

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

[>999]$0,"K";[<999]$0,"K";$0;

Improve this answer
1
  • 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 ♦
    Commented 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.