Excel Custom Formatting Conditions
Using custom formatting you can test for different conditions. You can use different formatting when different conditions are met. For example if the value in a cell is greater than 5 you can apply a custom formatting to color the font red, and if its less than 5 to color it green.
Contents
Comparison Operators:
There are 6 different comparison operators you can use:
Operator | Description |
= | Checks if the value of the cell is equal to the value on the right side of the operator |
<> | Checks if the value of the cell is different to the value on the right side of the operator |
< | Checks if the value of the cell is less than the value on the right side of the operator |
> | Checks if the value of the cell is more than the value on the right side of the operator |
<= | Checks if the value of the cell is less than or equal to the value on the right side of the operator |
>= | Checks if the value of the cell is more than or equal to the value on the right side of the operator |
Syntax 1:
There are 2 different syntaxes for using conditions in custom formatting. The first type only has 1 condition
Note the semicolon was previously used to determine whether the value in the cell is positive or negative. When using conditions in custom formats the semicolon rule is overridden. For more information about the semicolon character in custom formats please see the link below:
Syntax 1, Example 1:
In this example the custom formatting checks if the value in the cell is greater than 10. If the value is greater than 10 the postfix text string “OK” is added to the number. If the number is smaller than 10 the postfix “NG” is added to the number:
[<10]0" OK";0" NG"
The custom formatting above checks if the value of the cell is smaller than 10 or not:
[<10]
If it was smaller than 10, the formatting following the condition will apply:
0" OK"
This formatting prints the number as it is, and add the trailing text string ” OK”. If the value in the cell is not smaller than 10 the formatting following the first semicolon will apply:
0" NG"
Result:
Syntax 1, Example 2:
In this example the condition will check if the value in the cell is greater than 1,000,000 or not. If it is greater than 1,000,000 the value will be displayed as follows:
Value = 1,234,465
Value to display =1.2M
If the value in the cell is smaller than 1,000,000 the value will be displayed as follows
Value = 374,465
Value to display = 374.5k
This can be achieved using the custom formatting below:
[>1000000]0.0,,"M";0.0,"k"
The formatting above uses the conditional statement below to check if the value in the cell is greater than 1,000,000 or not:
[>1000000]
If the value in the cell is greater than 1,000,000 the formatting below will apply. The formatting below divides the number by 1,000,000 displays it using one decimal place and adds an “M” to end of the number:
0.0,,"M"
If the value is smaller than 1,000,000 the formatting below will apply. The formatting below divides the number by 1000, displays it using one decimal place and adds a “k” to the end of the number:
0.0,"k"
Result:
Syntax 2:
You could also use 2 conditions. If the first condition is not met then the second condition will be checked:
Syntax 2, Example 1:
In this example if the value in the cell is greater than 2000, the font color will be changed to red. If its smaller than 100 it will be changed to blue. The custom formatting used can be seen below:
[>2000][Red]0;[<100][Blue]0;0
Condition 1:
[>2000]
Formatting if True:
[Red]0
Condition 2:
[<100]
Formatting if true:
[Blue]0
Formatting if both conditions fail:
0
Result:
You can download the file related to this article from the link below
See also
- Excel, Custom Formatting Semicolon Character
- Excel, Custom Formats
- Excel Custom Formatting, 0, # and ? characters
- Excel, Custom Formatting Colors
- Excel Custom Formatting Adding Characters and Text
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
2 thoughts on “Excel Custom Formatting Conditions”