Microsoft

Use a custom format in Excel to display easier to read millions

Use a custom format in Microsoft Excel to reduce the number of digits in a large number without losing its scale.

Large numbers are a bit laborious to read, especially if there are many of them. To improve readability, you might want to reduce the number of digits, without losing the number's scale. For example, the sheet below shows a number of large numbers, some in the millions. We can reduce the number of digits using a custom format, but be careful; you might get some unexpected results! (We're specifically working with millions, but you can apply this technique to any scale.)

Now, let's try a custom format and see what happens:

  1. Select the data range and press [Ctrl]+1 to display the Format Cells dialog.
  2. From the Category list, choose Custom.
  3. In the Type control, enter the $#,," M"; format string. The pound sign combined with the two comma characters displays a character in the millions position, if one exists. The " M" component displays a literal M character, to denote millions.
  4. Click OK.

The results look good, but a couple of values disappeared! That's because those numbers are less than one million. You could add a few pound signs, but you might not get what you expect. For instance, repeat the instructions above and use the format string $#,###, " M";. (The period after the semi-colon is grammatical and not part of the format string.) As you can see below, the results aren't accurate. You don't want to imply that $21 thousand is actual $21 million, but that's what this custom format does! In addition, someone might interpret $1,794 M as $1,794 millions.

We have two formatting problems. How can we express millions and less than one million using the same custom format? Let's try one more time using the format string $#.##,," M";--the character to the right of the pound sign is a period character, not a comma. This format retains the scale, without losing any values and without misrepresenting the values!

The two comma characters retain the millions scale. The period character forces the value to truncate, while the last two pound signs display the appropriately rounded digits.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
greginfla
greginfla

DISREGARD: (Found my answer; didn't see how to delete a submitted question) is there a way to have a trailing 0.  For example 4,000,000 instead of $4 m would be $4.0M

jeng02
jeng02

This was exactly what I was looking for! Saved me a lot of time! Thanks for sharing!!

djed
djed

Excel automatically rounds 5/4 for all display purposes.

tufte
tufte

I also see that this format automatically rounds to the closest digit (to be shown). How does it do that?

tufte
tufte

I almost skipped over this one :( I'm glad I didn't. This is also useful for anyone who has to deal with government data, particularly macroeconomic data. For the U.S., much of this data is already in trillions. That's 13-15 digits, and yet for most government data only 2-4 of them are actually significant digits. So, for my purposes, I'll also add a custom format for billions and for trillions.* I actually had to use this sort of simplification for a group yesterday. I don't want a fence on the Mexican border, but some people do. But, the common complaint is that it would be too expensive: a common price tag is $15,000,000,000. People can't digest this number. So I pointed out that this is $15B and our government budget is around $3,600B ... so that if it's something you really wanted it's just a drop in the bucket. * We are within a few years of getting some fairly common statistics needing to be denomination in quadrillions (thousands of trillions).

dhays
dhays

To me it would be clearer if the < 10^6 numbers had a leadin zero e.g. $0.15 M instead of $.15 M, it would be easy to miss the first place and the decimal point without a leading 0, and possibly misreading the .15 as a 15. Especially for us older folks with poor eyesight!

zimmerwoman
zimmerwoman

How would it look if I was showing a trend analysis and some of the numbers had negative trends to display in millions?

ssharkins
ssharkins

Change the first # to 0 -- $0.##,, "M" -- be sure to test it well, but that should work for you.

SAAsh
SAAsh

Based on the example above, negative values would appear using the last format mask string ($#.##,," M") will appear with a leading minus sign, a floating dollar sign and the number -- e.g. -1234567890 would be: -$1234.57 M Note that there is no comma separating the billions from the millions. Below are three options for negative values corresponding to Excel's "standard" Currency (with floating dollar sign and either a leading dash or parentheses) and Accounting (fixed dollar sign and parentheses) format options. All three include "floating" commas to separate millions from billions, trillions, etc. Using -1234567890 as the test value: Currency format with a dash in front of negative values (note that a separate format mask for negative values is not used in this example): $#,##0.00,," M" $1,234.57 M The "#,##0.00" portion insures that values less than 1 million will always display a 0 in the millions place along with the 2 places to the right of the decimal. The inclusion of the comma will provide separators between millions, billions, etc. Currency with parentheses around negative values: $#,##0.00,," M";($#,##0.00,,)"M" ($1,234.57)M Note that the string following the semi-colon -- i.e. ($#,##0.00,,)"M" -- is the section that specifies the mask for negative values. Also note that the space precediing the "M" character in the positive value mask is replaced by the closing parenthesis in the negative value mask in order to preserve alignment of positive and negative values. Accounting style (i.e. fixed "$" and parentheses): _($* #,##0.00,," M";_($* (#,##0.00,,)"M";_($* "-"_) $ (1,234.57)M Note the 3rd mask string above -- _($* "-"_) which specifies the mask for zero values. Similar to Excel's default Accounting mask, this displays a dash preceded by a fixed dollar sign to indicate a zero value (as opposed to a blank cell). The "M" character is not displayed for zero values. Of course, these are only 3 of many options -- none of which use the color option which can be added to the front of each section of a custom mask -- e.g.: _($* #,##0.00,," M";[Red]_($* (#,##0.00,,)"M";_($* "-"_) which will output negative values in red. Way too long of a response I realize but hope it helps.