Custom Number Formats
What Are Custom Number Formats?
Custom number formats control how numbers are look in Excel. The key benefit is that they change how a number looks without changing any data.
Before we get started, here’s an awesome resource Excel Custom Format Builder
This concept is used most frequently with dates.
Excel Custom Number Format Rules - Dates
Input | Code | Result |
---|---|---|
20-Apr-2020 | yyyy | 2020 |
20-Apr-2020 | yy | 20 |
20-Apr-2020 | mmm | Apr |
20-Apr-2020 | mmmm | April |
20-Apr-2020 | d | 20 |
20-Apr-2020 | ddd | Mon |
20-Apr-2020 | dddd | Monday |
20-Apr-2020 | d/mm/yyyy | 20/04/2020 |
20-Apr-2020 | dd/m/yyyy | 20/4/2020 |
20-Apr-2020 | dd mm yyyy | 20 04 2020 |
20-Apr-2020 | General | 43941 |
20-Apr-2020 | mmm ‘yy | Apr ‘20 |
A date is just a numerical number at it’s core, changing the number format to General format shows a date’s true value.
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…
Positive; Negative; Zero; Text
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 you 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) or 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 utilise 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 |
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 |
* | Repeats character immediately following it |
Just by including one of these symbols, your Custom Number Format rule will automatically use it’s 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.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
Car | turn text font colour blue | [Blue]@ | Car |
i99603 | append ‘ID’ to the text | @” - ID” | i99603 - ID |
Sparrow | prepent ‘Captain’ to the text | “Captain. “@ | Captain. Sparrow |
Comma Symbol
The comma symbol can be used to separate your numbers 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.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
1608047 | add thousands separator | #,##0 | 1,608,047 |
-1608047 | add thousands separator | #,##0 | -1,608,047 |
1608047 | show in millions (2 decimals) | 0,,.00 | 1.61 |
-1608047 | add negative () with separator | #,##0;(#,##0) | (1,608,047) |
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.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
1 | two number places | 00 | 01 |
1 | two number places | 00. | 01. |
1 | three number places | 000 | 001 |
6824 | id string | 00-000-00 | 00-0682-40 |
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.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
63.7915 | two number places | ## | 64 |
63.7915 | two number places with decimal | ##.## | 63.79 |
63.7915 | three number places | ##.### | 63.792 |
63.7915 | id string | ##.##.## | 63.79.15 |
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.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
63.7 | align to 3 decimal places | 0.??? | 63.700 |
63.79 | align to 3 decimal places | 0.??? | 63.790 |
63.791 | align to 3 decimal places | 0.??? | 63.791 |
63 | align to 3 decimal places | 0.??? | 63.000 |
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.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
1 | add a ‘W’ sized space after number | 0_W | 1W |
1 | add a ‘)’ sized space after positive numbers | 0_);(0) | 1) |
1 | add two ‘w’ spaces in front of number | _w_w0 | ww1 |
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 make 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.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
573 | prepend three * to number | \*\*\*0000 | ***0573 |
6492 | prepend @ to number | \@0 | @6492 |
26514274 | add _ in front of last 2 digits | #\_00 | 265142_74 |
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.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
1 | append ‘bps’ to number | 0” bps” | 1 bps |
2.3763 | prepend ‘$’ and append ‘M’ to number | $0.0”M” | $2.4M |
2.3 | categorize +/- numbers | “Positive”;”Negative”;”Zero” | Positive |
150.231 | prepend ‘id’ to number | “id-“0 | id-150 |
Asterisk Symbol
An asterisk symbol can be used to fill the remaining space within a cell with the character immediately following it.
Value | Appearance Needed | Format Rule | Output |
---|---|---|---|
592 | repeat period in front of number | *.0 | .....................................592 |
5.642 | repeat period in front of number | *.0.00 | ...............................592.64 |
645826 | repeat underscore in front of number | *_#,##0 | _______________645,826 |