How to calculate grade in Excel sheet using VLOOKUP function

Teachers who are taking exams need to prepare grade sheets, If the number of students is less they can do it by manual process but when the number of students is large it became time-consuming work for them.  Microsoft Excel can make the job easier for them. If the teacher knows how to calculate grade in an Excel sheet it is a few minutes task for him. In this context, I will guide those teachers who want to prepare a grade sheet for their students.

I will use the VLOOKUP function to calculate. There are many other ways to prepare a letter grade sheet in Microsoft Excel. In future, I will share those examples as well.

To prepare the tutorial, I have prepared a dataset which I am sharing with you as a screenshot.

I will guide you step by step on how to make a grade sheet for students.

Prepare the Marks sheet

To prepare a grade sheet, you have to prepare a marks sheet first. From this marks sheet, you can calculate the grade using the VLOOKUP function in Excel. To prepare the marks sheet you can follow the below steps.

• Create a column for the serial number.
• Create another column for the student’s name.
• You can create one more column for Identity No./Roll  No. [I tried to keep the table small]
• Create the subject column as many as you have. I have added only three as an example.
• Input marks of all subjects for all students.
• If any student was absent write “Absent” on that cell.
• Create a total marks column to know his total marks.
• Use the SUM function to add all subject marks together.

To add all subject marks together you can use the following formula.

Here in the formula, SUM is the function and C4:E4 is the range of cells. Your range of cells can be different from mine. You can also add one-by-one cell to calculate the summation.

To learn the SUM function you can read this content:

How to sum up using Excel; SUM Function explained

• Create an average column to show the average marks of a student.

Do you know how to calculate the average in Microsoft Excel? The function AVERAGEA is used to calculate the average marks of a student.

I have used a Nested function here. Do you know what is a Nested function? Using a function as one of the arguments in a formula that uses a function is called a Nested function. I have used AVERAGEA as an argument for the ROUND function. So, it is a nested formula.

The ROUND function is used to round up average marks up to two decimal places. To learn about the ROUND function more you can read the below content.

How to round up number to 2 decimal places in Excel

AVERAGEA is the function that is used here to calculate the average marks. You can be surprised why I didn’t use the AVERAGE function, and why I have used the AVERAGEA function instead of the AVERAGE function. The reason behind this is quite interesting.

The AVERAGE function only counts the number value and zero from a cell or range of cells. If you type “Absent” or “Sick” the AVERAGE function will return the wrong average marks of the student. But the AVERAGEA function considers text value as zero and calculates the exact average marks of a student. You can try to calculate the average marks by using both the AVERAGE and AVERAGEA functions for better understanding.

To learn more about the AVERAGE or AVERAGEA functions you can read the below content.

The average formula in excel – AVERAGE function explained

Now this is time to calculate grade in Excel sheet using VLOOKUP function

It is better to know a little about the VLOOKUP function before we go into further discussion. It will help you to learn the VLOOKUP function to calculate the grade in Excel.

What is the VLOOKUP function?

VLOOKUP is a function that lookup for a value from a given table and returns a specific result to another table. VLOOKUP stands for vertical lookup. This function lookup values from a table in the vertical direction. So, before starting the VLOOKUP function, we can ask a few questions. Take a look at those queries.

1. Where do you want to put the value that you looked for?
2. What you are looking for?
3. From Which Table do you look for the values?
4. From Which column of the table do you look for the values?

Now answer the above questions.

1. H4, I want the result on H4.
2. I am looking for G4 values under which grade.
3. I am looking for values from table J4:K8.
4. Column 2 of table J4:K8.

So, these are my answer to those questions.

Now it is time to prepare a table of grades. The range of cells J4:K9 is the table of grades. It is the table from where the lookup value will be returned from. We named the rage as Grade.

If you are wondering how to name a range, take a look for a few seconds.

• Select your table of the range of cells.
• Type the Name in the Name Box of the Excel sheet, and press Enter.

If you give a name to any cell or range of cells any function can call that cell or range of cells by that given name. We can call the table J4:K8 by the name “Grade” in a formula instead of the range of cells J4:K8.

While creating the grade table we have to consider the following, otherwise, our VLOOKUP will not work.

Generic Formula of VLOOKUP function

The followings are the arguments for the VLOOKUP function:

• lookup_value – It means what you are looking for in a table.
• table_array – From which table you will look for the value?
• col_index_num- From which column you will extract the value?
• [range_lookup]- TRUE/FALSE, TRUE/1-approximate match and FALSE/0- exact  match.

In our case, For the first student- Ben Stokes

• lookup_value – Means what you are looking for in a table. Answer is G4.
• table_array – From which table you will look for the value? Answer is Grade.
• col_index_num- From which column you will extract the value? Answer is 2.
• [range_lookup]- TRUE/FALSE, TRUE/1-approximate match and FALSE/0- exact  match. Answer is FALSE.

Our Formula of VLOOKUP function

In our case, For the first student- Ben Stokes, Our VLOOKUP formula will be as

How it is working on Excel

VLOOKUP function looks for value G4 from the table Grade, Column 2 and data match is approximate.

Use your mouse pointer and drag to the last student to find the grades of them all. Let’s have a look what it looks like.

If you do not name a range Grade how to do it?

Here is our worksheet. we named a range J4:K8 as Grade. If we do not name that range how to calculate the grade sheet in Excel? Do you have any idea? Let’s have a look at how to do that.

You have to select a range of cells if you do not name the range of cells. But if you drag your mouse pointer or copy this formula to other cells it will not work. Because while copying or dragging a range of cells changed in Microsoft Excel. You have to fix it. For that, you can use the $ sign after and before the column number.

In this case, our range of cells should look like $J$4:$K$8, Now your table is fixed and firm for the VLOOKUP function. You can use your mouse pointer to drag the formula or copy and paste the formula for other cells.

Why should use Excel to prepare grade sheet

Microsoft Excel is the most popular worksheet in the world. The way you can calculate and prepare grade sheets for your student is very easy and simple. It takes a very few minutes to calculate. Most of the time is needed to give an entry to your datasheet. Once you prepare the data sheet it works in a minute or less to calculate the grade sheet.

Why should use VLOOKUP to prepare grade sheet

It is noted that you can prepare the same grade sheet with the help of some other functions like IF and IFS. I will describe how to calculate grade sheets with the help of IF and IFS in another content.

But remember that it is easy to prepare a grade sheet using VLOOKUP, Other function like IF is full of logic, you may not like that one.

I hope, this content is very much helpful to you as I have describes it in a very simple way, explaining all everything to you. If you still have any questions please let me know. My comment box is always open for you to give some opinions. I will try my best to reply to your comment as early as possible. Thank You.