How to Apply Custom Number Format With Multiple Conditions in Excel

Customizing number format involves using one or multiple conditions that enable users to format cells dynamically. For example, you can make positive numbers appear in one color and negative numbers in another or format values differently depending on their size or length in an Excel cell.

In this Excel tutorial article, you will explore 5 examples to apply Excel custom number format with multiple conditions. You will learn about the meaning and uses of different custom format codes of the custom number formatting.

The following overview GIF shows that the custom number format is being changed based on multiple conditions. Here, depending on the values you enter in the cell, the Delta or Inverted Delta symbols will be added at the end.

What Custom Number Format Does Excel Offer?

To understand the custom number format, you need to understand its sections. The Excel custom number format has 4 sections separated by semicolons. Each section has a different meaning. Look at the following list of examples for a 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.
• Formatting for zeros: "-"
  // It will display a dash (-) instead of ‘0’.
• Formatting for text values: [Red]@
  // Display text with Red font color.

Here’s an example of a complete custom number format code:

#,##0.000; (#,##0.000); "-"; [Red]@

The custom format code above displays numbers with commas and three decimals, negative numbers in parentheses, zero as “-“, and displays text in red.

A custom format doesn’t need to include all the 4 sections. If a single section is used, it will indicate the positive number system. To indicate 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”;

How to Apply Custom Number Format With Multiple Conditions: 5 Examples

Here, you will learn five examples of applying custom number formats with multiple conditions, such as: Displaying Positive, Negative, and Zero; Inserting Delta and Inverted Delta Symbols; Formatting Thousands, Millions, and Billions; Changing Font Color; Displaying Emoji Based on multiple conditions.

Displaying Positive, Negative, and Zero

Like the following image, suppose you have some numbers, and the right side column shows an expected format for these numbers. For example, in our case, these formatting will follow some conditions:

• Positive numbers ≥ 500 appear as “K”.
• Positive numbers < 500 show as “0 K”.
• Negative numbers ≤ -500 are displayed in “(K)” format.
• Negative numbers > -500 are shown as “(0 K)”.

To display custom formatted Positive, Negative, and Zero with multiple conditions:

1. Select your desired cell.
2. Press Ctrl+1 to open the Format Cells dialog box.
3. In the Format Cells dialog box:
   • Go to the Number tab.
   • Click on Custom from the Category section.
   • Write the following format code in the Type field: #,##0, "K";(#,##0, "K")
   • Hit OK.

   

As a result, you will see an output applying a custom number format with multiple conditions like the following GIF.

Displaying Dash as Zero:

You can also display 0 as Dash (-) when applying the custom number format like the following image. For this, you have to extend your formatting code and conditions to display a Dash.

To apply this, you have to use the formatting code: #,##0, "K";(#,##0, "K");"-"

Thus, you will see the intended output like the following.

Inserting Delta and Inverted Delta Symbols

Assume you want a custom number format for increasing or decreasing units with Delta and Inverted Delta symbols like the following screenshot. So, the idea here will maintain the following conditions:

• Positive numbers are marked with a Delta (▲) symbol.
• Zero is presented as the number 0.
• Negative numbers feature an Inverted Delta (▼) symbol.

To Insert Delta and Inverted Delta Symbols based on multiple conditions:

1. Select an empty cell.
2. Press Ctrl+1 to open the Format Cells dialog box.
3. In the Format Cells dialog box:
   • Go to the Number tab.
   • Click on Custom from the Category section.
   • Write the following format code in the Type field: 0" ▲"; -0" ▼";0
   • Hit OK.

   

As a result, you will see an output displaying delta and inverted delta based on conditions like the following GIF.

Formatting Thousands, Millions and Billions

When working with large numbers, you can display numbers in billion, million, and thousand formations. The idea can be done using a custom number format like the following image. However, you must apply several conditions to the formatting code. Here are the conditions:

• ≥ 1 billion: “B” notation.
• ≥ 1 million: “M” notation.
• ≥ 1 thousand: “K” notation.
• < 1 thousand: “K” notation with one-tenth (0.1) formation.

To format numbers as Thousands, Millions, and Billions notations:

1. Select an empty cell.
2. Press Ctrl+1 to open the Format Cells dialog box.
3. In the Format Cells dialog box:
   • Go to the Number tab.
   • Click on Custom from Category.
   • Write the following format code in the Type field: [>999999999] $#,##0.0,,,"B";[>999999] $#,##0.0,,"M";$#,##0.0,"K"
   • Hit OK.

   

Now, insert multiple large numbers and see an output like GIF below.

