﻿ Excel 2007 exercises: Highlight Cells | Conditional Format

Excel 2007 exercises: Highlight Cells | Conditional Format

Highlight Cells or Conditional Format

1. Highlight all students’ records that have final score less than 15
Click on Home->Conditional Format->Manage Rules...
Click New rule... button
Select Use a formula to determine which cells to format and write the formula in the box under "Format values where this formula is true:
=\$E3<15
Click Format... , select Fill tab and choose color you like then click Ok

2. Highlight all students’ records that have final scores more than 30 and less than 40
You will do as in point 1. The difference is the formula.
=AND(\$E3>30,\$E3<40)
3. Highlight all students’ records that have final scores=20
=\$E3=20
4. Highlight all students’ records who do not have assignment scores
=\$C3=0
5. Highlight students’ records who do not have mid-term score
=\$D3=0
6. Highlight all female students
=\$B3="F"
7. Highlight all students’ records who get the highest score
In cell F3, write the formula: =SUM(C3:E3) to calculate the total score for every student. To highlight the records, write the formula:
=\$F3=MAX(\$F\$3:\$F\$10)
8. Highlight all students’ records who get the lowest score
=\$F3=MIN(\$F\$3:\$F\$10)
9. Highlight all students’ records who are female and have final score >20
=AND(\$B3="F",\$E3>20)

Syntax:
MAX(number1,number2,...)
MIN(number1,number2,...)
AND(logical1,logical2, ...)