Create Custom Excel Formatting
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:
- $ : Displays the dollar sign at the beginning of each number. You can use different currency symbols if needed.
- #.## : The pound/hash symbol is a digit placeholder. If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed.
- ,, : Since the commas are after the digit placeholder, the number is scaled by 1,000. Basically this will scale down everything to the right of the comma in the original number, so using two commas when formatting 1,000,000 will result in 1.
- “M” : Displays the M right after the number to indicate Millions. If your table or chart indicates something like “all amounts in millions”, you can omit this label.
- ; : The semicolon at the end defines the end of the current code section. Number formats can have four sections of code, all which should be separated by semicolons. The code sections define formats for positive, negative, zero value, and text in that order. If you wanted to format positive numbers in green text, negative numbers in red text, and zero in black, you can do so with this format.
For more information on custom number formatting, checkout the Custom Number Format guidelines here.