Writing Your Own Custom Number Formats in Excel
What Are Custom Number Formats?
I love to explain number formats in Excel as the “clothing” of a spreadsheet cell. You can dress your cell values in any way you’d like but even though the outward appearance is different, the underlying value never changes.
This concept is used most frequently with dates:
Did you know that a date is actually just a numerical number at its core? You can see the true value of the date used in the above example by looking at the “General” format result. However, most people dress this number up to look like a date with forward-slashes.
4 Parts of a Number Format Rule
There are four parts or sections to a Custom Number Format rule. The first section is required while the additional three are optional. Each section is divided up by the use of a semi-colon ( ; ). Here is what each part of the number format rule represents:
- If the number is positive then do this…
- If the number is negative then do this…
- If the number equals zero then do this…
- If the value is not a number then do this…
A Few Caveats
- If only the first section has a format rule, it will be applied to all numerical values whether positive or negative (text values will be left alone)
- If only the first two sections have format rules, zero values will use the positive value format
Using the Number Format Editor
In order to write your own custom number format rules, you will need to navigate to the rule editor. The editor resides within the Format Cells dialog box where you can modify all the properties/formats of a cell.
There are multiple ways to navigate to the Format Cells dialog box:
- [Method 1] Right-click on cell >> Select Format Cells…
- [Method 2] Home Tab >> Number Button group >> click Grey Arrow in bottom corner
- [Method 3] Use the Keyboard Shortcut: ctrl + 1 (PC) | cmd + 1 (Mac)
Once you have opened the Format Cells dialog box, you will want to navigate to the Number tab. This tab will show you a bunch of preset number format rules you can navigate through or if you would like to write your own rule, you can navigate all the way to the bottom of the Category Pane and click Custom.
Special Characters & What They Do
There are a few special characters you can utilize while writing a Custom Number Format rule to add even more varieties to your value’s appearance. Let’s first look at the special characters available to you and then we will get into some examples.
| Character | What It Does |
|---|---|
| @ | A placeholder for text |
| , | Separates thousands |
| * | Repeats character immediately following it |
| 0 | Forces the display of a numerical value |
| # | Placeholder for an optional digit |
| ? | Used to align digits at various lengths |
| _ | Add a space sized as the character immediately following it |
Just by including one of these symbols, your Custom Number Format rule will automatically use its special ability. If you wish to include one of these symbols without their ability, see how to do so in the “Escaping” section of this article (scroll down a few sections).
@ Symbol
The @ symbol is used to control where your text value shows up in your rule. You can place modifications to your text value before or after your text via relocating the @ symbol within the rule.
Comma Symbol
The comma symbol can be used to separate your numbers in thousands or to round large numbers to a specific place (Millions, Billions, etc…).
If you place a comma in front of your “ones” place, you will gain the ability to see a comma separate your value every three places. You only need to use a single comma in order to trigger this format.
If you place a comma behind your “ones” place, the value will VISUALLY lose three places (essentially dividing 1,000). This behavior continues to occur for each additional comma you add behind your “ones” place.
Asterisk Symbol
An asterisk symbol can be used to fill the remaining space within a cell with the character immediately following it. I’ve typically only seen this done when someone is creating a contact list where they would like dots to connect the values in column 1 and column 2.
Zero Number
Using a zero in a number format rule will force that number place to be shown visually. If you would like all your numbers to show three digits, insert three zeros into your rule and 1 will equal 001. This can be very usual in cleaning up your numerical values to ensure they all visually align with one another.
Pound/Hash Symbol
The pound (or hash) symbol serves as an optional placeholder for digits if they exist. If you value exceeds the number of pound signs to the right of your decimal, the format rule will round your value to align with your designated amount of pound symbols. If your value has less digits than pound symbols, a zero will not populate in its place.
Question Mark Symbol
Question marks can be used to align digits when you don’t necessarily want zeros to show up as numerical placeholders. When a question mark resides in a place where no value is provided, a space will be added (shown in grey below) to maintain the alignment of the number.
Underscore Symbol
By using the underscore symbol you can add a single space either before or after your cell value. The character immediately following the underscore determines the size of the space. In most cases, Excel users use the underscore symbol to line up positive and negative numbers that use parenthesis.
Escaping Special Characters
There may be instances where you literally want to use one of the above characters instead of utilizing their special abilities. To remove the special ability (or “escape” the ability), just place a back-slash before the character. You’ll need to place a backslash before each individual symbol you wish to escape.
Adding Text
There may be occasions when you would like to add text before or after your values but still would like to perform spreadsheet math with your data. With Custom Number Format rules, we can easily accomplish making numerical values appear as text visuals while maintaining their cell value.
Other Resources
There are many other things you can do with Custom Number Format rules. Below are some links to other articles/tools you might find useful as you continue to learn more about creating these custom rules.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.