When you have a list of numbers, it’s fairly easy to change between Currency, Accounting, and a host of other number formats.

What if you need a format that doesn’t exist in Excel?

For example, let’s say you have a lot of numbers in the millions, such as $9,266,696. Reading just that one number isn’t so bad, but what if you had a whole table with large numbers like that? Reading an abbreviated version of the same number, like $9.27M would be much easier, right?

Applying custom Excel formatting like that is also easy to do so you can still have the full number ($9,266,696) in the cell the same way you would always have it, but have it display on the screen as the abbreviated version. This way you can still use the number in formulas, and other functions.

  • Select the cell, or range of cells you want the custom format to apply to.
  • Right click and choose Format Cells… from the menu.
  • Select Custom from the left side under Category.
  • In the Type box enter:
    • $#.##,,"M";
  • Click OK.

Your numbers should now have the new format applied.

Here’s a breakdown of each part of that particular format:

For more information on custom number formatting, checkout the Custom Number Format guidelines here..