Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 6 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .