Millions thousands custom number formatting in Excel
Millions & Thousands Custom Number Formatting
To display 1,100,000 as
$1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting.
[>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General
This works
great... unless the number goes negative. For instance a negative million will
show as a negative thousand K. I've tried many different variations to attempt
the negatives to appear correctly however the initial order Excel views them
(positive; negative; zero; text) makes them all invalid.
I suppose
ideally what I'm asking is if you can combine Custom Number Format much like a
AND OR function? Something to the effect of...
positive millions "M" or
thousands "K"; negative millions "M" or thousands "K"; general
...
because I would want to format for either/ or millions or thousands at the same
time??
I hope this makes sense. Does anyone know of a way to do this? Or
better yet... an alternative to my insanity that Excel will accept?
Answer:
try entering this custom format [>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General