Excel: Average Using Conditional Formatting

A little high a little low, but how do you know? A little conditional formatting can help determine who falls where.

Example: Suppose you were assigned to test employees on computer skills and you wanted to know who received above-average scores. If you listed the scores next to the employee number in range H2:H36 then this is how we do it:

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.