Marksheet in Excel

What is a 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.

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

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

2. Calculate Obtained Marks Using the SUM Function

• In Cell P3, enter the formula =SUM(G3,H3,I3,J3,K3,L3,M3,N3)

• After pressing Enter, you will see the value 410 appearing in cell P3. This indicates that Michael O’Connor scored a total of 410 marks out of 500 across all subjects.

• Drag the formula from cell P3 to P12 using the fill handle to display the marks of all students.

3. Determine Percentage

• Calculate the percentage by dividing the Obtained Marks by the Total Marks of each student.
• To calculate it for the first student, in cell Q3, enter the formula = P3/O3.

• Press Enter, and you will get a value of 0.82.
• To represent it as a percentage (82%) instead of 0.82, select cells Q3:Q12. Then, go to Home Tab> Number Group> Select Percentage (Shortcut: Ctrl+Shift+%). Now, Q3 will show 82% instead of 0.82.
• To apply this to other students, drag the fill handle or double-click it.

4. Assign Academic Grades with the IF Function

• We will assign grades based on the following percentage range:

• To find the grade of the first student, use the following nested IF formula in cell R3. And then apply it to the rest of the students by dragging it down.

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

• Finally, the Academic Grades for all 10 students will be displayed as follows:

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:

• For the first student, Michael O’Connor, type the formula =RANK(P3, $P$3:$P$12, 0) in cell S3.
• Press Enter, and you will see Michael O’Connor’s rank displayed as 3.
• To extend this ranking to all students, select cell S3, then drag the small square downwards at its bottom right corner.
• Now, column S will show rankings for all students.

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)

• P3: Represents the obtained marks for the first student, Michael O’Connor.
• $P$3:$P$3: It is the range holding all scores. We have used absolute references to lock this range.
• 0: Indicates ranking in descending order, where the highest score receives rank 1.

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

• To determine the Pass or Fail status of the first student, use the following formula in cell T3:

• Press Enter to display Michael O’Connor’s result as Pass.
• Select cell T3, then drag the small square downwards at its bottom right corner to apply the formula to other students in Column T.
• In the provided image:

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

7. Determine Status with Nested IF Statements with Two Steps

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

• Insert 8 Columns before the Total Marks Column (current column O).
• Change the background color to light peach and name columns as follows:

➔ 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

• In cell O3 (Tax-Theory), type the formula: =IF(G3>=25,””, “Tax-Theory”)
• Drag the formula for cells O3 to O12.
• Repeat for other columns using respective formulas:

➔ 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”)

• The result shows subjects in which a student has failed.

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

Formula explanation:

• IF(G3>=25): It checks if the value in cell G3 is greater than or equal to 25.
• “”: It returns an empty string if G3>=25 is true, indicating the student has not failed.
• “Tax-Theory“: This text is returned if G3<25, denoting the student failed in Tax-Theory.

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

• Select cell AC3, and type this formula:

• Press Enter and drag down the formula to check the Status in Column AC.

Formula explanation:

• The inner IF statement IF( AND($G3>=25, $H3>=10, $I3>=25, $J3>=10, $K3>=25, $L3>=10, $M3>=35, $N3>=35), “Pass”, “Fail” ) checks conditions using the AND function. If all are true, it returns “Pass”; otherwise, “Fail.”
• The outer IF statement IF( [Inner IF Statement] = “Pass”, “Pass”, “Fail in: ” & O3 & ” ” & P3 & ” ” & Q3 & ” ” & R3 & ” ” & S3 & ” ” & T3 & ” ” & U3 & ” ” & V3 ) checks the result. If “Pass,” it returns “Pass”; if “Fail,” it returns a custom message that concatenates the values in cells O3 through V3 with spaces, creating a message like “Fail in: [O3] [P3] [Q3] [R3] [S3] [T3] [U3] [V3].”

8. Improve the Table Presentation with Formatting

• Add numbers (1-29) in the row above the main headings (Roll No, Grade, Name).
• Highlight this row in yellow for easy identification.
• Hide Columns O to V.

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:

• • Design the Marksheet Format
  • Use VLOOKUP to Fetch the Date in Sheet2
  • Calculate the Total Obtained Marks for Each Subject
  • Determine the Grade
  • Calculate Total Marks (Theory + Internal), Passing Marks, and Total Obtained Marks
  • Find Percentages
  • Apply VLOOKUP for Rank, Academic Grade, and Status
  • Apply Conditional Formatting for Enhanced Visibility

1. Design the Marksheet Format

