Have you ever found yourself facing the formidable task of assigning letter grades to a vast array of scores? Whether you’re a dedicated educator, a meticulous instructor, or simply someone in need of an efficient grading solution, you’ve landed in the right place.

In the world of data analytics, Microsoft Excel shines as a versatile tool that can simplify complex tasks. One such challenge is the assignment of letter grades, whether for students’ academic achievements or evaluating the performance of sales teams. While manually creating tables might be an option, we’re here to show you a smarter way.

In this tutorial, we’ll explore how you can harness the power of Excel to automate the process of assigning letter grades.

No need for manual, time-consuming grading—instead, you’ll discover easy-to-use formulas, VBA scripting, and Office Scripts to streamline the task. So, grab your Excel workbook filled with scores waiting to be graded, and let’s dive into the world of efficient grading solutions.

Reasons to Assign Letter Grades in Excel

Letter grading is a common practice to convert numerical scores to alphabetic grades that people easily understand and relate to.

For example, if you say you scored 80 on a match test, this might not make a good impression on the audience. However, if you say that you’ve secured a grade A on your math test, your audience can easily understand that you’re among one of the top scorers in math in your class.

Also, the letter grading system allows educational institutes or anyone else who is using it to segregate hundreds of scores into a few grades. For instance, there are 300 salespersons in your business and you need to segregate them into four performance levels, like A, B, C, and F.

Find below some reasons to use letter grading in Excel:

Letter Grading in Education

1. Excel allows educators to quickly calculate and assign letter grades to a large number of students’ scores, saving time compared to manual calculations.
2. It ensures a consistent grading system, minimizing errors and promoting fairness in grading.
3. Excel provides flexibility to customize grading criteria to suit specific courses, assignments, or grading scales.

Letter Grading in Business

1. Excel enables employers and managers to assess employee performance based on predefined grading criteria using English alphabets like A, B, C, D, etc.
2. It promotes objective and data-driven performance evaluations by assigning letter grades to various job-related metrics.
3. Excel provides a digital record of employee evaluations, which is valuable for HR documentation and future reference.

Letter Grading for Small-Scale Use

1. When you learn a skill online or do mock tests at home to prepare yourself for an upcoming competitive test, you can grade your performance using letter grades in Excel.
2. You can use this skill to track personal goals and achievements. Thereby, you help yourself stay motivated and focused on improvement.
3. You can use letter grading in personal finance as well. Assigning letter grades to budget categories in Excel can provide you with a quick overview of financial health and areas that you can improve.

Assign Letter Grades Using the IF Formula

The IF function is the easiest of all methods that enable you to grade scores with alphabets like A, B, C, etc. It’s also available in most Excel desktop and online versions. Find below the steps you need to follow:

1. Here’s how you can organize your worksheet datasets containing scores to be graded:
   • A column header called Candidates in column A
   • The column header GPA in column B
   • Another column header Grades in column C
2. Populate the rows of columns A and B with the relevant data.

3. In the first row (cell B2) below the column header Grades, copy and paste the following IF function:

=IF(ISNUMBER(B2), IF(B2>=4, "A", IF(B2>=3.7, "A-", IF(B2>=3.3, "B+", IF(B2>=3, "B", IF(B2>=2.7, "B-", IF(B2>=2.3, "C+", IF(B2>=2, "C", IF(B2>=1.7, "C-", IF(B2>=1.3, "D+", IF(B2>=1, "D", IF(B2>=0.7, "D-", "F"))))))))))), "")

4. Hit Enter to get the letter grade for the first candidate.

5. Now, drag the Auto Fill handle of the selected cell and pull it down until to the cell where there’s data available in the adjacent column B.
6. Excel will populate the letter grades for the rest of the GPA scores instantly.

If you’re using the same GPA scale and letter grading system, which is the popular one in the US, just make the following changes in the above function so it works on your own worksheet:

• Replace all the occurrences of cell reference B2 in the formula with another cell reference where you got a score to be graded.

Using the IFS Function

If you’re using the Excel desktop app 2019 or newer and the Excel for the web tool, instead of using a long nested IF function to grade scores with letters, you can use the IFS function.

Find below the IFS function you can use for the same dataset used in the IF-formula-based method earlier in this tutorial:

=IFS(B2>=4, "A", B2>=3.7, "A-", B2>=3.3, "B+", B2>=3, "B", B2>=2.7, "B-", B2>=2.3, "C+", B2>=2, "C", B2>=1.7, "C-", B2>=1.3, "D+", B2>=1, "D", B2>=0.7, "D-", B2>=0, "F")
 

