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