How to format numbers and thousands with decimals in Excel?
To display 23567 as 23.6K
, you can use this custom number format:
##0.0,"K"
Or as pointed out in the comments, depending on your regional settings for the usage of ,
and .
use the following format:
#0,0."K"
You could also display it as 23.57K
with:
##0.00, "K"
Note the place of the comma. By adding a second comma you can also interpret millions, so 1234567 would be displayed as 1.2m
:
##0.0,, "m"
The comma works as the thousands separator, and since all the examples have nothing to the right, i.e. no 0 or #, then nothing more is displayed other than the string.
Another very cool example is this:
[<1000000]£##0, "k";[>=1000000]£0.0,," m"
It will show any number in the millions as £1.2 m
, and anything less than a million as £800 k
Custom numbers can also be used in charts.
Related videos on Youtube
cant_get_it
Updated on September 18, 2022Comments
-
cant_get_it over 1 year
I wish to display the numbers
23567
and100
as23.6K
and0.1K
, respectively.I know the format
##0."K"
gets me close:24K
and0K
, respectively. But I'm unable to tweak it to do what I want.I'm on
Excel 2013
. I provided the format I'm using above because I think it might contain relevant information regarding the roles of.
and,
in my system. -
cant_get_it almost 6 yearsWhat worked for me was
#0,0."K"
, I basically had to switch the roles of.
and,
given in your answer. I knew my difficulties were somehow tied to this. Care to fix your answer so that it actually answers my question? I'll gladly accept it then. -
PeterH almost 6 years@cant_get_it yeah sure will update, i think there are many possibilities with custom numbers
-
PeterH almost 6 yearsHi @Rajesh that will only format numbers greater than 999 with the desired format, OP also wants 100 to show as 0.1k, you do not need [>=1000] in there
-
Rajesh Sinha almost 6 years@PeterH, thanks you prompted me,, in that case the format should be [>=100]##0.0,"K";0 .
-
BruceWayne almost 6 yearsThat conditional Formatting ability in the cell formatting box is really neat, I always forget you can do that. +1 for that alone!
-
PeterH almost 6 years@Run5k Yeah absolutely, I will take your comments on board and review with more scrutiny going forward.