Excel 2007 exercises: Highlight Cells | Conditional Format


Highlight Cells or Conditional Format

 Excel 2007 exercises: highlight cells with formula

1. Highlight all students’ records that have final score less than 15
Select cells:A3:F10 (without head row)
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, ...)




Comments

Larry Barnes comment

 Larry Barnes

I'm looking for new assignments for my students.


2017-02-27



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.