Format numbers in thousands K in Excel

Question

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

User · Answer

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.

User · Answer

Non-Americans take note  If you use Excel with     as 1000 separator  you need to replace the     with a     in the formula  such as     gt  1000         0   K    lt  -1000 -        0   K  0   The code above will display     62 123 as      62 K

User · Answer

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

User · Answer

gt  1000     0  K    lt  -1000 -    0  K  0   teylyn s answer is great   This just adds negatives beyond -1000 following the same format

User · Answer

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  -    gt  1000       0  k   red   lt  -1000 -      0  k  0

User · Answer

Custom format    gt  1000     0  K  0   will give you     Note the comma between the zero and the  K    To display millions or billions  use two or three commas instead

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

Examples related to excel

Examples related to excel-formula