Notation for Thousands and Millions

To Bob, et al.,

The potential issue with your proposed custom number format is that it does not work as desired with negative numbers. Financial reports often have large positive and negative numbers, some of whose sign may not be known in advance.

This is where conditional formats can be very helpful.

In addition, you can then include additional formatting, eg: bold, italic, font colors, etc., as appropriate to highlight particular results based on, say, their magnitude compared to a threshold.

Hope this helps.

Thanks for the conditional format tip, Dave.
I can quite see that mixing units in a financial report could lead to misunderstanding. My usage would be more in the data storage field, where annotations such as Kb, Mb, Gb and Tb are more common.

[>999999]$#.0,,"m";[>999]$#,"k";$#,##0

To Kiwerry,

I've run into that same limitation that custom number formats only allow for two conditions.

Fortunately, you can get around that by using conditional formats to get as many custom number formats as you like. I haven't had to use this workaround often, by it's been handy when I did.

To Alan Cannon,

I've also run into some international accounting situations where millions is expressed as MM, or where there is a side note indicating numbers are in ($000s). For the latter, no suffix character is needed.

To all,

Personally, I prefer using one method and sticking with it all the way through with as few exceptions as possible. Mixing $x,xxxk and $y,yyyM in the same report is often confusing to the reader.

Thanks as aye for an interesting tip, Allen.
Comments:
The Custom Format approach allows further calculations which reference this cell; what is shown may not look like a pure (no Text) number, but it is, and it can be used in subsequent calculations.
The formula approach is more flexible; adding further tests (e.g. show a number as Tb or Gb or Mb or Kb or Bytes) is possible; Custom formatting rejected my attempt to add a third condition in front of the other two
In my experience the continued usability of the cell's contents is generally the deciding factor.

You can also use a custom format.
(see Figure 1 below)

In the engineering and scientific world, "m" is not millions; it is short for "milli" meaning 1/1000. Millions is designated with "M" for "Mega". For example, mg is milligrams and MT is megatons.

The only problem with the display option is that totals will work on the underlying figures and therefore the displayed numbers may not equal the displayed total.