In this article, we are going to discuss the Excel **custom number format** with multiple conditions. We will learn about the meaning and uses of different **symbols** of the **custom number formatting**. The custom number formatting will be done for **multiple conditions** which will include **currency**, **signs**, **text**, **colors** etc.

**Table of Contents**hide

## Download Practice Workbook

Download the workbook from here.

## Create Custom Number Format in Excel

Before working with the **Custom Number Format**, we should know the process of doing it. We can do this by following the steps below:

- First of all, open the
**worksheet**and select the cells for which you want to apply the**Custom Number Format**. - Next, press the
**right button**of your**mouse**and select the**Format Cells**option.

- After selecting the
**Format Cells**option, you will get a dialogue box of**Format Cells**. - After that, click on
**Number**if it is not selected. - Now, select the
**Custom**option in the**Category**section. - Therefore, go to
**Type**and write or select any**formatting**from the different options. - Finally, press
**OK**to apply the**formatting**.

## Custom Number Format System in Excel

To understand the **custom number format**, you need to understand the sections of it. The Excel custom number format has **4** sections which are separated by **semicolons**. Each section has a different meaning. Look at the picture below with the description of them.

**Positive Number Formatting – #,##0.000:**Display**3****decimal places**with the**thousands**separator.**Negative Number Formatting – (#,##0.000):**The same formatting as the**positive**numbers with**parentheses**enclosed with it.**Formatting for zeros – “-“:**Will display a dash (**–**) instead of ‘**0**‘.**Formatting for text values – [Red]@:**Display**text**with**Red****font color**.

It is not necessary that the format should include all the **4** sections. If a **single** section is used, it will indicate the **positive number** system. For indicating the **negative number** system, you have to indicate the **positive number** system first. To apply the **General** formatting, write **General** instead of the **format code** like this, **General; -General; “General”;**

## 12 Examples of Custom Number Format in Excel with Multiple Conditions

Here, we will discuss **12 **examples of creating a **custom number format** in Excel with **multiple conditions**. For this, we have used some numbers with **general formatting** and then applied **custom formatting** operations over them. So, without further delay, let’s see the examples below.

### 1. Custom Number Format in Excel by Controlling Decimal Places with Thousands Separator for Positive Numbers

In this example, we have used a dataset (**B4:C6**) in an Excel worksheet that contains **two numbers** (**B5:B6**) with **General Format**. Here, we need to use the **Thousands Separator **in cell **C4** of the dataset below to control the **decimal places** of the numbers. See the steps below:

- Firstly, insert the numbers (with
**General formatting**) of**B5:B6**into**C5:C6**. - Then, select the range
**C5:C6**.

- Secondly,
**right-click**on the selected range. - After that, click on
**Format Cells**.

- Hence, the
**Format Cells dialog box**will pop up. - In the
**dialog box**, go to**Number.** - At this time, select
**Custom**from the**Category**options. - Go to
**Type**and keep the**cursor**in the box below it. - Therefore, to show the
**thousands number**along with**2 decimal places**, you can enter the format code**#,##0.00**in the**Type**box. - Finally, click
**OK**.

- See the final output in the screenshot below.

### 2. Control Decimal Places with Thousands Separator for Positive & Negative Numbers Using Excel Custom Number Format Feature

Now, to control **decimal** places with the **thousands separator** for both **Positive** and **Negative** numbers, we will use the same code **twice** (see cell **C4**). Here, one is for the **positive** number and the other one is for the **negative** number which will be enclosed in the **parenthesis**.

The steps for this example are almost similar to the previous one.

**Steps:**

- Input the numbers of
**B5:B7**into**C5:C7**> select the range of cells (**C5:C7**) >**right-click**on the**selected**range > select**Format Cells**>**Number**>**Custom**>**Type**>**#,##0.00;(#,##0.00)**>**OK**. - In the end, we get the output the same as the picture below.
- We can see that the
**negative number**is in the**first bracket**.

### 3. Apply Custom Number Format to Add Text String with Numbers

If you want to add **text strings** along with **numbers** in your **custom number format** you can write it in **two** ways. See them below:

- If it contains a
**single****letter**just use a**backslash**(**\**) in front of that**letter**with the**number code**. - On the other hand, if you want to use a
**text string**, place it with**double quotation marks**.

In this example, we will use a dataset (**B4:F9**) in Excel containing some **numbers **(**B5:B9**) with **General formatting**. Now, to add **text string** with **numbers**, we will use the **formatting codes** in the range **C4:F4** of the screenshot. The steps are the same as the previous methods.

