Highlight Cells Above (or Below) Average

Excel Formulas › Conditional Formatting

All versionsAVERAGE

Draw the eye to the standouts. A formula rule using AVERAGE shades every value above the group’s mean — flip the comparison to catch the laggards instead.

Quick formula: select B2:B20, then add a formula CF rule:

=B2 > AVERAGE($B$2:$B$20)

The current cell is compared to the average of the whole locked range. Above-average cells get the format.

Functions used (tap for the full reference guide):

AVERAGE

The example

Sales by rep; the average is 30, so above-average reps are flagged.

	A	B
1	Rep	Sales
2	Ann	42
3	Bo	18
4	Cy	35
5	Di	25

The formula

The conditional-formatting rule:

=B2 > AVERAGE($B$2:$B$20) // avg 30 → Ann(42) & Cy(35) highlight

How it works

The rule compares each cell to the live average:

Select B2:B20 and add a formula rule: =B2 > AVERAGE($B$2:$B$20).
The test cell B2 is relative (walks down); the range inside AVERAGE is locked so every row compares to the same mean.
Any cell above the average returns TRUE and gets the fill. Because AVERAGE recalculates, the highlights update as data changes.
For below-average, just reverse it: =B2 < AVERAGE($B$2:$B$20).

Built-in version: Conditional Formatting → Top/Bottom Rules → Above Average does this without a formula. The formula version wins when you want a custom benchmark — e.g. compare to a target in a cell: =B2 > $E$1.

Try it: interactive demo

Live demo

Edit the numbers; above-average cells highlight.

Average:

Variations

Below average

Catch the laggards:

=B2 < AVERAGE($B$2:$B$20)

Above a target cell

Compare to a benchmark in E1:

=B2 > $E$1

Within 10% of average

Highlight the “typical” band:

=ABS(B2-AVERAGE($B$2:$B$20)) <= 0.1*AVERAGE($B$2:$B$20)

Pitfalls & errors

Lock the AVERAGE range. If $B$2:$B$20 isn’t absolute, the range drifts row by row and the benchmark is wrong.

Blanks count as zero? AVERAGE ignores truly blank cells but counts cells containing 0, which can drag the mean down. Clean the data if blanks are really “no value.”

Ties at the average. > excludes cells exactly equal to the mean; use >= if you want to include them.

Practice workbook

📊

Download the free Highlight Cells Above (or Below) Average practice workbook

A live above/below-average sheet with the target-cell and within-band rules applied, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I highlight cells above average in Excel?

Select the range and add a formula rule =B2 > AVERAGE($B$2:$B$20). Lock the AVERAGE range with $ so every cell compares to the same mean.

How do I highlight below-average cells?

Reverse the comparison: =B2 < AVERAGE($B$2:$B$20).

Can I compare to a custom target instead of the average?

Yes — point at a cell: =B2 > $E$1 highlights everything above the target you type in E1.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Highlight with a formula · Highlight top N · Average by group

Function references: AVERAGE