# How to format numbers and thousands with decimals in Excel?

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.

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.

• 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 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! Jun 25, 2018 at 21:42
• @Run5k Yeah absolutely, I will take your comments on board and review with more scrutiny going forward. Jun 29, 2018 at 12:52

How it Works:

Apply this number Format to selected Cells.

``````[>=100]##0.0,"K";0
``````
• 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 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