How To Highlight Cells Above and Below Average Value

In this article, we will learn how to highlight value in a range that are above average or below average, using conditional formatting of course.

To do so, one way is to find average of those numbers and then compare each value to highlight. But we don’t need to do that hustle. Excel Conditional Formatting has two presets for average comparisons. 

1. Above Average
2. Below Average

These options are found at Home? Conditional Formatting? Top Bottom Rule.

Let’s have an quick example to understand how to use it.
Example: Highlight Age Above Average and Below Average using Excel Conditional Formatting
Here, I have some random data of some people on earth with their age. I want highlight all value that are greater than the average age with Red fill and Below Average age with Green Fill.

Now select the age range and follow these steps.

• Go to Home? Conditional Formatting? Top Bottom Rule.
• Select Above Average.
• Select Light Red Fill with Dark Red Text (it is default).
• Hit OK.

Now we have all values that are above average highlighted. Next we do it for below average.

• Go to Home? Conditional Formatting? Top Bottom Rule.
• Select Below Average.
• In Formate Cell dialog, click on drop down and select Green Fill with Dark Green Text.
• Hit OK.

And it is done. We have our above average and below average values highlighted.

You can check it if you want by calculating the average.

So yeah guys, this how you can highlight above and below average values in a set of calculable values. If you have any doubts or any specific requirement of conditional formatting, ask it in the comments section below.

Related Articles:

Highlight Duplicates Values Among Two or More Columns

IF and Conditional formatting

Conditional Formatting with formula 2016

Conditional formatting based on another cell value

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel