1

I'm trying to create a custom number format that will display big dollar amounts with the typical k/M suffixes. Examples:

  • $1
  • $12
  • $123
  • $1.2k
  • $12.3k
  • $123k
  • $1.2M
  • $12.3M

And, actually, I partially managed with the custom number format:

$[>999999]#.#,,\M;$[>999]#.#,\k;$0

How it looks so far

My question is how to expand this custom number format to include negative numbers too:

  • -$1
  • -$12
  • -$123
  • -$1.2k
  • -$12.3k
  • -$123k
  • -$1.2M
  • -$12.3M

I would really like to avoid having to create an auxiliary row or column for this.

  • 1
    I think you are referring to this answer, which is great but does not extend any further. To have more formatting rules than that, one has to fall back on text concatenation in another cell. – user135384 Jul 17 '17 at 16:00
0

Google Sheets doesn't support more than two numeric ranges in a Custom Number Format (as of July 2017).

Therefore, the answer is: not possible without an auxiliary cell.

For my particular case, where I'm displaying dollar amounts, this is the auxiliary cell. Note that it will display $12.3k with one decimal point, but $123k instead of $123.4k. Similarly for negative numbers:

=if(A1 < -1000000, "$" & text(A1 / 1000000, "#.0") & "M", if(A1 < -100000, "$" & text(A1 / 1000, "#") & "k", if(A1 < -1000, "$" & text(A1 / 1000, "#.0") & "k", if(A1 < 1000, "$" & text(A1, "0#"), if(A1 < 10000, "$" & text(A1 / 1000, "#.0") & "k", if(A1 < 1000000, "$" & text(A1 / 1000, "#") & "k", "$" & text(A1 / 1000000, "#.0") & "M"))))))

Almost a duplicate of this other question, as Alex points out.

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.