**Steps:**

- In the beginning, we will change the
**formatting**of range**C5:C9**. - For this, go through the following steps:

Enter the values of **B5:B9** into **C5:C9** > select the range **C5:C9** > **right-click** on the **selected range** > **Format Cells** > **Number** > **Custom** > **Type** > **#.00,\K** > **OK**.

- Repeat these steps for the rest of the formatting (
**D4:F4**). - You just need to change the
**formatting code**in the**Type**box. - Finally, we can see the result just like the image below.

However, you can also display the **text formats with numbers** like the range **C5:C7** of the screenshot below. To do this, you can follow the above steps. You only need to replace the **formatting codes**.

- In this way, you will get the output the same as the picture below.

**Note:**Here, a

**single comma**represents

**3**digits. So, if you place

**one**comma, it represents a

**thousand**. Consequently,

**two**

**commas**represent

**millions**.

### 4. Display Leading Zeros with Decimal

If you write **0005** in Excel it will only display **5** as **Excel removes the leading zeros** **by default**. If you want to display the **leading zeros in Excel** you can easily do it by using the **Custom Format**. However, the worksheet can allow **4** **leading zeroes** at most. Let’s say, we have a dataset (**B4:C10**) in Excel that contains the **General formatting** of some **numbers** (**B5:B10**). Now, we have to display the **leading zeros** along with the **2 decimal** places of these numbers. For this, follow the steps below to do so.

**Steps:**

- To do so, go through the following steps:

**Copy (Ctrl + C)** the numbers of **B5:B10** and **Paste (Ctrl + V)** them into **C5:C10** > select **C5:C10** > **right-click **on selection > **Format Cells** > **Number** > **Custom** > **Type** > **0000.00** > **OK**.

- However, you can insert as many
**zeroes**as you can. - Thus, we will find the custom formatted numbers (
**C5:C10**) with**leading zeroes and two decimals**.

### 5. Custom Number Format by Changing Font Color

If you want different **colors** for **positive** and **negative** numbers along with the **zeroes** where the **currency** is stated you have to put the **currency** and **color** name with the **custom number code**. Assuming, we have a dataset (**B4:C8**) in Excel containing the **General Format **(**B5:B8**) of some numbers. Now, we need to **custom** the **formatting** of these numbers.

For this example, we will follow the previous steps again except the **code.** The code we will be using here is **[Green]$#,##0.00;[Red] -$#,##0.00;[Blue]”Zero”;[Magenta] @**

**Steps:**

**Copy****B5:B8**and**Paste**into**C5:C8**> select**C5:C8**>**right-click**on the selection >**Format Cells**>**Numbers**>**Custom**>**Type**>**[Green]$#,##0.00;[Red] -$#,##0.00;[Blue]”Zero”;[Magenta] @**>**OK**.- See the result in the screenshot below.

### 6. Customize Number Formatting for Security/ID/Telephone Numbers

The formatting for **Telephone Number**, **ID Number** & **Security Number **are different. If you need to insert a large number of data with these kinds of **formatting** you can easily do that with Excel **Custom Format**. Assuming, we have a dataset (**B4:E7**) that contains a **Telephone Number** (**C5**), an **ID Number** (**C6**) & a **Security Number** (**C7**). Now, we need to format these numbers as per the codes in **D5:D7**. The steps of Excel **custom number format** with multiple conditions are below.

**Steps:**

- To do so, we will follow the steps of
**example 1**till going to**Type**and keeping the**cursor**in the box of the**Format Cells dialog box**. - But for this example, we need to type the codes in the cells
**D5**,**D6**, &**D7**for the**Telephone Number**,**ID Number**&**Security Number**respectively. - Click
**OK**. - Finally, you will find the output similar to the picture below.

### 7. Include Currency Symbol Using Custom Number Format Option in Excel

We can also include **currency** **symbols** by using the symbols available in the **Format Cells dialog box**. Let’s say, we have a dataset (**B4:D7**) titled **Including Currency Symbol**. It has some numbers in **B5:B7** and also the **Currency Types** (**C5:C7**) for them. Now, we need to add the **currency symbols** for each of them as specified in **C5:C7**. See the procedure below.

**Steps:**

- In the first place, enter the value of cell
**B5**into cell**D5**. - Secondly, select cell
**D5**.