Create the marksheet format by adding Total Marks and Passing Marks.

2. Use VLOOKUP to Fetch the Date in Sheet2

While using the VLOOKUP formula in Excel marksheet-, we will often refer to the range “A2:AC12” in Sheet1, where all student data is stored. To simplify, let’s name this range “Studentsdata.”

Follow these steps:

1. Open Sheet1.
2. Select the entire table (A2:A12).
3. Go to the Formulas
4. Go to the Defined Names group and click Define Name.
5. A dialog box will appear.
6. In the Name section, name it “Studentsdata” (No spaces are allowed).
7. Click OK. The table array is now created and identified as Studentsdata.

We will use the VLOOKUP function in the Excel marksheet to fetch personal information like DOB, Father’s Name, Mother’s Name, and Obtained Marks (Theory and Internals).

• For Anthony Smith (Roll no 4), enter the VLOOKUP formula in Cell C9: =VLOOKUP($I$7,Studentsdata,3,0)
• Press Enter to display Anthony Smith’s name in Cell C9.

• Repeat the VLOOKUP process for DOB, Father’s Name, Mother’s Name, and Obtained Marks – Theory and Internals.

3. Calculate the Total Obtained Marks for Each Subject

Calculate the Total Obtained Marks for each subject using the SUM formula.

• Drag the formula to display the Total Obtained marks for other students.

4. Determine the Grade

To find the grade for each subject, use the IF function. This step is similar to calculating academic grades in Sheet 1.

5. Calculate Total Marks (Theory + Internal), Passing Marks, and Total Obtained Marks

• Calculate the Total Marks by using the SUM formula: =SUM(C14:D18) in Cell C20.

• Use the same approach for calculating Passing Marks and Total Obtained Marks.

6. Find Percentages

Find the Percentage by entering =C22/C20 in cell I20. The result will be 36%.

7. Apply VLOOKUP for Rank, Academic Grade, and Status

Use the VLOOKUP function to determine Rank, Academic Grade, and Status:

• In cell I21, enter the formula: =VLOOKUP($I$7, Studentsdata, 26, 0).
• In cell I22, enter the formula: =VLOOKUP($I$7, Studentsdata, 27, 0).
• In cell D24, enter the formula: =VLOOKUP($I$7, Studentsdata, 29, 0).

8. Apply Conditional Formatting for Enhanced Visibility

We will use conditional formatting to highlight specific cells/ranges in “light red fill and dark red text“. For instance, we will highlight Cells G14:G18 with a “light red fill and dark red text” if the score falls below the passing marks (25) in Theory.

We will apply conditional formatting to the following cells:

• Cells G14:G18: Highlight if the score is below passing marks (25) in Theory.
• Cells H14:H16: Highlight if the score is below passing marks (10) in Internals.
• Cells I14:I18: Highlight if the score is below passing marks (35) in Total Obtained.
• Cells J14:J18: Highlight if the Grade is F.
• Cell D24: Highlight if the text contains the word “Fail.”

To implement conditional formatting, select the respective cells > Home Tab > Conditional Formatting > Highlight Cells Rule > Less Than > Enter 25 (for Theory) or 10 (for Internals) > Click Ok. Repeat the respective steps for the other specified cells.

In the end, you will have a proper format of the marksheet in Excel.

Things to Remember while Creating Marksheet in Excel

• Use nested IF Statements to assign grades based on percentage ranges.
• Ensure proper closure of parentheses in the nested IF statements.
• Use the RANK formula in the Excel marksheet to rank students in descending order.
• Use IF and AND functions to determine if a student passes or fails based on subject-specific criteria.
• Insert additional columns for each subject.
• Hide unnecessary columns for clarity.
• Use the VLOOKUP function to automatically fetch data from Sheet1 based on the Roll No of the student.
• Lock cell references appropriately for accurate copying.
• Maintain consistent formatting for clarity.
• Verify the accuracy of formulas and formatting in both sheets.
• Ensure the marksheet provides a clear summary of student performance.

Recommended Articles

We hope you liked our guide on creating a marksheet in Excel! In this guide, we discussed Excel’s most useful functions for creating students’ marksheet in Excel, such as SUM, IF, and Nested IF. We also showed you how to use the VLOOKUP function to create the format of the marksheet in Excel. If you found this guide helpful, you can also explore some of the other useful articles:

1. Advanced Excel Formulas and Functions
2. Payroll in Excel
3. Insert Calendar in Excel
4. Calculate Income Tax in Excel