Sheet1 contains the personal details, numbers, and grading of each student.
Sheet2 contains the mark sheet template. We inserted a bold outside border.
Step 1 – Insert Personal Details
- Insert a roll number in Cell E8. We inserted 1.
- Insert the following formula in Cell E9 to get the corresponding student name:
=VLOOKUP(E8,Sheet1!B2:L10,2)
- Hit the Enter button for the output.
- Apply a similar formula using the VLOOKUP function again in Cell G8 and G9 to return the date of birth and father’s name.
Read More: How to Calculate Letter Grades in Excel
Step 2 – Insert the Obtained Marks
- In Cell F12, use the following formula to get the obtained marks in English:
=VLOOKUP($E$8,Sheet1!$B$2:$L$10,6)
- Hit Enter.
- Repeat and modify the VLOOKUP formula to get the numbers in the other subjects. Change the column index number according to the subject name in the array of Sheet1.
Read More: How to Make a Grade Calculator in Excel
Step 3 – Apply Conditional Formatting
- Select the cells and go to Conditional Formatting, choose Highlight Cells Rules, then select Less Than.
- Insert the minimum passing number in the first box and select your desired highlight color from the second dropdown box.
- Press OK.
- The numbers are highlighted with our selected color.
Read More: How to Apply Percentage Formula in Excel for Marksheet
Step 4 – Insert Grades
- Apply the following formula in Cell G12:
=IF(F12>80,"A",IF(F12>60,"B",IF(F12>50,"C",IF(F12>35,"D","F"))))
- Hit the Enter button.
- Use the Fill Handle tool to copy the formula for the other cells.
- You will get all the grades for roll number 1.
Read More: How to Calculate Grade Percentage in Excel
Step 5 – Calculate the Total Marks
- Insert the following formula in Cell E18–
=SUM(D12:D16)
- Hit Enter.
- Find out the total passing marks and total obtained marks using the SUM formula.
Read More: Calculate Grade Using IF function in Excel
Step 6 – Calculate the Result
- Insert the following formula in Cell G19:
=G18/E18
- Hit Enter.
- To determine if the student passed or failed, apply the following formula in E20.
=IF(G19>=35%,"Passed","Fail")
- Press the Enter button.
- 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"))))
- Hit Enter.
- If we insert any roll number, the sheet will show the corresponding detailed result of the student.
Read More: How to Calculate Subject Wise Pass or Fail with Formula in Excel
Download the Practice Workbook
Related Articles
- How to Calculate Grades with Weighted Percentages in Excel
- Calculate Percentage of Marks in Excel
- Calculate Average Percentage of Marks in Excel
How to make marksheets for all roll numbers at at a click of a button in excel
Thank you, SIDDHARTH, for your wonderful question.
Here is the solution to your question. Please take a look at the below steps.
Here is our VBA code. Therefore, you can apply this code to solve your problem.
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