The result in our transcripts is represented by the letter grades at the end of any exam. But it becomes difficult for the teachers to calculate grades one by one for each student. Therefore, excel has offered certain formulas to calculate letter grades. In this article, we will learn how to calculate letter grades in Excel in 6 simple ways.

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

Now, we will calculate the letter grades of the students.

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

In this first one, we will use** the VLOOKUP function** to get letter grades in Excel. Let’s go through the process below:

- First, change the
**Score**from range to single numbers as**VLOOKUP**calculates defining the series of numbers as a table array automatically.

- Next, insert this formula in
**cell D5**.

`=VLOOKUP(C5,$F$7:$G$11,2,TRUE)`

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

- Then, press
**Enter**. - That’s it, you can see the letter grade for the required mark.

- Following, use the
**AutoFill**tool to apply the same formula for all marks.

**Read More: **How to Make a Grade Calculator in Excel

## 2. Calculating Letter Grades with IF Function

At this stage, we will calculate the letter grades with **the IF function** in excel. Following is the step-by-step guideline:

- First, 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**.

- Now, press
**Enter**. - Finally, you can see the letter grade is visible beside the marks.

- Apply similar formula or drag the bottom corner of
**cell D5**to get the output for all the marks.

**Read More: **Calculate Grade Using IF function in Excel

## 3. Apply IFS Function in Excel to Calculate Letter Grades

**The IFS function** also helps to calculate the letter grades in Excel. Let’s see how it works.

- In the beginning, 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 the successful completion of one or more conditions. After that, it returns a value that corresponds to the first **TRUE** condition. The conditions are based on the source dataset.

- Then, press
**Enter**. - Now, you will see the letter grade based on the mark.

- Follow the similar process and calculate the letter grades of other students as well.

**Read More: **How to Make Automatic Marksheet in Excel

## 4. Insert LOOKUP Function for Letter Grades Calculation

**The LOOKUP function **is also very helpful to find the letter grades in excel. Follow the steps below:

- At first, change the range of scores to minimum numeric values of that respect range as per this image:

- Now, insert this formula in
**cell D5**.

`=LOOKUP(C5,$F$7:$F$11,$G$7:$G$11)`

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

- After this, press
**Enter**. - Lastly, use the
**AutoFill**tool in**cell range****D6:D11**to get the final output.

**Read More: **How to Make Result Sheet in Excel

## 5. Finding Letter Grades Using Excel VBA Macro

**Excel VBA macro **is a very useful technique to calculate letter grades. Let’s check the process.

- Firstly, go to the
**Developer**tab and select**Visual Basic**.

- Secondly, select
**Module**from the**Insert**section.

- After that, 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
```

- Now, click on the
**Run Sub**button or press**F5**on your keyboard.

- Therefore, click on the
**Run**in the**Macros**window.

- Finally, you will get all the letter grades at a time in
**cell range D5:D11**.

**Read More: **How to Apply Percentage Formula in Excel for Marksheet

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

In this last method, we will find grades defining pass or fail based on the marks of each student. If one gets below **60** marks, then it will be considered a **Fail**. Otherwise, it’ll be considered a **Pass**. We will use a combination of **the IF **& **AND functions** in Excel. Let’s see how it works.

- First, insert this formula in
**cell D5**.

`=IF(AND(B5,C5>=60),"Pass","Fail")`

Here, the **IF** function is used to return one value when a condition is true. It also defines another value if it’s false for the selected cell. The **AND** function tests the condition if the grade is greater than or equal to **60**. If all the conditions are correct, then we will get the result as **TRUE**. Otherwise, the result will be **FALSE**.

- Now, press
**Enter**. - There you will see the output.

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

**Read More: **Excel Formula for Pass or Fail with Color

## Things to Remember

- When you have a percentage instead of numbers, you will also need to define
**%**in the logical function to get letter grades. - Make sure to 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.

**Download Practice Workbook**

Get the sample file here to practice by yourself.

## Conclusion

I hope it was a helpful article for you on how to calculate letter grades in Excel. Let us know your feedback in the comment box.

## Related Articles

- How to Calculate Subject Wise Pass or Fail with Formula in Excel
- Calculate Grades with Weighted Percentages in Excel
- How to Calculate Percentage of Marks in Excel
- Calculate Average Percentage Increase for Marks in Excel Formula
- Calculate Average Percentage of Marks in Excel
- How to Calculate Grade Percentage in Excel
- Calculate College GPA in Excel