Custom formats that conditionally format entries

You can use Excel's number format codes to create custom number formats that do conditional formatting. To create a conditional format, you set up the condition inside square brackets using the same comparative operators (=, >, <, >=, <=, and <>) as you do in simple formulas.

For example, you can create a conditional custom format that displays a cent sign (¢) after all values that are less than a dollar and displays a normal dollar sign and two decimal places for all values over or equal to one dollar. To do so, open the Number tab of the Format Cells dialog box, select the Custom number category on the Number tab, and enter the following code in the Type text box:

In this conditional format, when the number in the cell is less than 1, the program adds the cent sign (¢) to the decimal number showing two places. Otherwise, when the number is 1 or any number greater, the program uses the second part of the conditional format (after the semicolon), which adds the dollar sign ($) and two decimal places to the number.

When creating conditional custom formats, you're not limited to two formats: the one used when the condition is true and the other when it's false. You can, in fact, create up to three formats: one format used when the first condition is true, a second format when the second is true, and a third format for all other cases.

For example, say that you want to create a conditional format that applies the General number format using a red font when a number is less than 1, blue when its value is greater than 100, and default black when its value is anywhere in between. To do so, open the Format Cells dialog box, select the Custom number category on the Number tab, and enter the following number codes in the Type text box:

In this conditional custom number format, the font color red is assigned to any values below 1, and blue is assigned to any values higher than 100. The default, black color is assigned to all other values (that is, any number between 1 and 100).