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.
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.
📌 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. 👇
🔎 How Does the Formula Work?
The equal sign(=) represents the beginning of a formula. Writing IF enables the IF function and now we need to input 3 arguments.
Now, the first argument is the logical test. Here, the logical test is if the mark obtained in this subject which is in the E8 cell is greater than 90.
The second argument is the value returned when the condition is true. From the grading system of the institution, we know if a student obtains greater than 90, the grade would be “O”. So the value returned when true is written as “O”. As this is a text value, the value should be inside the double quotation mark(“ “).
The third argument is the value returned when the condition is false. Now, here, many more grades can come in if you get below 90. So, you can not put a single value as your returned value when the logical test is false. So, you have to use nested IFs to conclude the other cases following your grading system.
After checking the cell value as if greater than 90, and if it comes as false, then the third argument comes in action. And, the third argument is acting here as another IF statement.
From breaking it down, we can see, that if the value is not greater than 90 but greater than 80, then it would return “A+” as the second if’s returned value is “A+” when the logical test is true. And, if the value is not greater than 90 and also not greater than 80, then it would go to the next returned value when the logical test is false.
Here, as there are so many 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. 👇
- 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. 👇
- 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:
🔎 How Does the Formula Work?
The equal sign(=) represents the beginning of a formula. Writing VLOOKUP enables the VLOOKUP function and now 4 arguments are needed.
We want to find the name of the student according to their ids. So, we have to look for student ids first. So, select the C7 cell as the lookup value. At this time, put a comma.
- =VLOOKUP(C7,’Basic Information’!B5:D14,
Subsequently, choose the table array or the defined range. We will find the student’s name from the Basic Information sheet. So, select the entire range of the students’ information which is B5:D14 from the Basic Information sheet. Just select the cells and the range will be fixed. Put a comma now.
- =VLOOKUP(C7,’Basic Information’!B5:D14,2)
At this time, write the column index number at which you want to return the value for your lookup value. As the students’ names are on the 2nd column of our selected range, we put 2 here. And as a result, it will give us the 2nd column’s value for every lookup value.
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 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.
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.
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.
- 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)