Again, you need to change the cell reference in the above formula, which is B2 to use the formula in your worksheet without any errors.

For example, if the scores are in column D, you should enter D2 in place of B2 in the above function.

Use the VLOOKUP Function to Assign Letter Grades

When it comes to assigning letter grades to scores in Excel, the VLOOKUP function proves indispensable.

This function enables you to search for a specific value (e.g., a score) in a table or range and retrieve a corresponding value (a letter grade) from another column.

Therefore, you can create a separate table with score-to-grade mappings, making it easy to assign letter grades automatically. Here are the instructions you can follow:

1. Here’s how you should organize your worksheet if you wish to use VLOOKUP:
   • Create column headers like Candidates, GPA, and Grades in one table of the worksheet
   • Populate the data as needed in the above data columns
   • Create another table for the reference data of GPA and letter grades under the column headers GPA and Letter Grades
   • Again, populate the data for the above reference table as well
   • Use the Sort Smallest to Largest option in Home > Editing > Sort & Filter to sort the reference data table

2. Now, highlight the cell of the second row in column C (Grades column) and enter this formula:

=VLOOKUP(B2,$E$2:$F$13,2,TRUE)

3. Hit Enter to get the letter grade for the respective score in the cell B2.

4. Now, use the Auto Fill drag box to copy and paste the same formula across all the rows of the Grades column.

Here’s how you can modify the above formula:

• Change the cell reference for the lookup value, which is B2 according to your own dataset.
• Also, modify the look-up table address reference which is, $E$2:$F$13 and the column index 2 according to your worksheet.

Assign Letter Grades Using the SWITCH Function

The SWITCH function is a dynamic tool for grading in Excel. It excels in scenarios where you have multiple conditions and outcomes to evaluate.

Instead of nesting multiple IF functions or creating complex VLOOKUP tables, SWITCH simplifies the process.

You provide a single value or expression, and SWITCH compares it to multiple values and their respective outcomes.

Here’s how you can use the SWITCH function to grade scores with letters in Excel:

1. Select a cell where you want to populate the letter grade for the corresponding score.
2. Enter the following formula into it and hit Enter:

=SWITCH(TRUE,B2=4,"A",B2=3.7,"A-",B2=3.3,"B+",B2=3,"B",B2=2.7,"B-",B2=2.3,"C+",B2=2,"C",B2=1.7,"C-",B2=1.3,"D+",B2=1,"D",B2=0.7,"D-",B2=0,"F")

3. Excel will generate the letter grade instantly.
4. Now, use the Auto Fill handle and drag it until the last value in the column.
5. You should see the grades for the rest of the scores.

Constructing a SWITCH formula is truly easy. You need to add an expression at the beginning, which is TRUE in this case. Then, simply add the logic, like B2=4, "A", B2=3.7, "A-", and so on.

You can change the target values of B2 according to your own worksheet. For example, B2=90%, and the grade for the corresponding score will be A.

Using Power Query to Assign Letter Grades in Excel

Find below how you can use Power Query and custom formula in Power Query to assign letter grades to score in Excel:

Firstly, you need to export your worksheet data or import an external database to the Power Query Editor. To do so, simply go to Data > Get Data and choose a data connector like From Database > From SQL Server Database.

For Excel worksheet data, simply highlight the data to be exported and click Data > From Table/Range. Click OK on the dialog that shows up to export data to Power Query.

Once you’re on the Power Query Editor with the score dataset, follow these steps:

1. Bring up the Custom Column wizard by clicking Add Column > Custom Column.
2. In the New column name field, enter the column name of your choice, like Grades.
3. Inside the Custom column formula, copy and paste the following formula into the formula field:

 if [Score] >= 4.0 then "A"
    else if [Score] >= 3.7 then "A-"
    else if [Score] >= 3.3 then "B+"
    else if [Score] >= 3.0 then "B"
    else if [Score] >= 2.7 then "B-"
    else if [Score] >= 2.3 then "C+"
    else if [Score] >= 2.0 then "C"
    else if [Score] >= 1.7 then "C-"
    else if [Score] >= 1.3 then "D+"
    else if [Score] >= 1.0 then "D"
    else if [Score] >= 0.7 then "D-"
    else "F"

4. Click OK to save the newly created column.

5. You should see a new column with letter grades relative to the imported dataset.

Now, you can export the new column along with the imported dataset of scores by following these steps:

1. Click the File tab.
2. Choose the Close and Load To option in the context menu that shows up.

