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.**

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

** **

**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.**

**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**‘s**s’**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.