We have already shown the usefulness of conditional formatting and the various occasions on which it can be used.
In the example below, we will show how to use conditional formatting with the average formula.
Combining Average and Conditional Formatting
We will show above stated with the table of the best NBA scorers in the season 2021/2022 so far:
Now we want to highlight only those players in column A that have more points than the average of all the players in the table.
We can do this in two ways- we can directly input the formula in Conditional Formatting or place the formula in the sheet and then use Conditional Formatting:
- Formula directly in Conditional Formatting:
To do this, we will select range A2:A21 and then go to Home >> Conditional Formatting >> New Rule:
On a pop-up window that appears we will choose the last option- Use a formula to determine which cells to format, and we will input the following formula beneath the line: Format value where this formula is true:
|
1 |
=B2>AVERAGE($B$2:$B$21) |
We will format all of these cells in column A with green color. The pop-up window will look like this:
Once we click OK, our table will look like this:
- The formula in the sheet directly
We can also combine the IF and AVERAGE formulas on our sheet directly and then use the results that we get to highlight the players.
We will put the formula in column C, and it will be like this:
|
1 |
=IF(B2<AVERAGE($B$2:$B$21), "Less than average", "Higher than average") |
What this formula does is that it checks if the number in cell B2 is smaller than the average in the range B2:B21 (these cells are locked as the range will not change) and gives out the result “less than average” if the statement is true and “higher than average” if this statement is false.
We will drag this formula to cell C21. Our table looks like this:
Let us say that we want to find all the players that have fewer points than the average. To do this, we will select range C2:C21 and then go to Home >> Conditional Formatting >> Highlight Cells Rules >> Text that Contains:
When a pop-up appears, we will input any word from column C that is specific so that we can find players with fewer points than average. We will simply input the word “less” (it can be the lower case as well):
When we click OK, cells in column C with players that have less than average points will be highlighted in red color:
