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

Microsoft Excel provides a number of fruitful & easy methods to calculate grade percentage. Here I’m going to show you the techniques with proper illustrations through which you’ll be able to calculate grade percentages from a set of specific data & then assign them into text strings based on some fixed criteria.


Download Practice Workbook

You can download our workbook to practice yourself which we’ve used to prepare this article. You can also use this workbook as a calculator in Sheet2 where you have to input data only according to your requirements & you’ll get the results immediately.


3 Suitable Methods to Calculate Grade Percentage in Excel

1. Calculate Letter Grade and Percentage for Each Subject Separately in Excel

Suppose, a student has obtained marks in a final examination of 5 subjects. Now, here’s a dataset below which represents a student’s obtained marks against total marks in all subjects.

In the bottom chart, there’s a letter grading system mentioned under all ranges of marks percentages. We have to find two things-

  • Grade Percentages for All Subjects
  • Letter Grades for All Subjects

Calculate Letter Grade and Percentage for Each Subject Separately in Excel

Step 1:

⇒ Select Cell D2.

⇒ Type =B2/C2 & press Enter.

It’ll divide marks obtained by total marks in math.

You’ll get a decimal value which you need to convert into a percentage.

⇒ Under the Home tab select the ‘%’(Percentile) icon from the Number group of commands.

Calculate Letter Grade and Percentage for Each Subject Separately in Excel

So, you’ve just determined the grade percentage for math.

Step 2:

⇒ Now point your mouse cursor onto the right bottom corner of Cell D2.

You’ll notice a ‘+’ icon there which is known as Fill Handle.

⇒ Select this Fill Handle, drag it to Cell D6 & release the mouse button.

Calculate Letter Grade and Percentage for Each Subject Separately in Excel

Now, you’ll get grade percentages for all subjects.

Calculate Letter Grade and Percentage for Each Subject Separately in Excel

Let’s move to the 2nd part now. We have to find Letter Grade for each subject now.

Step 3:

⇒ Tap on Cell E2 & type =VLOOKUP(D2,$C$9:$D$15,2,TRUE)

We’re using the VLOOKUP function here through which we’re able to find a range of specific information from an array for a particular data or cell.

First of all, we need to find out the letter grade for math only.

So in the Function Bar & inside the parenthesis,

D2 denotes that you’re selecting grade percentages for math which needs to be looked up in the grading system chart array.

$C$9 : $D$15 indicates the array where grade percentages as well as related letter grades are inscribed.

Here you have to lock the whole array by using the ‘$’ symbol before each Row Number & Column Name. This is called Absolute Cell References & unless you lock the cell references here, the calculation will not return to this specific array every time in the lookup process & error messages as well as misinterpreted results will be shown for some data. You can have a detailed idea of Absolute Cell Reference here.

‘2’ denotes the 2nd column in that array which needs to be printed as letter grade for a specific range of percentages.

And last of all, TRUE means the approximate match you’re going to find, otherwise the particular grade percentage obtained in a subject will not be included inside the particular percentage range if an exact match is not gained.

⇒ Press Enter & you’ll find the letter grade for math.

Calculate Letter Grade and Percentage for Each Subject Separately in Excel

Step 4:

⇒ In Cell E2, use Fill Handle again to fill down to Cell E6.

The letter grades for all subjects will be shown right away.


2. Calculate Average Grade Percentage and Average Letter Grade in Excel

Now let’s determine the average grade percentage & average letter grade for all subjects.

Step 1:

⇒ Add two extra columns named Average Grade Percentage & Average Letter Grade to the previous data set.

Calculate Average Grade Percentage and Average Letter Grade in Excel

Step 2:

⇒ Select Cell F2 & type =AVERAGE(D2:D6) & press Enter.

You’ll get the average grade percentage there.

Calculate Average Grade Percentage and Average Letter Grade in Excel

Step 3:

⇒ Now select Cell G2 & type =VLOOKUP(F2, C9:D15, 2, TRUE)

Here, we’re using the VLOOKUP function once again to find the letter grade assigned to the average grade percentage.

⇒ Press Enter & you’ll get the Average Letter Grade at Cell G2.

Related: Calculate Average Percentage in Excel [Free Template+Calculator]


3. Insert Nested IF Formula to Find Out Grade Percentage in Excel

We can gain similar results by using the Nested IF formula too if the VLOOKUP function seems a bit difficult to you. So here are the steps to use the nested IF function to find Letter Grades after finding out the Grade Percentages.

Step 1:

⇒ Select Cell E2.

⇒ Type =IF(D2<40%, $D$9, IF(D2<50%, $D$10, IF(D2<60%, $D$11, IF(D2<70%, $D$12, IF(D2<80%, $D$13, IF(D2<90%, $D$14, $D$15))))))

⇒ Press Enter

You’ve just determined the Letter Grade for math in Cell E2.

So what’s happening here exactly?

We’re using the Nested IF function to add multiple conditions to meet our criteria.

If the value in Cell D2 does not meet the first condition then it’ll waver around all conditions until it meets the exact criteria. Once this process fulfills the condition for D2, the fixed Letter Grade from the cells (D9:D15) will be assigned to it.

Nested IF Formula to Find Out Grade Percentage in Excel

Step 2:

Now use the Fill Handle in Cell E2 to fill down to E6 & you’ll get the expected results at once.


Conclusion

These are some of the most effective methods to calculate Grade Percentages and then convert them to Letter Grades in Excel I’ve found. I hope, this article has helped you to guide with proper instructions. If you have any questions, thoughts, or feedback you can comment here. You can also have a look at other useful articles related to Excel on our website.


Further Readings

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo