6

I wish to display the numbers 23567 and 100 as 23.6K and 0.1K, respectively.

I know the format ##0."K" gets me close: 24K and 0K, 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.

Improve this question

2 Answers 2

Reset to default
10

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.

enter image description here

Improve this answer
4
  • What 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.
    – cant_get_it
    Jun 25, 2018 at 10:48
  • @cant_get_it yeah sure will update, i think there are many possibilities with custom numbers
    – PeterH
    Jun 25, 2018 at 10:50
  • That conditional Formatting ability in the cell formatting box is really neat, I always forget you can do that. +1 for that alone!
    – BruceWayne
    Jun 25, 2018 at 21:42
  • @Run5k Yeah absolutely, I will take your comments on board and review with more scrutiny going forward.
    – PeterH
    Jun 29, 2018 at 12:52
0

enter image description here

How it Works:

Apply this number Format to selected Cells.

[>=100]##0.0,"K";0
Improve this answer
2
  • 1
    Hi @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
    – PeterH
    Jun 25, 2018 at 11:58
  • @PeterH, thanks you prompted me,, in that case the format should be [>=100]##0.0,"K";0 .
    – Rajesh Sinha
    Jun 25, 2018 at 12:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .