Excel Custom Number Format Multiple Conditions

In this article, we are going to discuss the Excel custom number format 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.

How to Create a Custom Number Format in Excel

Before working with the Custom Number Format, we should know the process of doing it. The process is stated below.

• In your worksheet select the cells for which you want to apply the Custom Number Format.
• After selecting the cells, 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. In this dialogue box select the Custom option and after selecting the Custom option write or select any formatting from the different options. Press OK to apply the formatting.

Excel Custom Number Format System

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.

1. Positive Number Formatting (Display 3 decimal places with the thousands separator).
2. Negative Number Formatting (The same formatting as the positive numbers with parenthesis enclosed with it).
3. Formatting for zeros (Will display a dash (-) instead of “0”).
4. Formatting for text values (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”;

Controlling Decimal Places with Thousands Separator

To show the thousands number along with 2 decimal places you can enter the format code #,##0.00 in the format cells dialogue box.

Controlling Decimal Places with Thousands Separator and Negative Numbers

The same code will be used twice. One for the positive number and the other one for the negative number which will be enclosed in the parenthesis.

Adding 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. If it contains a single letter just use a backslash (\) in front of that letter with the number code. If you want to use a text string place it with double quotation marks.

Display leading Zeros with Decimal with Excel Custom Number Format

If you write, 0005 in Excel it will only display 5 as by default Excel remove the leading zeroes. If you want to display the leading zeroes in Excel you can easily do it by using the Custom Format. Let`s say you have to display the leading zeroes along with the 2 decimal places in your Excel worksheet. The worksheet can allow 4 leading zeroes at most. You can do this by using the format-number code, 0000.00 in the custom number format. You can insert as many zeroes as you can.

Read More: Add or Keep Leading Zeros in Excel (10 Suitable Ways)

Changing Font Color with Custom Number Format

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. The code that we will be using here is [Green]$#,##0.00;[Red] -$#,##0.00;[Blue]”Zero”;[Magenta] @

Formatting for Security/ID/Telephone Numbers

The formatting for Security, ID, and telephone numbers 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.

Conclusion

In this article, the Excel Custom Number Format with multiple conditions were shown. Besides these, some useful formatting like ID number Format, Custom Number Formatting with colors were demonstrated. This article will be useful where same number formatting with the same operation is required. Hope you will like this article.

Stay tuned for more useful articles.

Further Readings

• How to Round up Decimals in Excel (4 Simple Ways)
• Excel Round to 2 Decimal Places (with Calculator)
• Excel Round to Nearest 100 (6 Quickest Ways)
• Excel round to nearest 10000 (5 Easiest Ways)
• How to Round Off Numbers in Excel (4 Easy Ways)
• How to Round to Nearest Multiple of 5 in Excel (3 Easy Ways)
• Excel Number Stored As Text [4 Smart Ways]
• Using Excel to Round to Nearest 1000 (7 Easy Ways)
• How to Round Numbers in Excel Without Formula (3 smart ways)
• How to Apply Accounting Number Format in Excel! (Easy way)