How to Make a Marksheet in Excel

A marksheet in Excel lists each student with their subject marks, then calculates the total (SUM), percentage, grade (IF/IFS), result (pass/fail) and rank (RANK). Once the formulas are in the first row, you copy them down and the whole class is graded automatically. This project builds a complete, reusable marksheet from a blank sheet, using formulas you've already met.

Set up the columns

In row 1, type these headings:

Roll No, Name, English, Maths, Science, Total, Percentage, Grade, Result, Rank.

Enter roll numbers, names and the three subject marks for a few students. Leave the calculated columns blank for now.

Step 1: Total marks

Total is the sum of the three subjects. In the Total cell (say F2):

=SUM(C2:E2)

Step 2: Percentage

If each subject is out of 100, the total is out of 300. Percentage in G2:

=F2/300*100

Format it to one decimal, or add a % — but if you use the % format, divide by 300 only: =F2/300 then apply percent format.

Step 3: Grade with IF (or IFS)

Give a grade based on percentage. In H2, using nested IF:

=IF(G2>=75,"A",IF(G2>=60,"B",IF(G2>=33,"C","Fail")))

Or, in Excel 2019/2021/365, use IFS:

=IFS(G2>=75,"A", G2>=60,"B", G2>=33,"C", TRUE,"Fail")

Step 4: Result (pass/fail)

A student passes only if they clear 33 in every subject. In I2:

=IF(AND(C2>=33,D2>=33,E2>=33),"Pass","Fail")

This is stricter than the overall percentage, since one failed subject fails the student.

Step 5: Rank

Rank orders students by total marks. In J2:

=RANK(F2,$F$2:$F$20)

Note the $ signs: the range is locked so it stays the same as you copy down. The highest total gets rank 1.

Step 6: Copy the formulas down

Select F2:J2, then drag the fill handle down to cover all students. Because the subject references are relative and the RANK range is absolute, every row calculates correctly.

Worked example

Karan's overall percentage is 42.7 (grade C), but he failed because he scored below 33 in Maths. That's why the Result formula checks each subject.

Make it look professional

• Bold and shade the heading row.
• Centre the marks and grades.
• Add All Borders to the table.
• Use conditional formatting to colour "Fail" red.

Pro tips

• Lock the RANK range with F4 ($F$2:$F$20) before copying, or ranks come out wrong.
• Keep pass mark and max marks in their own cells and reference them, so you can reuse the sheet for different exams.
• Use IFS instead of nested IF if your Excel supports it; it's easier to read.

Common mistakes

• Forgetting to lock the RANK range. Without $, the range slides and ranks break.
• Grading on percentage but ignoring subject fails. A student can have a passing percentage yet fail a subject. Check each subject for the Result.
• Percentage over 100. If you divide by the wrong maximum, percentages go wrong. Divide by total possible marks.

Key takeaways

• Total with SUM, percentage with a division, grade with IF/IFS, result with IF+AND, rank with RANK.
• Lock the RANK range with absolute references before copying.
• Result should check every subject, not just the percentage.
• Format the sheet and colour fails for a professional look.

Practice task

Build a marksheet for five students across three subjects. Add total, percentage, grade, result and rank using the formulas above. Then colour any "Fail" result red with conditional formatting.

Practise real projects like this with a trainer in the ADCA program at HCI.