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.

Editor's Picks