Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Create an Excel Custom Number Format – Excelchat

There are various built-in formats for numbers like currency, accounting, date, time, percentage, etc. A custom number format is a special code of how we want to display the numbers in cells without changing the actual numeric values. We can create a custom format of numbers to display insignificant zeros, significant digits, aligned decimal, thousands separator, currency symbol, etc.

Figure 1. Custom Number Format

Custom Number Format

Excel has many built-in custom number formatting codes that we can apply or create our own custom format code to display the numbers. Optionally, we can also customize the built-in custom number codes that are somewhat similar or nearer to our desired formatting code and save it as a copy of that format. The original or built-in custom codes cannot be changed or deleted. To apply or create a custom format do the followings;

  • Select the cell(s), right-click and select Format Cells option.

Figure 2. Applying Cell Formatting Option

  • On Number tab of Format Cells window, click on Custom from the category list.

Figure 3. Custom Formats

  • Select any built-in custom format or enter the custom number format code in the box below the Type field and press the OK button.

Figure 4. Entering Custom Formats

What is Custom Number Format Code

The custom number format has a particular structure, consisting of up to four sections of code, where each section is separated by a semicolon in the following pattern;

POSITIVE; NEGATIVE; ZERO; TEXT

Each section of code represents the desired custom format rule but it is not compulsory to include all the four sections. Below is an example of a custom format code, where positive and negative values are displayed with currency symbol ($) and thousands separator (,), also the negative value has Red font with brackets ( ). Zero value is formatted in the Black font but displayed as a dash “-”. The text value is displayed in the Blue font.

$ #,##0; [Red]($ #,##0); [Black]"-"; [Blue]@

Figure 5. Custom Formats Codes

Rules For Custom Number Format

There are some rules to understand regarding the custom number format code and its sections;

  • The custom format changes only the view of the value stored, but it does not change the value itself.
  • The custom code may contain at least 1 or up to 4 sections. If it contains only 1 section then the same rule will be applicable to positive, negative and zero values. If it contains 2 sections then the first section is applied on positive and zero values and the second section on negative values

Figure 6. Custom Format Code -2 Sections

  • The custom format applies to text values if the code contains the fourth section.
  • To keep the default number format for any of the sections, just type General in the specific section
  • If we want to hide certain value, like zero, then we skip the corresponding section and insert the ending semicolon for that section.

Figure 7. Custom Format Code Rules

Digit and Text Placeholders

We use several digits and text placeholders in the format code section(s) to formulate a specific custom number format. The list of these placeholders along with the explanation is given below;

 Displaying Decimal Places and Insignificant Zeros

With the help of Zero (0) character, we can show the insignificant number of zeros and display the number of decimal places by adding the custom number format as follows;

Figure 8. Controlling Decimal Places

Displaying Thousands Separator

In Excel custom number format, we can show the thousands separator using Comma (,), Zero and Pound (#) symbols with and without decimal places as follows;

Figure 9. Showing Thousands Separator

Displaying Negative Numbers in Parenthesis and Red Font

As we know there are 4 sections of custom number format i.e.

POSITIVE; NEGATIVE; ZERO; TEXT

Therefore, we can show the negative numbers in parenthesis and along with Red font color as a custom number format in the 2nd section of code as follows;

Figure 10. Showing Negative Custom Number

Displaying Zero as Dash

Similarly, we can display zeros as dashes by using the dash “-” in the 3rd section of the code. We use the following codes in this example;

Figure 11. Displaying Zero as Dash

Repeating Character in Custom Format

We can fill the extra space in the cells by repeating characters using the Asterisk symbol (*) in custom number format as follows;

Figure 12. Repeating Character in Custom Format

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc