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.

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.

Siam Hasan Khan

Hello!

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.