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.

Consider the following scenario. I have a data column that contains a large list of numeric, whole number values. Some of these values are comparatively small (i.e. 1,000) while others are very large (i.e. 10,000,000,000).

What I am desiring is that smaller numbers (less than 1,000,000) should be displayed as a standard number, with commas. Larger numbers should be abbreviated by showing an M for Millions, a B for Billions, possibly a TR for Trillions,etc., next to a factored number of the actual value. Below are a few examples of the type of formatting that I am looking for:

| Actual Value  | Display Value |
+---------------+---------------+
| 100,000       | 100,000       |
| 999,999       | 999,999       |
| 1,000,000     |   1.0 M       |
| 1,250,000     |   1.3 M       |
| 5,000,000,000 |   5.0 B       |

Is there an easy way to do this without having to create a formatting function?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

Depends what you mean by "an easy way".

You can simply format the cells with a custom number format which may have up to three "patterns" such as this:

[>999999999.999]#.0,,, _-"B";[>999999.999]#.0,, "M";#,##0  _M

You could use conditional formatting to choose different number formats if you need to extend this to more than three levels (eg Trillions) as long as your version is Excel 2007 or higher. Hope this helps

share|improve this answer
    
That works. Thank you! –  RLH Dec 2 '11 at 17:29
    
You're welcome! –  AdamV Dec 2 '11 at 21:15
add comment

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.