Excel 2007 exercises: Highlight Cells (advanced)


Highlight Cells (advanced)

In this exercise, you are about to create a simple program in Excel sheet. The program provides  a user-friendly interface (left to the sample data) . The interface shows the tasks that your program provides to the user. The tasks can be selecting (highlight) student by sex, a students' name started with a specified letter, total score, max or min score, rank, and a students' name contained a specified text. The sample data are provided as shown in the picture below. It is about a score sheet of 4 students.


 Excel 2007 exercises: highlight cells with if and left rank

1. Highlight students by sex


After the user inputs a letter of sex(M or F) in to cell I3 and press ENTER, the students who are in the specified sex group will be highlighted.

-Select cells:A2:G5 (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:
=$B2=$I$3
-Click Format... , select Fill tab and choose color you like then click Ok

 Excel 2007 exercises: highlight cells by sex
 

2. Highlight students whose names started with a letter

To highlight students whose names starting with the specified letter, you will need to use the Left function to take out one letter from the every name. Then compare the letter with cell I5.
The formula to highlight the records is
=LEFT($A2,1)=$I$5

 Excel 2007 exercises: highlight cells by name starting with a letter


3. Highlight students by a specified range of total scores

After the user inputs start score in cell I9 and end score in cells J9 and press ENTER, the program will highlight students whose total scores fall in that range.
The formula for this point is:
=AND($G2>=$I$9,$G2<=$J$9)

 Excel 2007 exercises: highlight cells by range of total score


4. Highlight student who gets the max or min total score

To highlight the student getting the highest ore lowest score, the user needs to type the word "Max" or "Min" in cell J12. We will use the If formula to check whether "Max" or "Min" is input. The formula to get this thing done is:
=IF($J$12="Max",SUM($C2:$F2)=MAX($G$2:$G$5),IF($J$12="Min",
SUM($C2:$F2)=MIN($G$2:$G$5),FALSE))

 Excel 2007 exercises: highlight cells by max or min total score

5. Highlight a student by rank (number 1, number 2,...)

The user can choose to highlight a student by his/her ranking number. We will use the Rank function to rank the total scores. The max score is ranked as number 1. The formula that is used in this point is:
=RANK($G2,$G$2:$G$5)=$I$16
 

 Excel 2007 exercises: highlight cells by rank



6. Highlight students whose names contain a specified text

Students whose names contain a specified text (e.g ea) can be highlighted by the use of Find, Len, Trim, and And functions. The formula used in this problem is:
=AND(FIND($I$19,TRIM($A2))<=LEN($A2),FIND($I$19,TRIM($A2))>=1)

 Excel 2007 exercises: highlight cells by text that contain




Comments




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.