Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've grown to love the StackExchange number format for question views: after a particular question passes 1000 views, the view count is displayed as 1k. This format has become second-nature to me, and is very easy to understand.

How can I implement this number format in Excel?

As an example, see below:

Data with general number format

I want to make the bar chart labels to display 10.2k, 1k, and 626, respectively. (I want to use the format in a budget display, but I'm using SE reputation because it's safe to share.)

share|improve this question

1 Answer 1

I have found a method that seems to work, but it has some room for improvement. (I'm still looking for other options, even though I'm posting this.)

Use the number format code: [<1000]0;[>=1000]#.0,k

This displays the just the number (no decimals) if it's less than 1000. If the number is greater than 1000, then it displays the number divided by 1000 and rounded to one decimal with a following k.

Applied to the prior example:

Data with SE number format

Notice that, since this is a number format and not a formula, it can be applied to any number (even the axis labels).

Problem: This doesn't quite fit the question's requirements, because it would be best to output 1k instead of 1.0k. I can get rid of the unnecessary zero with a # instead of a 0 in the format string, but it leaves the ..

share|improve this answer
    
I've not played with custom number formats much, but can you specify a 3rd range? if so make when to display 1k a 3rd condition. –  Tyson Aug 6 at 4:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.