Large Number, Abbreviated Formatting

Question

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?

Solution

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