Al Arafat Siddique
This article illustrates the use of number format code in Excel to change the appearance of a number. The actual number doesn’t change, shown in the formula bar while changing the number format. Number formatting in Excel is a very powerful and essential feature required to display data in an understandable and meaningful way to the viewers, not affecting the calculations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
What is the Number Format Code in Excel
To format a number we can use the built-in formats like currency, percentage, accounting, date, time, etc. that Excel provides.
But sometimes it may require custom formatting to make the data more understandable. When creating a custom number format we can specify up to four sections of the format code which are for positive numbers, negative numbers, zero values, and text sequentially. Let’s see an example:
#,###.00; [Red] (#,###.00); “-”; “USD”@
| Format Code | Format Represents | Explanation |
|---|---|---|
| #,###.00 | Positive numbers | 2 decimal numbers and a thousand separator. |
| [Red] (#,###.00) | Negative numbers | 2 decimal numbers and a thousand separator enclosed in parentheses and colored red. |
| “-” | Zeros | Displays dash (-) instead of zero. |
| “USD”@ | Text | Adds USD before all texts. |
Excel Formatting Rules
- If we put only one section of the code, it’ll be applied to all numbers.
- In the case of only two sections of the code, the first section will be applied for positive and zero sections. And the second section will be used for negative numbers.
- In a number format code with three sections, these will be used for positive, negative, and zeros sequentially.
- If there is a fourth section, it’ll work on the text content, not a number.
- We need to separate all the sections of the format code with a semicolon.
- To skip a section of the number format code, we must put a semicolon there.
- To join or concatenate two values, we can use the ampersand (&) text operator.
Placeholder for Digits and Placeholders
| Code | Description |
|---|---|
| Number Sign, # | Displays only the significant numbers in a number, don’t allow non-significant zeros.
Digit Placeholder |
| Zero, 0 | Displays non-significant zeros.
Digit placeholder. |
| Question Mark,? | Adds spaces for non-significant zeros on either side of the decimal point. Although zeros don’t show up, this aligns with the decimal point.
Digit placeholder. |
| At Sign, @ | Text placeholder. |
6 Different Ways to Insert Custom Number Format
1. The context menu in Excel provides the option of Cell Formatting named as Format Cells. With the options of the Cell Formatting, we can change the format for the selected cell. We can open the context menu by simply clicking the right button of our mouse on the selected cell.
2. We can also go to the Cells section from the Home Tab. Then from the Format Tab select the Format Cells option.
3. Press Alt + H + O + E on your keyboard to make the Format Cells window opened.
4. We can also go to the Number section from the Home Tab. Then from the Number Format dropdown select the More Number Formats option.
5. Another way is to go to the Number section from the Home Tab. Then click the Format Cells: Number arrow to open the Format Cells window.
6. Select the Cell and Press Ctrl + 1 to open the Format Cells window.
Now that you have the Format Cells window opened, in the Number tab select Custom from the Category list.
In the Type input box write your number format code and then click OK.
13 Ways to Use the Excel Number Format Code
1. Use of the Excel Format Code to Display Text with Number
1.1 Text Strings
To display text with numbers enclose the text characters in double quotation marks (“ ”). For example, try out the following code that displays Positive text after positive numbers and Negative text after negative numbers
#,##0.00" Positive ";#,##0.00" Negative"
1.2 Single Character
To display a single character with a number we need to precede a single character with a backslash (\). Let’s put the following format code to put P after positive numbers and N after negative numbers.
#,##0.00 P;#,##0.00\N 
Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)
2. Add Decimal Places, Spaces, Colors, and Conditions in Excel by Using the Number Format Code
2.1 Decimal Places
In a number format code, the location of the decimal point is expressed by a period (.) while the number of decimal places required is represented by zeros (0). In the following screenshot, we showed several format codes to show the position of the decimal point and the number of decimal places after it.
Note: If we put the # sign before the decimal point in the number format code, numbers that are less than 1 will start with a decimal point like .75. On the other hand, if we put 0 before the decimal point, the formatted number will start with a zero like 0.75.
2.2 Spaces
To add spaces for non-significant zeros on either side of the decimal point we can use a question mark (?). It’ll align the decimal points when they are formatted with a fixed-width font.
2.3 Color
To specify a color for any section of the number format we can choose one of the eight colors available. The name of the color must be enclosed in square brackets. We also need to put it as the first item of the section of the number code.
The available colors are: [ Black ] [ Blue ] [ Cyan ] [ Green ] [ Magenta ] [ Red ] [ White ] [ Yellow]
Let’s see an example:
2.4 Conditions
We can apply conditions in format code which will be applied on numbers only when the condition meets. In this example, we applied red color for numbers that are equal to or less than 100 and blue color for numbers that are greater than 100.
2.5 Repeating Character Modifier
Using the Asterisk (*) sign can repeat a character. It repeats a character that is immediately after the asterisk until it fills the cell width.
2.6 Thousands Separator
The comma (,) is a placeholder used in the number format code to display a thousand separator in a number. It can also be used to indicate the behavior of digits to thousands and millions.
Read More: How to Apply Number Format in Millions with Comma in Excel (5 Ways)
2.7 Add Indents to Number
We can add a space equal to the width of a character either from the left border or the right border by using an underscore ( _ ) to the format code.
Read More: Custom Number Format: Millions with One Decimal in Excel (6 Ways)
Similar Readings
- How to Round Off Numbers in Excel (4 Easy Ways)
- Round up Decimals in Excel (4 Simple Ways)
- Excel round to nearest 10000 (5 Easiest Ways)
- How to Format a Number in Thousands K and Millions M in Excel (4 Ways)
- Add Currency Symbol in Excel (6 Ways)
3. Apply Fractions, Percentages, and Scientific Notation in Excel Using the Number Format Code
3.1 Fractions
Fractions can be displayed in different ways that can be determined by the number format code. To display a decimal as a fraction we need to include a slash (/) in the number code and a space to separate the integer part.
The predefined fraction formats align fraction numbers by the slash (/) sign. We can implement this by using the question mark (?) instead of the pound mark (#).
Read More: [Solved:] Long Numbers Are Displayed Incorrectly in Excel
3.2 Percentages
Percentages can also be displayed in different ways depending on the format code. We can show fractional percentages or even specify the number of significant digits with decimal places
Read More: Excel Number Format Not Working (2 Reasons with Solutions)
3.3 Scientific Notation
The number format code is used to display very large or small numbers in scientific notation format so that it becomes easier to read. We need to use one of the exponent codes like E+, e+, E-, e- in the number code. Any number of # or 0 after the exponent determines the number of digits in the exponent. The codes “E–” or “e–” place a minus sign (-) by negative exponents. The codes “E+” or “e+” place a minus sign (-) by negative exponents and a plus sign (+) by positive exponents.
Read More: How to Convert Number to Percentage in Excel (3 Quick Ways)
4. Use of the Number Format Code to Format Date and Times in Excel
By using the following codes, we can display dates and times in different formats as we want.
| Display | Format Code | Output |
|---|---|---|
| Years | yy | 00-99 |
| Years | yyyy | 1900-9999 |
| Months | m | 1-12 |
| Months | mm | 01-12 |
| Months | mmm | Jan-Dec |
| Months | mmmm | January-December |
| Months | mmmmm | J-D |
| Days | d | 1-31 |
| Days | dd | 01-31 |
| Days | ddd | Sun-Sat |
| Days | dddd | Sunday-Saturday |
| Hours | h | 0-23 |
| Hours | hh | 00-23 |
| Minutes | m | 0-59 |
| Minutes | mm | 00-59 |
| Seconds | s | 0-59 |
| Seconds | ss | 00-59 |
| Time | h AM/PM | 4 AM |
| Time | h:mm AM/PM | 4:36 PM |
| Time | h:mm:ss A/P | 4:36:03 PM |
| Time | h:mm:ss:00 | 4:36:03:75 PM |
| Elapsed time (hours and minutes) | [h]:mm | 1:02 |
| Elapsed time( minutes and seconds) | [mm]:ss | 62:16 |
| Elapsed time (seconds and hundredths) | [ss]:00 | 3735.80 |
Read More: Excel Custom Number Format Multiple Conditions
Notes
- In case we use ‘m’ or ‘mm’ immediately after ‘h’ or ‘hh’ or before ‘ss’ code, it will show minutes instead of months.
- If the format contains AM or PM in it, the hour will be based on a 12-hour Otherwise, the hour is based on a 24-hour clock.
Conclusion
Now, we know how to use the number code format in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- How to Apply Accounting Number Format in Excel! (Easy way)
- Remove Leading Zeros in Excel (7 Easy Ways + VBA)
- How to Round Numbers in Excel Without Formula (3 smart ways)
- Using Excel to Round to Nearest 1000 (7 Easy Ways)
- Excel Round to 2 Decimal Places (with Calculator)
- How to Change Number Format from Comma to Dot in Excel (5 Ways)
- Change International Number Format in Excel (4 Examples)
