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.

2 Answers 2

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

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. 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
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 . Jun 25, 2018 at 12:07

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.