Use conditional formatting to identify above average scores in Excel

Conditional formatting can help you get the information you
need from data. For example, suppose you would like to know how many students
in your class received an above-average score on a recent exam. You’ve listed
their exam scores next to their names in the range L2:L24. To identify the
above-average students in your class, follow these steps:

1. Select L2:L24.
2. Go
   to Format: Conditional Formatting.
3. Click
   the drop-down arrow and select Formula Is.
4. Enter
   the following formula:
   =L2>Average($L$2:$L$24)
5. Click
   the Format button.
6. Select
   Red in the drop-down Color menu.
7. Click
   OK twice.

All above-average scores will be red.

Note: When entering the
formula in step 4, ensure that the cell references in the Average function are
absolute.)

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.