How to Make Marksheet in Excel?

Getting Started

1. Start by organising student details, subjects, and marks in a clear Excel worksheet.

2. Decide what calculations you need, such as total marks, percentage, average, grade, or pass/fail status.

3. Use formulas and functions to automate calculations and minimise manual errors.

4. Apply simple formatting to make the marksheet clear, structured, and easy to read.

5. Check all entries and formulas carefully before finalising or sharing the marksheet.

You may have come across teachers and professors working hard, sweating and writing marks for each student in a register. But did you know where they lack after all this hard work? It is the smart work that can simplify making a Marksheet in Excel.

Today, according to Acuity Training, one out of 25 professionals spend 80% of their time using Excel. So, it can work wonders for anyone who wants to use Excel and has become a one-stop solution for all your needs.

So, if you are in the education sector, leave the hassle of marking everything on paper. Use Excel to effortlessly manipulate student data, all that while ensuring it is error-free. Wondering how to do it? Worry no more.

In this blog, you will learn how to create a Mark Sheet in Excel step by step. You can create a complete, fully automated mark sheet management system in excel.

What are the Contents of Marksheet Template in Excel?

A marksheet template in Excel usually contains all the essential details required to record, calculate and review a student’s academic performance. It organises information such as student details, subject-wise marks, total scores, percentages, grades and results in a clear format, making assessment records easier to manage and understand.

To understand how a complete marksheet is organised, let’s look at the key contents it typically includes.

1) Basic Data Sheet

It contains the basic student and school information that may appear on the final marksheet or report card. It can include:

a) School name

b) School address

c) Academic year detail

d) Class teacher’s name

e) Principal’s name

f) Name of the student

g) Class

h) Division

i) Roll number

j) Total attendance

Here is an example of the basic information sheet:

2) Template for Grading System

The grading template essentially includes parameters by which the students’ marks can be divided, making it easy to calculate final grades. This also provides students and parents with insight into the grading system. You can create this sheet using the following steps:

In the above spreadsheet, you can see that marks are categorised based on grades. Further remarks are provided to explain the student’s performance throughout the academic year.

3) Marks Entry Sheet

The last and vital step is to create a sheet with data values as students’ marks. You can enter data/marks in this sheet at your convenience. In simple terms, marks can be displayed on the spreadsheet in subject-wise, term-wise, and roll number-wise patterns.

This way you will create three sheets:

1) Sheet 1: To store the personal details

2) Sheet 2: To specify the grading system

3) Sheet 3: To insert the marks of each student

How to Make Marksheet in Excel?

After declaring all the necessary details, such as basic data of students and their marks, it is time to find the results of the students. To fulfil this purpose, you will need to perform various operations and enter formulas. This way, you will obtain a precise marksheet format for your class, free of errors. 

But what are these operations? How do they work? Well, Microsoft Excel provides various formulas and Excel Functions to manipulate data. For example, in the marksheet, if you want to add marks for Tracy, you can use the SUM() function, and you will get the total of all the marks within a few seconds. 

Let's find out the total marks, averages, grades, and the results of the students using various functions that MS Excel provides.

1) Entering Personal Information

The first step in creating a marksheet is to enter the personal details of the students. But the question arises, how can you automate the process? Do you have to jump from one sheet to another? Well, the answer is no. Here, we will use the VLOOKUP() function to enter the basic information. So, let’s get started:

Step 1: Firstly, enter the student’s roll number, class, and division in the specified columns.

Step 2: Use the VLOOKUP function to enter the student’s name. Your marksheet will look as follows:

Here, in the VLOOKUP function, we first enter the lookup value, followed by a comma (H7,). The next step is to enter the sheet or array table from where we need to extract the data (Sheet2!A4:H20,2). Further, enter the column index number where you think the data lies.

Result: This way you will get the desired data value. For example, here we got Tracy as our data value.

Additionally, if you also want to add other basic information like class teacher’s name, principal’s name, date of birth, or any other detail, you can use VLOOKUP in Excel and follow a similar format.

2) Entering the Marks Obtained

To insert the subject-wise marks of each student, you can again use the VLOOKUP() function, but with a twist. Here, you will need to apply conditional formatting to determine the performance of each student. Here’s how you can do it:

Step 1: Enter the VLOOKUP formula in the specified cell. Here for example, “in cell C12, the marks obtained by Tracy for the first subject will be retrieved. The formula will be as follows:

Step 2: Press the Enter key. Then, to enter the same formula to find out the marks for other subjects, you just need to change the column index number according to the subject column in Sheet 1. This way you will get all the marks for Tracy, like this:

3) Conditional Formatting

As you can see from the above image, finding the marks for Tracy has become easy. However, if you want to compare the marks, you can turn on conditional formatting in Excel. This way, teachers, students, as well as the parents will be able to see in which subjects does the student lacks. Thus, providing an easy analysis of their marks. Just follow these steps to apply conditional formatting:

Step 1:

a) Select the cells

b) Click on the HOME ribbon

c) Then click on the CONDITIONAL FORMATTING option

d) Go to HIGHLIGHT CELLS RULES

e) Click LESS THAN

Step 2: Further, enter the minimum passing marks in the first box.

Step 3: Select a colour of your choice from the drop-down box. This way you will be able to highlight the data values.

Step 4: Click on the OK button.

If a student’s mark fails to meet the criteria set to pass, the data values will get highlighted in that colour. Your marksheet will look something like this:

Learn how to use Excel workbook settings and troubleshoot formulas with our Microsoft Excel Expert MO201 Course  - Join now!

4) Adding the Marks

To determine the total marks of the students, you can use the SUM() function. Let's take a closer look at how this can be done:

Simply insert the formula into the designated cell. The syntax for obtaining a total is as follows:

a) Comma method: This method utilises comma(,) to insert each cell value. For example, SUM(A1,A2,A3,A4)

b) Colon method: In this method, a colon replaces the comma, and you only need to specify the first and last cell values. For example, SUM(A1:A4)

As a result, you will get the total marks for Tracy which is 427.

5) Calculating the Percentage

Calculating percentages is crucial to determine the final result of a student. For teachers, this process may be time-consuming and mentally taxing as it requires a lot of calculations. However, with Excel, this task becomes a breeze. You can follow the steps mentioned below:

Step 1: Select the cell on which you want to apply the formula.

Step 2: Enter the cell number that contains the total marks obtained by the student and divide it by the total marks, i.e., 500.

Result: You will get the percentage for Tracy by implementing a simple formula.

6) Finding Student’s Grade

To find students’ grades, you can use the IF() function. As we have already defined the criteria for marking percentages in Sheet 3, it will be easy to insert students’ grades in the marksheet. Here is an example of how to declare the grades of the students using the IF() function:

Step 1: First, enter all the possible parameters that can be applied to a cell range. The table is referenced as the "logical_test”. It contains the criteria for grading the percentages of students.

Step 2: Next, use logical operators such as “Greater Than,” “Less Than,” and “Equal To.”

Step 3: Then compare the cell with the range where you want the grades to fit in.

Result: As a result, you will get the grades that you previously specified for a range of percentages.

By using the IF() function, you can determine if the percentage scored by the student is greater than or equal to 94, and the value will return as True. However, if the criteria are not met by the percentages, the argument will return as False.

If you need to fulfil additional criteria and return True, you will need to write the IF function again. In this case, the IF function is applied 9 more times.

Remember to close the bracket the same number of times you have opened it, which in this case is nine times.

Gain knowledge of the Excel Shortcuts with our Microsoft Excel Course - Register now!

7) To Insert the Remarks

In order to add remarks for the students you can use the same function, i.e., the IF() function. Let's see how it works:

Step 1: Insert the formula into the cell.

Step2: Specify all the possibilities you want the remarks to fit in. The following is the manner you can do it:

Result: After you have specified the conditions, you will get your final remarks:

Benefits of Creating Marksheet in Excel

MS Excel can benefit you in many ways if you know how to use it efficiently. It not only helps teachers, but also provides a better understanding of the data to parents, giving a deep insight into students’ performance. But this is just one of the many benefits; some of the other benefits of Excel are as follows:

1) Time-saving: Using Excel formulas and functions makes calculations quick and easy, saving teachers time and effort.

2) Reusable Sheets: Unlike paper, a marksheet in digital format can be altered multiple times and without any wastage. So, even if you need the marksheet five or six times in a year, it will be easily accessible.

3) Ready-to-view Marksheet: Excel allows users to print the marksheets, making it easy to provide copies to students and parents.

4) Improved Data Analysis: Excel presents data in a streamlined format, allowing teachers to compare students' performance and provide opportunities for improvement.

Boost productivity and simplify complex processes through our Microsoft Excel VBA and Macro Training – Join now!