How to Make Report Card in Excel (Download Free Template)

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.

Download Report Card Template

Here we have attached the Report Card template. You can download it for free.

What Is a Marksheet and a Report Card?

A mark sheet is a document that lists all of the students’ 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 the demographic information about students in mark sheets, such as their age, address, etc. so that they can use the report card as a reference.

And, 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, class, section, 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

In our practice workbook, we have made a report card in Excel for 10 students of 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 obtained marks by a student. Moreover, you can use the IF function to get the grades of the students easily according to the grading system of your institution.

Read More: How to Create a Summary Report in Excel (2 Easy Methods)

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, class, section, institution’s name, location, principal, class teacher’s name, grading system, etc.

Read More: How to Make Daily Activity Report in Excel (5 Easy Examples)

📌 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 grade 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. 👇

Now, you can drag the fill handle and copy the formula for other subjects as well.

Thus, the result will look like this. 👇

Read More: How to Generate Reports Using Macros in Excel (with Easy Steps)

Similar Readings

• How to Create an Expense Report in Excel (With Easy Steps)
• Create an Income and Expense Report in Excel (3 Examples)
• How to Generate Report in PDF Format Using Excel VBA (3 Quick Tricks)
• Make Production Report in Excel (2 Common Variants)
• How to Make Daily Production Report in Excel (Download Free Template)

📌 Step 3: Create Term-Wise Report Card

Now, the main step to make 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, the 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.
• For automating 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:

• Subsequently, after automating the student’s name, you can also automate the student’s class and section number using the same formula 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.

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

Read More: How to Generate Reports from Excel Data (2 Easy Methods)

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

Read More: How to Automate Excel Reports Using Macros (3 Easy Ways)

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.

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. And, visit ExcelDemy for many more articles like this.

Related Articles

• How to Make Daily Sales Report in Excel (with Quick Steps)
• Make Monthly Report in Excel (with Quick Steps)
• Create a Report That Displays Quarterly Sales in Excel (with Easy Steps)
• How to Make MIS Report in Excel for Sales (with Easy Steps)
• Make Inventory Aging Report in Excel (Step by Step Guidelines)
• How to Generate PDF Reports from Excel Data (4 Easy Methods)
• Prepare MIS Report in Excel (2 Suitable Examples)