How to create an advance Student Mark sheet / Grade sheet in excel

At first create a mark sheet format as shown below, here last row is 36.

First we will work with Selective Subject, here we have two subject but we have to put number in only one subject because a student can take either of the language subject. If by mistake put number on both subject then the report will be wrong. That is why we will put restriction in those columns so that if we put number on Bengali then we will not be able to put number on Hindi and same for the other. For that we need to use Data Validation.

To do that first select cells from C7 to C36, then go to Data tab and click on Data Validation then select Data Validation from the Data Tool group.

Then in Data Validation dialog box, select Settings tab and then select Custom in Allow menu, then in the formula box type =ISBLANK($D7). Then click OK.

Then for D column repeat the same step but the formula will be =ISBLANK($C7).

Now if you type any number in C7, you cannot type any number in D7 and vice versa.

Now type student name and the numbers they got in each subjects as shown below. Here we will use AB as absent in examination.

Then we will use Conditional Formatting to highlight failed subject and absent subject as show below.

So select the cell range C7:I36, go to Home tab and click on Conditional Formatting and select Highlight Cells Rules and then Between, and then in the Between box type 1 & 34 as shown in image below. And then use custom color as red color then type OK.

Now again use Conditional formatting for absent, select Conditional formatting then Highlight Cells Rules then Text that Contains, then in the box type AB and select yellow color as cell color.

Now we will work for Result. The conditions for results are given below:

Result Condition

Condition for Absent, if all subject has AB

Condition for Pass, if all subject has >=35

Condition for Fail, if total count of cells which are <35 & AB is greater than 2

Condition for Compartment, if total count of cells which are <35 & AB is equal to than 2

In cell J7 type the formula

=IF(SUM(C7:I7)=0,"Absent",IF(COUNTIF(C7:I7,"AB")=COUNTA(C7:I7),"Absent",IF(AND(COUNTIF(C7:I7,"AB")<=0,COUNTIF(C7:I7,"<35")<=0),"Pass",IF(AND(COUNTIF(C7:I7,"<35")>=1,(COUNTIF(C7:I7,"AB")+COUNTIF(C7:I7,"<35"))>=3),"Fail","COM"))))

=IF(SUM(C7:I7)=0,"",IF(COUNTIF(C7:I7,"AB")=COUNTA(C7:I7),"Absent",IF(AND(COUNTIF(C7:I7,"AB")<=0,COUNTIF(C7:I7,"<35")<=0),"Pass",IF(COUNTIF(C7:I7,"AB")+COUNTIF(C7:I7,"<35")=2,"COM","FAIL"))))

Now we will work for Total. The conditions for Total are given below:

Total Condition

If Result is Pass, Fail or Compartment then we will total but if result is blank then we will not do total.

In cell K7 type the formula

=IF(J7="Pass",SUM(C7:I7),IF(J7="COM",SUM(C7:I7),IF(J7="Fail",SUM(C7:I7),"")))

=IF(OR(J7="Pass",J7="COM",J7="Fail"),SUM(C7:I7),"")

Now we will work for Percent. The conditions for Percent are given below:

In cell L7, type the formula

=IFERROR(AVERAGE(C7:I7),"")

=(IF(NOT(J7="Absent"),K7/6,""))

To excelude both Absent Student and Compartment student

=IF(OR(J7="Absent",J7="Compartment"),"",K7/6)

Now we will work for Grade. The conditions for grade are given below:

No Grade for Absent, Fail & Compartment Student

Marks Range Grade

91-100 A+

81-90 A

71-80 B+

61-70 B

51-60 C

41-50 D

35-40 E

0-34 F

In cell M7, type the formula

=IF(OR(J7="Absent",J7="Fail",J7="COM"),"",IF(J7="Pass",IF(L7>=90,"A",IF(L7>=70,"B",IF(L7>=50,"C",IF(L7>=35,"D","Fail"))))))

Now we will work for Rank. The conditions for rank are given below:

Rank only for pass student

Rank only for pass student if duplicate rank

In cell N7, type the formula

=IF(J7="pass",RANK(L7,$L$7:$L$36,0),"") For only

=IF(OR(J7="Absent",J7="Fail",J7="COM"),"",RANK(K7,$K$7:$K$36,0)+COUNTIF($K$7:K7,K7)-1)

Now you can work with formatting the mark sheet. Watch video shown above. If you want to learn more about alternate row color Click Here.

Now we will work with Visual Basic Application.

First Open the Visual Basic Application with ALT+F11 or click Visual Basic in Developer tab.

Then click Insert menu and select Module and then paste the code given below. This code is for coping the sheet, so that you can clear the previous worksheet and create mark sheet for another class.

Sub Copyrenameworksheet()

Dim ws As Worksheet

Set wh = Worksheets(ActiveSheet.Name)

ActiveSheet.Copy After:=Worksheets(Sheets.Count)

If wh.Range("A1").Value <> "" Then

ActiveSheet.Name = wh.Range("A3").Value

End If

wh.Activate

End Sub

Then we paste another code to clear the role numbers, names of the students and all numbers, the code is given below:

Sub Clearcells()

Range("A7", "I7").ClearContents

Range("A8", "I8").ClearContents

Range("A9", "I9").ClearContents

Range("A10", "I10").ClearContents

Range("A11", "I11").ClearContents

Range("A12", "I12").ClearContents

Range("A13", "I13").ClearContents

Range("A14", "I14").ClearContents

Range("A15", "I15").ClearContents

Range("A16", "I16").ClearContents

Range("A17", "I17").ClearContents

Range("A18", "I18").ClearContents

Range("A19", "I19").ClearContents

Range("A20", "I20").ClearContents

Range("A21", "I21").ClearContents

Range("A22", "I22").ClearContents

Range("A23", "I23").ClearContents

Range("A24", "I24").ClearContents

Range("A25", "I25").ClearContents

Range("A26", "I26").ClearContents

Range("A27", "I27").ClearContents

Range("A28", "I28").ClearContents

Range("A29", "I29").ClearContents

Range("A30", "I30").ClearContents

Range("A31", "I31").ClearContents

Range("A32", "I32").ClearContents

Range("A33", "I33").ClearContents

Range("A34", "I34").ClearContents

Range("A35", "I35").ClearContents

Range("A36", "I36").ClearContents

End Sub

Sub Clearcells()

Range("A7:I36").ClearContents

End Sub

Now close the Visual Basic and two buttons on the right side of the mark sheet, in one button type Copy Sheet and on another type Clear Sheet.

Right click on Copy Sheet button and select Assign Macro and select Copyrenameworksheet from the dialog box and click OK.

then right click on Copy Sheet button and click Assign Macro and select Clearcells from the dialog box and click OK.

Now Save the file as Excel Macro-Enabled Workbook.

Your Mark Sheet is finally created, test it for errors.

Thats All

********************************* ~:Support Our Work Financially:~ *********************************

Project File Type: Premium

Project File Cost: Rs.100/-