How To: Use conditional formatting for a student grading report in Microsoft Excel

If you're looking for a way to create a student report, Microsoft Excel makes it easy to create a wonderful spreadsheet with tons of information. This video shows an example of a student report being created, which has three simple items in the columns of the sheet, which are the names of the students, the marks obtained and the grades.

Column A and Column B are the inputs by the user and Column C is the grade of the students that is dependent on the following four criteria:

1. If obtained marks are more than or equal to 80, and less than 90, then grade would be "A"
2. "..." 70, and less than 80, then grade would be "B"
3. "..." 60, and less than 70, then grade would be "C"
4. If obtained marks are less than 60, then grade would be "Failed"

For above purpose, put this "IF" function in C4 column. The function is:

=IF(B4>=80,"A",IF(B4>=70,"B",IF(B4>=60,"C","Failed")))

Then copy this function to all the required cells. Then, time for some conditional formatting on column C for the following criteria:

A. If marks are greater than or equal to 80, and less than 90, then background color of the cell should be green
B. "..." 70, and less than 80, then background color of the cell should be purple
C. "..." 60, and less than 70, then background color of the cell should be gray

To achieve this follow the step-by-step process:

1: Select the cells of C column (Grade) where conditional formatting is to be applied.
2: Click Format Menu.
3: Choose Conditional Formatting.. From the menu.
4: Conditional formatting dialogue box will be appeared on the screen. Under condition 1, choose Formula Is from the drop down menu and then type this formula in the text box:

=AND(IF(B4>=80,B4<90))

For the remaining 4 steps, check out the original article.