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.

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

## 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.

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

## 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**.

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

## 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**.

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

## 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.

**Download the Practice Workbook**

Get the sample file here to practice.

## 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 of Marks in Excel
- How to Calculate Grade Percentage in Excel
- Calculate College GPA in Excel