Format numbers in thousands (K) in Excel

excel excel-formula
Alg_D picture Alg_D · Aug 13, 2014 · Viewed 235.9k times · Source

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!

Answer

teylyn picture teylyn · Aug 13, 2014

Custom format

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

will give you:

enter image description here

Note the comma between the zero and the "K". To display millions or billions, use two or three commas instead.

Related questions

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More