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.

 


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

Excel, Custom Formatting, 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:

Excel, Custom Formatting Syntax 1 Example 1


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:

Excel, Custom Formatting Syntax 1 Example 2


Syntax 2:

You could also use 2 conditions. If the first condition is not met then the second condition will be checked:

Excel, Custom Formatting, Condition., Syntax 2png


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:

Excel, Custom Formatting Syntax 2 Example 1

You can download the file related to this article from the link below

See also

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”

  1. David Diamond says:

    I found this page very helpful. I have an additional question. I am trying to apply the conditional formatting so that the cell will convert the number to $1.0M if in the millions and in terms of K if in the hundreds or less. That part your article explains very well. Where I am having problems is integrating your first example with the second. I would like the number to turn red if it is negative and green if positive.

    My attempt was to do it like this
    [>0][Green][>1000000]0.0,,”M”;0.0,”K”;[1000000]0.0,,”M”;0.0,”K”

    Again thanks for the really helpful article

    1. pedrumj says:

      Hi
      I’m glad you found this article helpful 🙂
      You would need to use something like this:

      [Green][>=1000000]0.0,,"M";[Green][>0]0.0,"k";[Red]0

      it has 3 parts:

      • more than 1E6
      • less than 1E6 but more than zero
      • Less than zero

      The result will look something like this:
      result
      Please let me know if you have any other questions

Leave a Reply

Your email address will not be published. Required fields are marked *