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.
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:
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.
To get our results, we will input the following formula:
|
1 |
=B2<=20 |
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:
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:
When we click OK, these players will be highlighted in yellow:
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:
|
1 |
=IF(OR(B2<20,B2>25),"TRUE","FALSE") |
We will get the following results:
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:
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:
