Writing Your Own Custom Number Formats in Excel

By Chris Newman •  Updated: 12/08/19 •  7 min read
Excel Custom Number Format Rules

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:

Excel Custom Number Format Rules - 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.

Number Formats Excel Add-in

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…
Excel Custom Number Format Rules

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.

Excel Format Cells Dialog Box

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.

Excel Custom Number Format Rules - @ Symbol

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.

Excel Custom Number Format Rules - Comma Symbol

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.

Excel Custom Number Format Rules - Asterisk Symbol

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.

Excel Custom Number Format Rules - Zero Symbol

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.

Excel Custom Number Format Rules - Pound Symbol

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.

Excel Custom Number Format Rules - Question Mark Symbol

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.

Excel Custom Number Format Rules - Underscore Symbol

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.

Excel Custom Number Format Rules - Backslash Symbol

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.

Excel Custom Number Format Rules - Adding Text

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.

Keep Learning

Chris Newman

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.