How to Custom Number Format in Excel with Multiple Conditions

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.

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.

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.