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.

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:

  1. If the number is positive then do this…
  2. If the number is negative then do this…
  3. If the number equals zero then do this…
  4. 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

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

Leave a Comment