# How to Design a Simple Grade Book Template Using Excel Formulae

## How to create a Grade Book Template using Microsoft excel formulae

The aim of this article is to help teachers and other people grade the students in an easy way. I advise that users or readers should have at least a basic knowledge of Microsoft excel.

This template has two sections: The entering section and the school reports section. First, let us start by looking at the picture below:

Once the names of the students and their scores are entered in the above section, they are linked to the school reports part automatically. For example, in the “NAME” column, if you type the name say “Masautso Chiwamba” with the following scores: AGR = 90, BIO = 76, CHE = 70, CHI = 67, ENG = 80, HIS = 45, GEO = 34, LIF = 100, MAT = 56, PHY = 33 and SOC = 91, then these details will be linked automatically to the school report section. See the pictures below:

Note that each student we enter in the entering section has his or her own Progress Report Card. What it means is that if we enter 100 names, then we are also going to have 100 progress reports. Note also that the above school report is based on A-F scale.

So, let us now go into details on the formulae behind the above template.

First, we are supposed to create the Progress Report Card like the one above or any depending on the details we would like to put on the report card. So, for the sake of understanding, let’s use the above progress report card.

The progress report has a row for the “NAME OF STUDENT”. The name entered in the entering section on the sheet named GRADE BOOK TEMPLATE-JCE is linked on this row. For example, if we type “Masautso Chiwamba” in the entering section on cell A2, then to link this we copy the cell A2 and then open the sheet named SCHOOL REPORTS-JCE and then paste the link on B6 (='GRADE BOOK TEMPLATE- JCE'!$A$2). See the pictures below:

From the “entering section” AGR stands for Agriculture, BIO stands for Biology, CHE stands for Chemistry, CHI stands for Chichewa, ENG stands for English, HIS stands for History, GEO stands for Geography, LIF stands for Life Skills, MAT stands for Mathematics, PHY stands for Physical Science and SOC stands for Social and Development Studies. Therefore, the score entered in AGR is copied and paste linked in Progress report card on cell B13. The score entered in BIO is also copied and paste linked on cell B14 in the Progress Report Card. The process is repeated for the rest of other subjects.

Still on the Progress Report Card, the “GRADE” column contains grades of a student. To get these grades automatically, we use a formula. For example, using the progress report above, the formula used on cell C13 is =IF(B13>=80,"A",IF(B13>=65,"B", IF(B13>=55,"C",IF(B13>=40,"D","F")))). The formula simply says that if a score is greater and equal to 80 then award a grade of “A”, if the score is greater and equal to 65 but less than 80, then award a grade of “B”, if the score is greater and equal to 55 but less than 65, then award a grade of “C”, if the score is greater and equal to 40 but less than 55, the award “D”, otherwise award “F” if the score is less than 40. This formula can be modified to meet your needs.

Similarly, the same formula can be used on cell C14 as =IF(B14>=80,"A",IF(B14>=65,"B", IF(B14>=55,"C",IF(B14>=40,"D","F")))). Continue like this with the rest of other cells.

The “TEACHER’S COMMENTS” column also uses the same formula as that of “GRADE” column. However, the only difference is on the details used in the formula. For example, the formula used on cell E13 is =IF(B13>=80,"Excellent",IF(B13>=60,"Very Good", IF(B13>=50,"Good",IF(B13>=40,"Pass","Fail")))). Similarly, the formula on cell E14 is =IF(B14>=80,"Excellent",IF(B14>=60,"Very Good", IF(B14>=50,"Good",IF(B14>=40,"Pass","Fail")))). Continue like this with the rest of other cells.

The “SIGN” column can also be called the “INITIAL” column. Instead of writing the initials of each report, we simply go to the first progress report on top and write there once and the initials for all progress reports are also written at the same time.

For the first time, to do this, simply go to the second report card on cell F61 and write equals and then copy the initial(s) on the first progress report on cell F13 and paste it on the second progress report. See the figures below:

Continue like this with the rest of other cells and the subsequent progress reports. Although this can be time consuming, but once done, it is done forever.

The same process applies to “FORM”, “TERM” and “YEAR” parts on the progress report card.

Having explained some of the sections of the progress report, we have “POSITION” (for the specific subject), “Number in Form” and “Position” (for all students) remaining. These sections will be explained later after looking at the entering section of our template.

Now, there are some columns that have been hidden in the entering section of our template. The picture below shows some of those hidden columns:

From the above template, the hidden columns are “GRADE”, “POSITION” and “REMARK” which are coming after each subject.

Let us now look into details at these hidden columns. The “GRADE” column is similar to the one we have already seen in the progress report card. For example, once the score is entered on cell B2, then it is changed to A-F scale in cell C2 automatically by using the following: =IF(B2>=80,"A",IF(B2>=65,"B", IF(B2>=55,"C",IF(B2>=40,"D","F")))). If the score is entered on cell B3, then the formula used in cell C3 is =IF(B3>=80,"A",IF(B3>=65,"B", IF(B3>=55,"C",IF(B3>=40,"D","F")))).

Again, if the score is entered on cell N2 for example, then the formula used in cell O2 is =IF(N2>=80,"A",IF(N2>=65,"B", IF(N2>=55,"C",IF(N2>=40,"D","F")))). Continue like this with other cells.

The “POSITION” column relates a student with the rest of other students in a specific subject in order of the scores. Those that have good scores are on lower positions. Note that there are 123 rows that have been used in this template. Therefore, using the above template, the formula that has been used in cell D2, for example is =RANK(B2,B2:B123,0). The formula is ranking the score entered in cell B2 in relation to the scores entered in the same column. In the same way, =RANK(B3,B2:B123,0) is ranking the score entered on B3 in relation to the rest of other scores. Again, the formula used in cell L2 which is =RANK(J2,J2:J123,0) is ranking the score entered in cell J2 in relation to the scores entered in column J. So, the formula used in D2, is the one that has been linked to “POSITION” column of the progress report. In this case, cell D2 is linked to D13 of the progress report as shown in the pictures below:

Similarly, cell L2 is linked to D14 on the progress report, P2 is linked to D15, and so on. Note that “BIB” which is Bible Knowledge is not appearing on the progress report. However, we can include it if want to.

The “REMARK” column indicates whether a student has passed or failed that specific subject. For example, if a student is to pass any subject in this case, he or she has to get a score of 40 and above. Using the above template, the formula that is used in cell E2 is =IF(B2>=40,"PASS","FAIL"). This means that if the score entered in cell B2 is greater and equal to 40, then a “PASS” should be awarded otherwise that is a failure (“FAIL”). Likewise, the formula used in cell I2 is =IF(F2>=40,"PASS","FAIL").

As the names of students are entered in the “NAME” section, the number of students entered, are added automatically. Using the above template, this is achieved by using the following excel formula =COUNTA(A2:A123). See another picture below:

This formula is supposed to be written at the end of rows that are used in the template. In this case, if there are 123 rows, then row 124 under “NAME” column should contain this formula. So, the formula on row 124 under “NAME” is the one that has been linked to “Number in Form” for each progress report. See the pictures below:

We are still on the entering section of our template. We will now consider the last section which was also hidden. See the picture below:

The above section acts as a summary sheet of the scores entered for each student. The section has columns for total marks, remark as well as position. The column for position relates each student to all students in the classroom.

See the picture below to better explain some of the columns in the summary sheet:

The formula used in the summary sheet section is simple. For example, on cell AX2, simply type an equals (=) sign and then type A2. Also on cell AY2, type an equals (=) sign and type B2. Note that cell A2 corresponds to AX2 as they all contain the name “Masautso Chiwamba” and also B2 corresponds to cell AY2 as they all contain the scores for AGR. This also applies to columns AZ, BA, BB, BC, BD, BE, BF, BG, BH and BI.

The “TOTAL MARKS” column contains the sum for the best six subjects including ENG (English) as it is in this case. It is regarded as the key subject. In this case, using the above template, the formula used on cell BJ2 is =LARGE(AY2:BH2, 1) + LARGE(AY2:BH2,2) + LARGE(AY2:BH2,3) + LARGE(AY2:BH2,4) + LARGE(AY2:BH2,5) + BI2.

The “REMARK” column simply displays the information whether the student has passed or failed for the best six subjects in all the subjects that a student has written. Note that for the student to pass the examination in this case, he or she must have a score of 40 or more including English and as such the formula used on cell BK2, for example, is =IF(BI2<40,"FAIL",IF(COUNTIF(AY2:BH2,">=40")>=6,"PASS","FAIL")).

The “POSITION” column depends on “TOTAL MARKS” column. Just as I have already explained earlier, the position column relates each student to the rest of other students and the formula used for example on cell BL2 above is =RANK(BJ2,BJ2:BJ123,0). This formula applies to the rest of other cells in column BL. It is also the formula that has been linked to position part on the progress report section. See the picture below:

Let us now wind up the tutorial by looking at the whole student progress report. The picture below shows the report:

By looking at the above student progress report card, we notice that there is an “Aggregate” part and the “PASS” or “FAIL” part that need to be explained as well. Check the parts I am talking about below (indicated by red arrows):

Using the above template, the formula that has been used on cell C24 above is =IF(SUM(B13:B23)/11>=80,"A",IF(SUM(B13:B23)/11>=60,"B", IF(SUM(B13:B23)/11>=50,"C",IF(SUM(B13:B23)/11>=40,"D","F")))).

The formula explains that if the sum of the scores from B13 to B23 divide by 11is equal and greater to 80, then award an “A”, if the sum of the scores from B13 to B23 divide by 11 is greater and equal to 60 but less than 80, then award “B”, if the sum of the scores from B13 to B23 divide by 11 is greater and equal to 50 but less than 60, then award “C” and if the sum of scores from B13 to B23 divide by 11 is greater and equal to 40 but less than 50 then award “D”, otherwise award a grade of “F” which is a failure. Note that the number 11 we are dividing is the total number of subjects we have on the progress report card.

To show whether the student has failed or passed the examination then we use the following formula on cell E24: =IF(B17<40,"FAIL",IF(COUNTIF(B13:B23,">=40")>=6,"PASS","FAIL"))

The same formula is the one that has been used on cell E10 above.

Note that with the same formulae used on A-F grading system, we can also come up with the student progress report using 1-9 grading system or any other system we would like to implement.

Finally, I would like to state that if there is any question in relation to the above template then you can contact me any time.

**© 2017 Masautso Chiwamba**

## Comments

No comments yet.