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.

**Table of Contents**hide

## 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

ExcelDemyThe 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