# How to Calculate Letter Grades in Excel (6 Simple Ways)

Here is an annual grade sheet for 7 students in an institution, with names and marks. We also have grades based on the range of scores.

## Method 1 – Using the VLOOKUP Function to Calculate Letter Grades in Excel

Steps:

• Change the Score from a range to single numbers as VLOOKUP calculates, automatically defining the series of numbers as a table array.

• Insert this formula in cell D5:
`=VLOOKUP(C5,\$F\$7:\$G\$11,2,TRUE)`

Here, C5 defines the student score we want to calculate for the letter grade. Cell range F7:G11 is the table from which the lookup value will be returned. 2 is the column number in the lookup table to return the matched value. TRUE is for an approximate match.

• Press Enter.

You can see the letter grade for the required mark.

• Use the AutoFill tool to apply the same formula for all marks.

## Method 2 – Calculating Letter Grades with the IF Function

Steps:

• Insert this formula in cell D5:
`=IF(C5>=90,"A",IF(C5>=80,"B",IF(C5>=70,"C",IF(C5>=60,"D","F"))))`

Here, the IF function is applied to return one value based on the condition given for the selected cell, defining whether it is true or false. Therefore, if the student gets more than 90, the letter grade will be A, and chronologically marks less than 60 will get F.

• Press Enter.

You can see the letter grade is visible beside the marks.

• Drag the bottom corner of cell D5 to get the output for all the marks.

## Method 3 – Applying the IFS Function in Excel to Calculate Letter Grades

Steps:

• Insert this formula in cell D5:
`=IFS(C5>=90,"A",C5>=80,"B",C5>=70,"C",C5>=60,"D",C5<60,"F")`

Here, the IFS function checks whether one or more conditions have been successfully completed. Then, it returns a value that corresponds to the first TRUE condition. The conditions are based on the source dataset.

• Press Enter.

You will see the letter grade based on the mark.

• Drag the bottom corner of cell D5 to get the output for all the marks.

Read More: How to Make Automatic Marksheet in Excel

## Method 4 – Inserting the LOOKUP Function for Letter Grades Calculation

Steps:

• Change the range of scores to minimum numeric values of that respect range as per this image:

• Insert this formula in cell D5:
`=LOOKUP(C5,\$F\$7:\$F\$11,\$G\$7:\$G\$11)`

Here, C5 defines the student score we want to calculate for the letter grade. Cell range F7:F11 is the reference from which the lookup value will be returned. Lastly, G7:G11 is the source cell range of scores.

• Press Enter.
• Use the AutoFill tool in cell range D6:D11 to get the final output.

Read More: How to Make Result Sheet in Excel

## Method 5 – Finding Letter Grades Using Excel VBA Macro

Steps:

• Go to the Developer tab and select Visual Basic.

• Select Module from the Insert section.

• Insert this code on the blank page:
``````Sub InsertLetterGrade()

Dim i As Integer
Dim rslt As String
For i = 5 To 11
If (Cells(i, 3) >= 90) Then
result = "A"

ElseIf (Cells(i, 3) >= 80) Then
result = "B"

ElseIf (Cells(i, 3) >= 70) Then
result = "C"

ElseIf (Cells(i, 3) >= 60) Then
result = "D"

Else
result = "F"
End If
Cells(i, 4).Value = result
Next i
End Sub``````

• Click the Run Sub button or press F5.

• Click the Run in the Macros window.

You will get all the letter grades at a time in cell range D5:D11.

## Method 6 – Combining IF and AND Functions to Calculate Letter Grades in Excel

Steps:

• Insert this formula in cell D5:
`=IF(AND(B5,C5>=60),"Pass","Fail")`

Here, the IF function returns one value when a condition is true and defines another value if it’s false for the selected cell. The AND function tests the condition if the grade exceeds or exceeds 60. If all the conditions are correct, we will get the TRUE result. Otherwise, the result will be FALSE.

• Press Enter.

There you will see the output.

• Apply the AutoFill tool to get results in cell range D6:D11.

## Things to Remember

• When you have a percentage instead of numbers, you must define % in the logical function to get letter grades.
• Lock the selected range before dragging the formula to other cells.
• In the case of calculating the grades for a situation where the less number means a higher grade, then use “<” instead of “>” as the operator.

Get the sample file here to practice.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF