Conditional Number Formatting

Use conditional formatting to define a number’s color or number format, contingent upon its value. Conditions define the range of number values to which a certain format should be applied.

Contents:

Conditions are formed using brackets and their numerical ranges are defined using the symbols for the following:

• greater than (>)
• greater than or equal to (>=)
• less than (<)
• less than or equal to (<=)
• equal to (=)

Conditional rules can be inserted anywhere formatting syntax, but they may be most helpful to place before formatting rules. The following example can be read as: “For number values less than or equal to 400,000, format in the following way…”

[<=400000][backgroundcolor=CCCCCC][red]$#,#.##

Separating Conditional Rules with Semicolons

You can set multiple formatting rules that define formatting for different value ranges by using semicolons ( ; ) to separate rules. Consider the following example:

[<600000][red]$#,#.##;[=600000][yellow]$#,#.##;[>600000][green]$#,#.##

This syntax breaks down into the following rules. For all values less than 600,000: values are displayed in red font:

[<600000][red]$#,#.##;

For all values equal to 600,000: values are displayed in yellow font:

[=600000][yellow]$#,#.##;

For all values greater than 600,000: values are displayed in green font:

[>600000][green]$#,#.##

Overlapping Conditions

Consider the following example rules (line-breaks added) where conditional formatting is used to create a temperature scale effect. 

[<400000][red]$#,#.##;
[<500000][magenta]$#,#.##;
[<600000][yellow]$#,#.##;
[>=600000][green]$#,#.##

These rules are interpreted in the following sequence.

1. All values less than 400,000 are red.
2. All values greater than or equal to 400,000 and less than 500,000 are magenta.
3. All values greater than or equal to 500,000 and less than 600,000 are yellow.
4. All values greater than or equal to 600,000 are green.

Using Conditionals with Negative Numbers

Example:

[<100]#,#

The above number format (#,#) applies to all values less than 100. In this case, the value –70,000 is displayed as 70,000.

The syntax could be modified to explicitly address this issue. In this example, all values less than zero are formatted in red and preceded by a negative symbol. 

[<0][red]-#,#

You might also decide to use additional symbols, like parentheses, to denote negative data values:

[<0][red](#,#)

Using Conditionals with Null Values

By default, cells with NULL values are left blank. You can also apply conditional formatting to cells with NULL values with the [=NULL] tag. In the following example, cells of null values are displayed with gray background color and “No Value” written in red font.

[=Null][backgroundcolor=DDDDDD][red]No Value;

Example - Automatically Round Numbers

You can use conditional formatting to automatically round and truncate numbers according to their value. 

The following syntax applies different formatting rules to values greater than or equal to one billion, between one million and one billion, and between one thousand and one million, as well to negative values of each of these ranges (line breaks added). Note the order in which the formatting rules are specified.

[>=1000000000]#,,,.0 B; 
[>=1000000]#,,.0 M; 
[>=1000]#,.0 K; 
[>=0]#,##0 
[<=-1000000000]-#,,,.0 B; 
[<=-1000000]-#,,.0 M; 
[<=-1000]-#,.0 K; 
[<0]-#,##0