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.
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:
-Click Format... , select Fill tab and choose color you like then click Ok
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
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:
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:
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:
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: