Combine If Functions with Conditional Formatting in Excel

Conditional formatting might be one of the most useful reporting tools that are available. It is a great way of highlighting things that we want to show.

In the example below, we will show how to combine the IF function (integrated and not integrated) and conditional formatting.

Combining Integrated If Formulas with Conditional Formatting

For our example, we are going to use a list of NBA players and their points total from one night.

Table

Description automatically generated

We want to highlight all the players that had equal or less than 20 points, and fill the cells with their names in red color.

To do this, we will select our range (A2:A11) and then go to Home >> Conditional Formatting >> New Rule:

Graphical user interface, application

Description automatically generated

Under the options on a pop-up that appears, we will choose the last option: Use a formula to determine which cells to format.

We cannot input the IF function in a standard form in Conditional Formatting, but the last option in New Formatting Rule already defines that the values where a certain condition is met will be formatted, which is the same thing that the IF function does. We can say that the IF function is integrated in this way in conditional formatting.

Graphical user interface, text, application, email

Description automatically generated

To get our results, we will input the following formula:

=B2<=20
Graphical user interface, application, email

Description automatically generated

We will define that all of these cells are highlighted in green:

When we click OK, we will get the following results in our table:

Table

Description automatically generated with medium confidence

We can also combine AND, OR, or NOT functions with this option. For example, we will highlight all the players who scored more than 20 points and less than 25.

Since we do not have a large data set, we realize that Kyrie Irving, James Harden, and Giannis Antetokounmpo will be highlighted.

We will select our range again and do the same steps as in the first example. Our formula will be a little bit different:

Graphical user interface, text, application, email

Description automatically generated

When we click OK, these players will be highlighted in yellow:

A picture containing table

Description automatically generated

Combining If Formulas with Conditional Formatting

Since we cannot use IF formulas directly with conditional formatting, we will input the IF formula in the C column. We will define that the word “TRUE” is written for all players who scored less than 20 points and more than 25 points. Word “FALSE” will be shown otherwise.

Our formula is as follows:

=IF(OR(B2<20,B2>25),"TRUE","FALSE")

We will get the following results:

Timeline

Description automatically generated with low confidence

Now we can format column C based on these values. We will select range C2:C11 and go to Home >> Conditional Formatting >> Highlight Cells Rules >> Text that Contains:

Graphical user interface, text, application

Description automatically generated

On a pop-up window that appears, we will input the word “TRUE”. We will highlight these cells with red color:

Once we click OK, only these cells in column C will be highlighted:

Timeline

Description automatically generated with low confidence