How to highlight above and below average values

Let’s take a look.

Here we have a table that contains three test scores for a group of students.

Let’s use Conditional Formatting to quickly highlight values that are above or below average.

There are two presets in the Top and bottom category under Conditional Formatting: Above average and Below average.

Let’s choose Above average, and use the green preset for the format.

Excel now highlights all test scores that are above average.

Let’s delete the rule we created and create three new Above average rules, one for each test.

We now have three independent rules.

Each rule can be edited to change the condition and format.

We could for example, change a rule from above average to below average.

Instead, let’s add one more rule to highlight below average values for test three.

First, select all scores for test three.

Then, choose the Below average preset from the Top and Bottom category.

We’ll use the red preset for format.

Now we have four rules total.