How to Use Number Format Code in Excel (13 Ways)

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.

excel number format code

2. We can also go to the Cells section from the Home Tab. Then from the Format Tab select the Format Cells option.

excel number format code

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.

excel number format code

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.

excel number format code

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"

excel number format code


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

excel number format code single character

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.

excel number format code decimal places

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:

excel number format code add color


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.

excel number format codes repeating character


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.


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.

excel number format code add indent

Read More: Custom Number Format: Millions with One Decimal in Excel (6 Ways)


Similar Readings:


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.

excel number format codes fractions to decimal

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 (#).

excel number format code


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


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.

excel number format codes scientific notation

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

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo