How to Make Automatic Marksheet in Excel (with Easy Steps)

Making mark sheets in Excel is quite easier and smarter because we can use functions here to avoid repetitive tasks and apply conditional formatting to highlight specific results. Just by changing the roll number, we can get all the information at once. So, in this tutorial, we are giant to show the procedures to make an automatic marksheet in Excel with some easy steps and clear illustrations.

Easy Steps to Make Automatic Marksheet in Excel

First, get introduced to our dataset which has two sheets. Sheet1 contains the personal details, numbers, and grading of each student.

And Sheet2 contains the mark sheet template. We inserted a bold outside border to create a better look for the mark sheet.

Step 1: Insert Personal Details

The first part of our marksheet contains the personal details of a student. We’ll insert the roll, class, and session manually and the other information will be returned using the VLOOKUP function.

Steps:

Firstly, insert a roll number in Cell E8. I inserted 1.
Then insert the following formula in Cell E9 to get the corresponding student name-

=VLOOKUP(E8,Sheet1!B2:L10,2)

Next, hit the ENTER button for the output.

After that, apply the same type of formula using the VLOOKUP function again in Cell G8 and G9 to return the date of birth and father’s name. The formula is quite similar to the previous one, just the column index number of the array will be different according to the date of birth and father’s name.

Then you will get the personal details part of the mark sheet like the image below.

Step 2: Insert Obtained Marks

Now we’ll extract the obtained marks in every subject for every student using the VLOOKUP function again to make an automatic marksheet in Excel.

Steps:

In Cell F12, type the following formula to get the obtained marks in English-

=VLOOKUP($E$8,Sheet1!$B$2:$L$10,6)

Later, press the ENTER button for the output.

Next, use the same type of VLOOKUP formula to get the numbers in the other subjects. Just we’ll have to change the column index number according to the subject name in the array of sheet 1.

Here are all of our obtained numbers for roll number 1.

Step 3: Apply Conditional Formatting

At this moment, we’ll apply conditional formatting to the obtained marks to highlight if the numbers return to the failed range.

Steps:

Select the cells and click as follows: Home > Conditional Formatting > Highlight Cells Rules > Less Than.

Insert the minimum passing number in the first box and select your desired highlight color from the second dropdown box.
Finally, just press OK.

Now see, the numbers are highlighted with our selected color.

Step 4: Insert Subjectwise Grade

The next step is to create the grade for every subject in our automatic mark sheet. Here we’ll use the IF function.

Steps:

Apply the following formula in Cell G12–

=IF(F12>80,"A",IF(F12>60,"B",IF(F12>50,"C",IF(F12>35,"D","F"))))

Then just hit the ENTER button.

Next, use the Fill Handle tool to copy the formula for the other cells.

Soon you will get all the grades for roll number 1.

Step 5: Calculate Total Marks

In this section, we’ll calculate the total marks, total passing marks, and total obtained marks using the SUM function.

Steps:

To get the total marks of all subjects, insert the following formula in Cell E18–

=SUM(D12:D16)

Next, press the ENTER button just.

After that, find out the total passing marks, and total obtained marks using the same type of formula.

Step 6: Calculate Result

We reached our final step to create an automatic marksheet in Excel- calculating the final result of a student. We’ll calculate the obtained percentage, result as remark, and final grade.

Steps:

First, we’ll calculate the percentage. So insert the following formula in Cell G19–

=G18/E18

Later, just hit the ENTER button.

To get the result as the student passed or failed, apply the following formula using the IF function-

=IF(G19>=35%,"Passed","Fail")

And press the ENTER button then.

Finally, to calculate the grade, use the following formula in Cell G20–

=IF(G19>80%,"A",IF(G19>60%,"B",IF(G19>50%,"C",IF(G19>35%,"D","F"))))

Press the ENTER button to finish.

The automatic mark sheet is created successfully and completely ready. Now if we just insert any roll number, it will show the corresponding detailed result of the student. I inserted roll number 3 and see, it created the mark sheet automatically for the corresponding student-

Things to Remember

Make sure you have closed the brackets for the IF
Make sure you have used double quotes (” “) while specifying any text in the functions like “Passed,” “Fail,” etc.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Automatic Marksheet.xlsx

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to make an automatic mark sheet in Excel. Feel free to ask any question in the comment section and please give me feedback.

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Siddharth Pande Apr 1, 2023 at 6:49 PM

How to make marksheets for all roll numbers at at a click of a button in excel
- Reply
  Bishawajit Chakraborty Apr 3, 2023 at 12:16 PM
  Thank you, SIDDHARTH, for your wonderful question.
  
  Here is the solution to your question. Please take a look at the below steps.
  - Create a table with columns for Roll Number, Name, and Marks in Excel.
  - Enter the Roll Number, Name, and Marks for each student in the table.
  - Create a new sheet in Excel and name it “Marksheets”.
  - In cell A1 of the “Marksheets” sheet, enter the text “Roll Number”.
  - In cell B1, enter the text “Name”.
  - In cell C1, enter the text “Marks”.
  - Select cell A2 on the “Marksheets” sheet.
  - Go to the “Data” tab in the Excel ribbon and click on “Data Validation”.
  - In the “Data Validation” window, select “List” as the validation criteria.
  - In the “Source” field, enter the range of Roll Numbers from the table you created earlier.
  - Click “OK” to close the “Data Validation” window.
  - Repeat steps for cells B2 and C2, but select the range of Names and Marks respectively.
  - Now, select the Roll Number, Name, and Marks here.
  Here is our VBA code. Therefore, you can apply this code to solve your problem.
```
Sub Generate_Marksheet()
Dim roll_number As String
Dim name As String
Dim marks As Integer
Dim i As Integer
i = 2 'Starting at row 2 of the Marksheets sheet
Do While Worksheets("Marksheets").Cells(i, 1) <> "" 'Loop until the Roll Number column is empty
roll_number = Worksheets("Marksheets").Cells(i, 1).Value
name = Worksheets("Marksheets").Cells(i, 2).Value
marks = Worksheets("Marksheets").Cells(i, 3).Value
'Creating a new sheet for each Roll Number
Worksheets.Add(After:=Worksheets(Worksheets.Count)).name = roll_number
'Adding the Name and Marks to the sheet
Worksheets(roll_number).Range("A1").Value = "Name"
Worksheets(roll_number).Range("B1").Value = "Marks"
Worksheets(roll_number).Range("A2").Value = name
Worksheets(roll_number).Range("B2").Value = marks
i = i + 1 'Moving to the next row
Loop
End Sub
```
  Here, you will see the final result in another sheet with names and marks.
  
  I hope this may solve your issue.
  
  Bishawajit, on behalf of ExcelDemy

How to Make Automatic Marksheet in Excel (with Easy Steps)

Easy Steps to Make Automatic Marksheet in Excel

Step 1: Insert Personal Details

Step 2: Insert Obtained Marks

Step 3: Apply Conditional Formatting

Step 4: Insert Subjectwise Grade

Step 5: Calculate Total Marks

Step 6: Calculate Result

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply