Excel Custom Format for Numbers

Excel Custom Format Dialog Box

The Excel custom format of numbers allow us to create different types of format. Those are adapt to our needs. When none of the preset formats is suitable, the best option is to create a custom format.

Create Excel Custom Formats

Many Excel users, even some who have been using the tool for years, avoid creating custom formats because they believe it is a very complicated task. Actually, Excel custom formats of numbers look more complex than they really are.



The easiest way to create a custom format is by pressing the key combination Ctrl + 1 to display the Format Cells dialog box and make sure that we are in the Number section. That is when we select the Custom category. (More information about Excel Formatting)

Excel Custom Format Dialog Box

An Excel custom format is composed of a series of codes which are specified in the Type text box so that the value of a cell adopts the new format.

Parts of an Excel custom format

A custom format allows us to specify 4 codes within the same text string: positive values, negative values, zero values and text. Each code must be separated by a semicolon. Consider the following example of a custom format:

[Green]General;[Red]General;[Black]General;[Blue]General

Custom Format Colors Classification

First, note that the 4 codes are separated by a semicolon. The first code indicates that the positive values will be green. The second code tells us that the negative values will be red. The third code will make the values that are zero have a black color and the last code causes all the cells that are text type should be blue. See how this custom format behaves when applied to cells with different values:

Custom Color Format Results

The word “General” that appears within the custom format that we have just created means that the number will be displayed in a standard format and we will only apply one color to it. It is important to note that the example shown makes use of the color names which we can use in our custom formats.

Ignore a code in the Excel custom format

Although in the example shown I specified the 4 codes, it is not always necessary to do so. We can omit some of the codes of the custom format taking into consideration the following:

  • If we only specify a single code then it will be applied for all values (positive, negative, zero and text
  • For two codes, the first one will apply for positive values, zeros and text. The second code will be for negative values.
  • Finally, If we specify three codes, the first will be for the positive values and the text. The second code for the negative values and the third code for the zeros.

See the result of applying a custom format with three sections only:

[Green]General;[Red]General;[Black]General

Excel Custom Color Format - 3 Codes

When you create an Excel custom format  we often make several attempts until you reach the format you want. Every time we edit the text string of the custom format, Excel adds it to the list as a new available format. So when you have achieved the customized format that you need, be sure to eliminate those formats that were only tests and that you will not need again. (Excel Total)

Be the first to comment

Do you want to said something? Leave a Reply