3. On the Import Data dialog, select the Existing worksheet option.
4. Also, select a range of cells as a destination on the worksheet.
5. Click OK to complete the exporting process.
6. Delete the existing GPA column.

That’s it! You’ve successfully assigned letter grades in Excel using Power Query.

Automatically Assign Letter Grades to Scores Using Excel VBA

Excel VBA lets you automate the whole process of letter grading in Excel. You just need to supply the input data. Find below the steps to follow:

1. Press Alt + F11 to bring up the Excel VBA Editor.
2. Click Insert and choose Module.
3. Copy and paste the following script inside the blank module:

Sub AssignLetterGrades()
    Dim ws As Worksheet
    Dim cell As Range
    Dim grade As String
    
    ' Define the worksheet where the GPA scores are located
    Set ws = ThisWorkbook.Sheets("Sheet5") ' Change "Sheet5" to your sheet name
    
    ' Loop through each cell in column B (B2 to B7)
    For Each cell In ws.Range("B2:B7")
        ' Check if the cell is not empty
        If Not IsEmpty(cell) Then
            ' Get the GPA score from the cell
            Dim gpa As Double
            gpa = cell.Value
            
            ' Assign the letter grade based on the GPA score
            Select Case gpa
                Case Is >= 4#
                    grade = "A"
                Case Is >= 3.7
                    grade = "A-"
                Case Is >= 3.3
                    grade = "B+"
                Case Is >= 3#
                    grade = "B"
                Case Is >= 2.7
                    grade = "B-"
                Case Is >= 2.3
                    grade = "C+"
                Case Is >= 2#
                    grade = "C"
                Case Is >= 1.7
                    grade = "C-"
                Case Is >= 1.3
                    grade = "D+"
                Case Is >= 1#
                    grade = "D"
                Case Else
                    grade = "F"
            End Select
            
            ' Write the letter grade back to the adjacent cell in column C
            cell.Offset(0, 1).Value = grade
        End If
    Next cell
End Sub

4. Click the Save button and close the VBA Editor.

To use the macro, follow these steps:

1. Press Alt + F8 to bring up the Macro dialog box.
2. Choose the AssignLetterGrades macro.
3. Click Run to execute the macro.

You can easily customize the VBA script by following the comments added in the script.

Use Office Scripts to Assign Letter Grades in Excel

If you need to automate letter grading tasks in Excel for the web app, you can use Office Scripts. Find below the script and steps to use it:

1. Click Automate and choose the New Script option.
2. In the Code Editor panel, copy and paste the following script:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C2 on selectedSheet
	selectedSheet.getRange("C2").setFormulaLocal("=IF(ISNUMBER(B2), IF(B2>=4, \"A\", IF(B2>=$F$2, \$E$2, IF(B2>=$F$3, \$E$3, IF(B2>=$F$4, \$E$4, IF(B2>=$F$5, \$E$5, IF(B2>=$F$6, \$E$6, IF(B2>=$F$7, \$E$7, IF(B2>=$F$8, \$E$8, IF(B2>=$F$9, \$E$9, IF(B2>=$F$10, \$E$10, IF(B2>=$F$11, \$E$11, \"F\"))))))))))), \"\")");
	// Auto fill range
	selectedSheet.getRange("C2").autoFill("C2:C7", ExcelScript.AutoFillType.fillDefault);
}

3. Click the Save script button.
4. Click the Run button to execute the script.

Here’s how you can use the script:

• Enter GPA values in the cell range E2:E13 and Letter Grades in the cell range F2:F13.
• Change the cell address B2 in the above script to another cell address according to your worksheet.
• To change the GPA values (column E) and Letter Grade (column F) references in the script, you need to modify the entries like $F$2 and $E$2 in all the nested IF functions.
• To populate letter grades in the relevant columns or cell ranges, modify the cell addresses in the code element selectedSheet.getRange("C2").autoFill("C2:C7", of the above script.

Conclusions

Now you can efficiently and programmatically grade student or work performance scores in Excel. For occasional need and small-scale use, you should rely on Excel functions like IF, IFS, VLOOKUP, and SWITCH.

When you’re importing a large dataset containing scores from a third-party database site or a different Excel file, you can use Power Query. It lets you transform scores into grades and import the transformed dataset to your desired worksheet.

Finally, for large-scale and frequent letter grading of scores in Excel, you can automate the process up to 90% by using scripting tools like Excel VBA and Office Scripts.

Did this article help? Don’t forget to comment below.

Did I miss a method? Do mention that in your comment.

Looking for another easy tutorial on an Excel topic that you want to learn? Write it in the comment box.