Displaying Numbers Less Than 1000 as General Format:

Assume you are in a situation where you do not want to display the “K” notation for values less than one thousand, like the screenshot below.

Unfortunately, only using a custom number format to perform such a task is very difficult. However, you can apply multiple conditional formattings in a cell so that if the values are less than one thousand, it will format like regular Currency. Follow these:

1. In an empty cell, first, apply the currency format.
2. Now, apply the following three conditional formatting and three format codes in the cell:
   • Formula 1: =$E$5>=1000000000
   • Format Code 1: $#.00,,," B"
   • Formula 2: =$E$5>=1000000
   • Format Code 2: $#.00,,"M"
   • Formula 3: =$E$5>=1000
   • Format Code 3: $#.00,,"K"

It is important to remember that you must prioritize the conditional formatting rules from the highest to the lower order, which means the formula for billions will be at the top, later comes the millions formula, and lastly, for thousands.

Finally, you will see an output like the GIF below.

Read More: How to Format a Number in Thousands K and Millions M in Excel

Changing Font Color With Multiple Conditions

You can customize a number format code to change font color based on several conditions. Assume you want to use the green font for positive numbers and the red font for negative numbers, and if the cell value is text, the font color will be purple, like in the following screenshot.

To change font color using a custom number format:

1. Select your desired cell.
2. Press Ctrl+1 to open the Format Cells dialog box.
3. In the Format Cells dialog box:
   • Go to the Number tab.
   • Click on Custom from the Category.
   • Write the following format code in the Type field: [Green]$#,##0.00;[Red] -$#,##0.00;[Blue]"Zero";[Magenta] @
   • Hit OK.

   

Finally, you will get an output like the following GIF.

Read More: How to Apply Number Format in Millions with Comma in Excel

Displaying Emoji With Multiple Conditions

You can insert various emojis with the help of a custom number format based on multiple conditions. Assume you have a dataset like the following image where you want to format student marks, including some thumb emojis at the end.

In this case, you have to create a custom format keeping the following conditions in mind:

• Marks less than 50: An “👎” emoji will be included at the end to indicate a lower performance.
• Marks between 50 and 79: An “👌” emoji will be included to indicate an acceptable performance.
• Marks equal to or greater than 80: An “👏” emoji will be included to indicate an excellent performance.

Note: To insert an emoji in Windows OS, press the Windows+. or Windows+; keys. It is important to note that these emojis will be displayed as greyscale on the Excel Desktop. However, you will find the emojis with their colored glory in Excel Online.

To insert emojis based on multiple conditions using custom number format:

1. Select an empty cell.
2. Press Ctrl+1 to open the Format Cells dialog box.
3. In the Format Cells dialog box:
   • Go to the Number tab.
   • Click on Custom from the Category section.
   • Write the following format code in the Type field: [>80]0 "👏";[>50]0 "👌";0 "👎"
     You can modify the formatting code for other conditions. For example, insert the desired condition within the square bracket [> 60].
   • Hit OK.

   

Finally, you will see the intended emoji will be displayed and get an output like the following GIF.

Read More: How to Add Number with Text in Excel Cell with Custom Format

Download Practice Workbook

Conclusion

So, in this article, I have shown various aspects of applying a custom number format with multiple conditions in Excel. You have learned about several practical examples: Displaying Positive, Negative, and Zero; Inserting Delta and Inverted Delta Symbols; Formatting Thousands, Millions, and Billions; Changing Font Color; and Displaying emojis based on multiple conditions. Hopefully, all the ideas shared here will help you. Do not hesitate to leave comments if you have any questions.

Frequently Asked Questions

How do I create a custom number format for displaying numbers in millions with comma separators?

To display numbers in millions with comma separators, you can use the custom number format: #,##0,,”M”

Apply this format to the cells containing the numbers. Excel will automatically format them in millions with commas.

Can I share custom number formats with others or across different Excel files?

Custom number formats in Excel are specific to each workbook. You can manually recreate the formatting in other workbooks or use Excel templates with the desired custom formats to share them.

How do I remove custom number formatting from a cell or range of cells?

To remove custom number formatting from a cell or range of cells, select the cells and press Ctrl+1 to open the Format Cells dialog box; next, select the General category and click OK. This will revert the formatting to the default General format.

Related Articles

• Excel Custom Number Format – Millions with One Decimal
• How to Apply Custom Format Cells in Excel
• How to Format Number to Millions in Excel
• How to Add Text after Number with Custom Format in Excel

<< Go Back to Custom Number Format | Number Format | Learn Excel