[excel] Format numbers in thousands (K) in Excel

In MS Excel, I would like to format a number in order to show only thousands and with 'K' in from of it, so the number 123000 will be displayed in the cell as 123K

It is easy to format to show only thousands (123), but I'd like to add the K symbol in case the number is > 1000. so one cell with number 123 will display 123 one cell with 123000 will show 123K

Any idea how the format Cell -> custom filters can be used?

Thanks!

This question is related to excel excel-formula

The answer is


I've found the following combination that works fine for positive and negative numbers (43787200020 is transformed to 43.787.200,02 K)

[>=1000] #.##0,#0. "K";#.##0,#0. "K"


Non-Americans take note! If you use Excel with "." as 1000 separator, you need to replace the "," with a "." in the formula, such as:

[>=1000]€ #.##0." K";[<=-1000]-€ #.##0." K";0

The code above will display € 62.123 as "€ 62 K".


The examples above use a 'K' an uppercase k used to represent kilo or 1000. According to wiki, kilo or 1000's should be represented in lower case. So, rather than £300K, use £300k or in a code example :-

[>=1000]£#,##0,"k";[red][<=-1000]-£#,##0,"k";0

Enter this in the custom number format field:

[>=1000]#,##0,"K€";0"€"

What that means is that if the number is greater than 1,000, display at least one digit (indicated by the zero), but no digits after the thousands place, indicated by nothing coming after the comma. Then you follow the whole thing with the string "K".

Edited to add comma and euro.


[>=1000]#,##0,"K";[<=-1000]-#,##0,"K";0

teylyn's answer is great. This just adds negatives beyond -1000 following the same format.