Menu
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!
634
If you want to show numbers in thousands, you could use #,##0, as the custom number format (see cell D8). To add a “K” abbreviation, the code is #,##0,K, as shown in cell D9. Each comma after the last zero in the code divides the number by a thousand. And for millions, use the code #,##0,, shown in cell D10.
Custom format
[>=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.
82
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".
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
