How to Compute Grades in Excel (3 Suitable Ways)

In this tutorial, I am going to show you 3 suitable ways to compute grades in excel. Although you can manually create a grade sheet, using excel will make the process very quick and easy. Also, you can compute the grades of a large number of students with the help of a few clicks. In the following section, we will see how to use the powerful functions of excel to easily compute the grades of the students.


Download Practice Workbook

You can download the practice workbook from here.


3 Suitable Ways to Compute Grades in Excel

In the dataset for this tutorial, we have the columns with the names Name, Marks, and Grades. We are using a small number of students to keep things simple. For the grades, we have 5 possible ranges. Those obtaining 90 or above will get an A grade, 80 or above B grade, and so on as we shall see in the formula. We will use the following dataset to compute grades in excel.how to compute grades in excel


1. Compute Grades Using IF Function in Excel

The IF function in excel performs a logical test and returns two different values based on the result being true or false. We will use this function to compute grades in excel different conditions.

Steps:

  • First, double-click on cell D5 and enter the following formula:
=IF(C5>=90,"A",IF(C5>=80,"B",IF(C5>=70,"C",IF(C5>=60,"D","F"))))

Compute Grades Using IF Function in Excel

  • Then, press Enter and you should see the grade in cell D5.

Compute Grades Using IF Function in Excel

  • Finally, copy the formula to the rest of the cells below using the Fill Handle.

Read More: Calculate Grade Using IF function in Excel (with Easy Steps)


2. Utilizing VLOOKUP Function

The VLOOKUP function in excel searches for data in a table organized vertically. We will use this function to look up data in the Score-Criteria table in our dataset. Note that, you should organize the Score column in an ascending manner starting from 0 and then going down to 90.

how to compute grades in excel

Steps:

  • To begin with, double-click on cell D5 and type in the below formula:
=VLOOKUP(C5,$F$5:$G$9,2,TRUE)

how to compute grades in excel

  • Now, press the Enter key and this will determine the grade of the student.

  • Then, copy the formula of cell D5 to the cells below by dragging the Fill Handle and excel will give you the grades of all the students.

Read More: How to Calculate Letter Grades in Excel (6 Simple Ways)


Similar Readings


3. Applying IFS Function in Excel to Compute Grades

We can use the IFS function to test multiple conditions without multiple IF statements. This will help us to compute grades in excel with less complexity. Let us see how to use this function.

Steps:

  • To start with, click on cell D5 and enter the following formula:
=IFS(C5>=90,"A",C5>=80,"B",C5>=70,"C",C5>=60,"D",C5<60,"F")

Applying IFS Function in Excel to Compute Grades

  • Now press Enter and the formula will calculate the grade for cell D5.

Applying IFS Function in Excel to Compute Grades

  • Lastly, copy the formula of cell D5 and fill the cells below with it using the Fill Handle.

Read More: How to Calculate Grade Percentage in Excel (3 Easy Ways)


How to Count Number of Students in Excel Having Certain Grades

To count the number of students within each range, we will use the COUNTIF function in excel. This function counts the number of cells in a range that meet specific criteria.

How to Count Number of Students in Excel Having Certain Grades

Steps:

  • First, double-click on the cell G5 and enter the below formula:
=COUNTIF($D$5:$D$10,F5)

How to Count Number of Students in Excel Having Certain Grades

  • Next, press the Enter key to confirm the formula.
  • As a result, excel will count the number of students with the grade F.

  • Then, simply drag the Fill Handle to copy the COUNTIF formula to the cells below.
  • Finally, you should get the number of students within each grade.

Read More: How to Make Result Sheet in Excel (with Easy Steps)


Things to Remember

  • Make sure that you type the logical operators like >= properly.
  • If the numbers are inserted as percentages, then we need to define percentages in the formula as well.
  • It is important to note that the IF function and the IFS function are not the same.
  • For the first method, we need to insert the same number of closing parentheses as the number of IF functions.

Conclusion

I hope that this tutorial was helpful for you to compute grades in excel. You can take the methods shown in the tutorial and use them for much larger datasets. Also, if you get stuck in any step, make sure to review the detailed methods a few times. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo