How to highlight above and below average values

How to highlight above and below average values with conditional formatting

In this lesson, we'll look at another conditional formatting option in the top and bottom category - the ability to highlight values that are above or below average.

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.

Like other presets in the Top and Bottom category, Excel bases its calculations on the selection used to create the rule. In this case, this means  the average is based on all scores for all three tests, which may not be meaningful.

For example, if several students missed test one, that will lower the average for all scores, and increase the number of above average scores for the other two tests.

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. Three that highlight above average scores for each test in green, and a fourth that highlights below average scores for test three in red.