- Next,
**right-click**on the**selected**cell (**D5**). - Afterward, click on
**Format Cells**.

- Consequently, the
**Format Cells dialog box**will appear. - At this moment, go to
**Number**>**Category**>**Currency**>**Symbol**dropdown menu > select**American >**click**OK**.

- You can add the
**Currency**symbols for the rest of the numbers in the same way.

### 8. Apply Percentage Formatting for Customizing Number Formatting

Let’s assume, we have a dataset (**B4:E7**) that has some numbers under the **General** column heading. Here, we have to apply **percentage** formatting to these numbers. For this, we have used **3** types of **custom format code **(**C4:E4**).

**#%:**Shows**percentages**as**integer**values.**#.000%:**Displays**percentage**values with**3****decimal**places.**#,##.000%:**Shows**percentage**values with**3****decimal**places including**thousands separator**.

**Steps:**

- To apply this example, at first, we need to go through the steps of
**example 1**up to**keeping the cursor in the box under Type**. - After following the steps, we need to type the
**formatting codes**given in**C5:E5**for corresponding ranges. - Finally, click
**OK**.

### 9. Format Number in Excel as Fractions

We can also **format** the numbers in Excel as **fractions** by using some **codes**. To do so, we will use a dataset (**B4:F7**) containing some **General **formatted numbers (**B4:B7**) along with some formatting codes (**C4:F4**) for fractions.

**# #/#:**Shows a**fraction remaining**up to**1**digit.**# ##/##:**Displays**remainder**of**fraction**with**2**digits.**###/###:**it can create a**fraction**where the**numerator**can be**equal to or greater**than the**denominator**with**3**digits.**# #/4:**Creates a**fraction**whose**denominator**is always**4**.

**Steps:**

- Follow the steps of
**example 1**up to putting the**cursor**in the**Type**box. - Then, type the codes (
**C4:F4**) for each range. - Lastly, click
**OK**.

### 10. Create Scientific Notation by Formatting Numbers in Excel

For creating **scientific notation**, we will use the dataset (**B4:E7**) below. Here, we can see some **custom format codes** in **C4:E4** where we have used **E **for** scientific notation format.**

**0.00E+00:**It will represent**12000**as**1.20E+04**.**#0.E+000:**It will display**12000**as**1.E+004**.**#E+#:**It will show**12000**as**1E+4**.

**Steps:**

- Go to
**Type**in the**Format Cells dialog box**by following the steps in**example 1**. - After that, select the box.
- Then, insert the formatting codes (
**C4:E4**) for each range. - Finally, click the
**OK**button.

### 11. Customize Zeros as Dashes or Blanks

We can customize the **zeros** as **dashes** (**–**) or **blanks** by formatting the numbers (**B5:B7**) of the dataset (**B4:E7**) below.

**Steps:**

- Follow
**example 1**up to going to the**Type**box. - Then, enter the
**codes**(**C4:E4**) of the dataset each time. - In the end, click
**OK**.

### 12. Apply Custom Number Format Feature to Format Date & Time in Excel

Suppose, we have a dataset (**B4:D6**) in Excel where we have a **Date **in cell** C5** and a **Time** in cell **C6**. First of all, we will change the **formatting** of the **date** and then the **time**. The steps to do so are below:

- In the beginning, input the value of
**date**from cell**C5**to cell**D5**.

- Therefore, select cell
**D5**. - After that,
**right-click**on the selection. - Now, click on
**Format Cells**.

**Number**>**Category**>**Date**>**Locale**dropdown >**Type**dropdown >**14-Mar-2012**>**OK**.

- As a result, we will get the output like the screenshot below.

- The formatting of
**time**is almost the same as**date**formatting but this time we need to select**Time**from the**Category**. - The next steps are similar to
**date**formatting.

- We can see the final result in the picture below.

## Conclusion

In this article, the Excel custom number format with multiple conditions was shown. Besides these, some useful formatting like ID number Format, Custom Number Formatting with colors were demonstrated. This article will be useful where the same number formatting with the same operation is required. Hope you will like this article. Follow our website **ExcelDemy** to get more articles like this.

Good.

Thanks for your feedback.

Interesting but I have not used it before.

Thanks for early training articles to equip one at the right time.

Thanks once again

Thanks for the feedback. Hope you will find this technique useful for your next some jobs.

Best regards

Kawser Ahmed

Thanks a lot sir for simple explanation and illustrations.