Marksheet in Excel

Marksheet in Excel

If you are a teacher or professor, Microsoft Excel can be useful for tracking your students’ grades and performance. A marksheet in Excel is a dynamic document that automatically calculates students’ scores, grades, and pass/fail status.

To create a marksheet in Excel, you only need a list of your student’s names, roll numbers, and marks in each subject. Then, you can use Excel functions like SUM, IF, AND RANK, and VLOOKUP to automate the process of calculating total marks, grades, and pass/fail status.

The best part is that you only need to create one marksheet format in Excel. Once you have it in place, you can change the student’s roll number and check the report of any student.

Excel functions, formula, charts, formatting creating excel dashboard & others

Let’s see how you can create a marksheet in Excel with formulas and simplify your grading process.

You can download this Marksheet in Excel Template here – Marksheet in Excel Template

How to Create a Marksheet in Excel?

Here’s a step-by-step guide to help you create a basic student marksheet in Excel:

  1. Enter Student Details in Excel
  2. Calculate Obtained Marks Using the SUM Function
  3. Determine Percentage
  4. Assign Academic Grades with the IF Function
  5. Rank Students (1 to 10) Using the RANK Function
  6. Calculate Results (Pass or Fail) with IF and AND Functions
  7. Determine Status with Nested IF Statements
  8. Improve the Table Presentation with Formatting

Below is a detailed explanation of how to create a student marksheet in Excel with all the steps

1. Enter Student Details in Excel

Open Excel and create a new sheet (Sheet1). Label columns with information such as:

  1. Roll No
  2. Grade
  3. Name
  4. Fathers Name
  5. Mothers Name
  6. Date of birth
  7. Subject Marks
  8. Total Marks

Marksheet in Excel - Enter Student Details

2. Calculate Obtained Marks Using the SUM Function

Marksheet in Excel- Calculate Obtained Marks Using the SUM Function

Calculate Obtained Marks Using the SUM Function step 2

Calculate Obtained Marks Using the SUM Function step 3

3. Determine Percentage

Determine Percentage

Marksheet in Excel- Determine Percentage

4. Assign Academic Grades with the IF Function

Percentage Academic Grade
>=90% O
80% to 89% A
70% to 79% B
60% to 69% C
50% to 59% D
40% to 49% E
F

=IF($ Q3 >=90%,”O”,IF ($ Q3 >=80%,”A”,IF ($ Q3 >=70%,”B”,IF ($ Q3 >=60%,”C”,IF ($ Q3 >=50%,”D”,IF ($ Q3 >=40%,”E”,IF ($Q3 ) ) ) ) ) ) )

Note: The formula uses a $ sign before Q for consistent application across all cells. Make sure to close all seven parentheses to prevent errors.

Assign Academic Grades with the IF Function

Note: The breakdown of the above formula is given in this table:

Condition Formula Grade Representation
Condition 1 IF(Q3>=90%, “O”,) Outstanding
Condition 2 IF(Q3>=80%, “A”,) Excellent
Condition 3 IF(Q3>=70%, “B”,) Good
Condition 4 IF(Q3>=60%, “C”,) Satisfactory
Condition 5 IF(Q3>=50%, “D”,) Needs Improvement
Condition 6 IF(Q3>=40%, “E”,) Poor
Condition 7 IF(Q3 Fail

Marksheet in Excel - Assign Academic Grades with the IF Function

5. Rank Students (1 to 10) Using the RANK Function

You must apply the rank formula in the Excel marksheet to determine the students’ rank from Obtained Marks.

Follow the below steps:

Marksheet in Excel - Rank Students (1 to 10) Using the RANK Function

In the below image, you can observe that Emily Walsh secured rank 1 with 456 marks, while John Fitzgerald achieved rank 2 with a score of 424, and so forth.

Explanation of the formula: =RANK(P3, $P$3:$P$12, 0)

6. Calculate Results (Pass or Fail) with IF and AND Functions

=IF(AND(G3>=25,H3>=10,I3>=25,J3>=10,K3>=25,L3>=10,M3>=35,N3>=35),”Pass”,”Fail”)

➔ Gabriel Sullivan’s result is a Pass.
➔ Anthony Smith’s result is Fail, and so on.

Calculate Results (Pass or Fail) with IF and AND Functions

Note: Conditions inside the AND function require specific values to be met for each subject:

If all these conditions are true, the formula returns “Pass”; otherwise, it returns “Fail”.

7. Determine Status with Nested IF Statements with Two Steps

Step 1: Identify subjects in which a student has failed.

➔ Column O: Tax-Theory
➔ Column P: Tax-Internals
➔ Column Q: IB-Theory
➔ Column R: IB-Internals
➔ Column S: BE-Theory
➔ Column T: BE-Internals
➔ Column U: BC-Theory
➔ Column V: EVS-Theory

Identify subjects in which a student has failed

➔ Cell P3: =IF(H3>=10, “”, “Tax-Internals”)
➔ Cell Q3: =IF(I3>=25, “”, “IB-Theory”)
➔ Cell R3: =IF(J3>=10, “”, “IB-Internals”)
➔ Cell S3: =IF(K3>=25, “”, “BE-Theory”)
➔ Cell T3: =IF(L3>=10, “”, “BE-Internals”)
➔ Cell U3: =IF(M3>=35, “”, “BC-Theory”)
➔ Cell V3: =IF(N3>=35, “”, “EVS-Theory”)

Identify subjects in which a student has failed step 2

Identify subjects in which a student has failed step 2

For example, Anthony Smith failed in Tax-Theory, IB-Internals, BE-Internals, and EVS-Theory.

Formula explanation:

Step 2: Find the Final Status (Pass or Fail) with Subject Names.

=IF(IF ( AND ( $G3 >=25, $H3 >=10, $I3 >=25, $J3 >=10, $K3 >=25, $L3 >=10, $M3 >=35, $N3 >=35 ) ,”Pass”,”Fail” ) =”Pass”,”Pass”,”Fail in: “& O3 &” “& P3 &” “& Q3 &” “& R3 &” “& S3 &” “& T3 &” “& U3 &” “& V3 )

Find the Final Status (Pass or Fail) with Subject Names 1

Formula explanation:

Note:

8. Improve the Table Presentation with Formatting

Improve the Table Presentation with Formatting

Example: How to Create Excel Marksheet Format?

We will create the format of marksheet in Excel in Sheet2. For this, we will use data from Sheet1 to automate the marksheet using the VLOOKUP formula.

Follow these steps to complete the entire format of the marksheet in Excel on Sheet2: