It is a very frequent need for educational institutions to make progress reports for the students. It would be a lot easier if you can make a report card in Excel, as Excel has a lot of formulas and features. You can easily make a standard assessment report card and copy it through for every student. It will save your time too. Here, in this article, I will show you how to make a report card in Excel.
What Is a Marksheet and a Report Card?
A mark sheet is a document that lists all of the student’s grades in each subject. In other words, schools utilize the mark sheet to enter and record academic assessments such as grades, remarks, and so on. You can also include demographic information about students in mark sheets, such as their age, address, etc., so that they can use the report card as a reference.
Each student’s academic success is summarized in a report card. In other words, it is each student’s assessment report that shows his total achievement on any given assessment exam.
Essential Parts of a Report Card
A school report card contains student information, subject-specific marks, subject-specific grades, and class-specific grades. It shows the overall percentage, grade, and rank of the student.
1. Student Information
This section includes student information like student IDs, names, classes, sections, institution information, term information, grading system, etc.
2. Mark Sheet
In this section, a term-wise mark sheet for every subject and every student is present. Here, basically, the ids and names of the students and marks obtained in each subject are present.
3. Term-Wise Report Card
In this section, we have a term-wise report card of an individual student. Every student’s all terms’ individual report cards are presented here briefly.
4. Cumulative Report Card
This section includes the cumulative report card of an individual student. This is the summary of the student’s every term’s mark sheet.
How to Make a Report Card in Excel (With Easy Steps)
In our practice workbook, we have made a report card in Excel for 10 students in class IX. We have prepared 4 worksheets in our dataset for the four essential parts of a report card. We have also used the VLOOKUP function for automating the students’ names and sections according to their student IDs rather than writing individual names and sections each time. Besides, you can use the SUM function to calculate the total marks obtained by a student. Moreover, you can use the IF function to easily get the grades of the students according to the grading system of your institution.
Steps to Make a Report Card in Excel
Follow the step-by-step guidelines below to make a report card in Excel. 👇
📌 Step 1: Create a Basic Information Sheet
For preparing a report card in Excel, it is very helpful to record student information, institution information, term information, grading system, etc. So, first, prepare a sheet containing students’ IDs, names, classes, sections, institution’s name, location, principal, class teacher’s name, grading system, etc.
📌 Step 2: Create Term-Wise Students’ Mark Sheet
Now, prepare a term-wise mark sheet for every subject and for every student. Here, basically, record the IDs and names of the students and marks obtained in each subject at each term.
Now, from the marks, you need to record the grades of the students in each subject too. This is tiring for putting grades individually. Here, you can automate the grading of your report card using the nested IFs. write the following formula in your grade cell. 👇
=IF(E8>=90,"O",IF(E8>=80,"A+",IF(E8>=70,"A",IF(E8>=60,"B+",IF(E8>=50,"B",IF(E8>=40,"C","F"))))))
🔎 How Does the Formula Work?
- Here, the first IF function will check if Cell E8 is greater than or equal to 90, If it is True it will return O otherwise it will go to the next IF function.
- The next IF function will check if Cell E8 is greater than or equal to 80 it will return A+ or else it will move on to the next IF function.
- This pattern is continued through each subsequent IF statement, checking if the value in Cell E8 is greater than or equal to 70, 60, 50, and 40 and returns A, B+, B, and C respectively.
- If none of the conditions is True, then it will return F.
Here, as there are other grades between 80 to 0, another IF would come accordingly. Subsequently, this would continue until the grading system is fulfilled.
- Now, you can drag the fill handle and copy the formula for other subjects as well.
Thus, the result will look like this. 👇
📌 Step 3: Create Term-Wise Report Card
Now, the main step to making a report card in Excel is to create a term-wise report card for each student from the second step. Follow the steps below carefully for making this term-wise report card. 👇
Steps:
- Record the basic pieces of information, such as the student’s ID, name, section, attendance, term number, etc., at the top of the report card. Here, you can automate the student’s name and section from the Basic Information sheet. This means you don’t need to write the name or section repeatedly for each student. But you can use the VLOOKUP function and write only the student ID. Excel will automatically find the name and section of the students according to the student ID from the Basic Information sheet.
- To automate the name of the student according to the student ID, click on the E7 cell where you have to write the name of the student. Now write the following formula:
=VLOOKUP(C7,'Basic Information'!B5:D14,2)
🔎 How Does the Formula Work?
- In the VLOOKUP function, we inserted Cell C7 as loopup_value, cell range B5:D14 of Basic Information named worksheet as table_array, and 2 as column_index_num to find the value of Cell C7 from the given cell range and return data from the 2nd column of that range.
And, the result will look like this. 👇
- Subsequently, after automating the student’s name, you can also automate the student’s class and section number using the same formula and following the same process. But, here at the col_index_num argument, put 3, as the class and the section is in the third column of our selected range.
- So, your top part of the report card is prepared. Now, from the Term Wise Mark Sheet, record the marks of the student for the following term in each subject.
- Now, you have to put the marks obtained by each student again here which is boring and tiring. You can put the mark here by just referencing your all students’ Term-Wise Mark Sheet here. For doing this, click on the cell of the Report Card where you want to record the mark of the student. Then, put an equal sign(=) which represents the beginning of a formula. Now, click on the cell from the Term-Wise Mark Sheet where the mark of the student of that subject was recorded. Press the Enter button.
Following this process, you can fill up all the marks obtained cells by referencing them from the Term-Wise Mark Sheet properly.
- Now, you need to record the grade of the students in each subject too. This is tiring for putting grades individually. Here, you can automate your grading using nested IFs as the step 2 procedures shown above.
- At this time, you need to put remarks according to the grades. you can use the VLOOKUP function again for your remarks rather than writing them individually in each case.
- Here, your lookup value will be the achieved grade of the student. In the Basic Information sheet, there is a column for Remarks according to their Grades. So, select the range of the grades and remarks starting the selection from the grades column. Furthermore, put the column index number in the formula according to your selection. And, write FALSE which means Exact match at the last argument. As, the column values are not numbers but text values, an exact match declaration would make the formula more reliable and correct.
Note:
Here, put the dollar sign ($) to make the table array an absolute reference. Here, when you drag the fill handle below, the formula will be applied to all the cells below. So, absolute referencing is a must here, else your table array will be dragged down too as you drag down the formula. Thus, you will get the wrong results. You can press the F4 key to make absolute cell referencing.
- Moreover, you can find the total marks obtained using the SUM function. For using this function, write =SUM(). Here, the equal sign(=) represents a formula being written. The SUM function is the function that sums the referred values. So, it requires the argument of cell reference which requires to be summed. So, here, put the cell reference of all the marks obtained in each subject. You can reference the cells by just selecting all the cells.
-  At this time, you need to calculate the overall percentage of the student’s obtained marks. You can use the Division functionality of Excel in this regard. At first, click on the cell where you want to put your overall percentage. Then, put an equal sign(=) and divide the total marks obtained by the total marks of the subjects. It will give you a ratio of the student’s overall marks on a 0 to 1 scale. Now, multiply it by 100 to get the result in 100 scales. Thus, you will get the overall percentage of the student.
- Subsequently, according to this overall percentage, you can get the overall grade of the student following the grading system. You can use the nested IF conditions in this regard just like used in step 2. So, follow that process on the overall percentage score and get the overall grade.
📌 Step 4: Create Cumulative Report Card
Now, create the cumulative report card for each student. In this report card, all term mark sheets will be combined into a single report card. Here, you can also use the VLOOKUP function to put the name, class, and section for each student on the top of the report card. And, use the SUM function to sum the obtained marks and use nested IFÂ to calculate the grade too.
Things to Remember
- When using the VLOOKUP function, remember that this is a vertical lookup process. So, you can only look up your values through the columns. You can not look up your value through the horizontal rows.
- Another thing, you should remember is that, when selecting the table array, keep the lookup value column as the first column in your selection. And, the return value column index number will be put according to this serial.
- If you lookup numbered values, the range_lookup argument is not so important. But, if you lookup text values, it is very advisable to put the range_lookup argument as FALSE, if you want an exact match always.
Download Report Card Template
Here we have attached the Report Card template. You can download it for free.
Conclusion
So, here I have described the step-by-step guidelines to make report cards in Excel. You can follow these easy steps to accomplish your targets in this regard. I hope you find this article a great help. If you have any further queries or recommendations, please feel free to contact me.
I have found this article very helpful,but it could have been nice if you had created a pdf file for such information for us to read properly.
Hi, Elton!
Thanks for your appreciation. We will try to launch pdf files.
Regards
ExcelDemy
The article is wonderful but please elaborate more on the functions used for proper understanding. Thanks
Dear Elly,
Thanks for your appreciation. We re-explained our formulas to make them more understandable. If you find any difficulty now, let us know in the comment section below.
Regards
ExcelDemy