Free Course Marker for Teachers

Teachers are overworked. After spending the entire day teaching at school, the teachers go home and start marking tests for another 4 to 5 hours. Because of this, I have created a Course Marker Excel file to make a teacher's life easier. The file created supports up to 8 tests/exams and 30 students in one course.

If you want to use Course Marker as a template for marking, click here to download the file with protected formulas.

If you want to manipulate Course Marker such as adding more available tests and students or you just want to see how the formulas work, click here to download the file without protected formulas.

If you want to go through the walk-through tutorial below, click here to download the file with sample data.

Let's begin.

After downloading and opening the file with sample data, you should see the first worksheet called 'Tests'. See Figure 1.0.

Figure 1.0










Where:

'History 101' - (Data Entry Required) is the yellow area where you would enter the name of the course you are teaching.

'Test Name' - (Data Entry Required) The name of the tests you would like for your course.

'Date' (Data Entry Required) - The date of each scheduled test.

'Total Marks' (Data Entry Required) - Total marks for each test.

'Bell Curve' (Data Entry Optional) - The number you would enter if you feel the student scores were not high enough for a particular test.

'Actual Marks' (No Data Entry - Calcuation) - This is the difference between 'Total Marks' and 'Bell Curve'.

'Weight' (Data Entry Required) - This is the level of importance you want for each test. For example, Participation is worth 5 out of 100 possible marks to a student's final mark in the course. The Final Exam on the other hand is worth 35 marks out of 100. Therefore, the Final Exam has 7 times more weight than Participation towards a student's final mark in the course. You will notice I manually entered the weights so the total on the bottom right of Figure 1.0 would be equal to 100. It doesn't have to equal 100 but I figured this would be the most common situation.

Let's move on the next worksheet called 'Grade Scale'. See Figure 1.1.

Figure 1.1

















Where:

'Percentage Needed for Grade' (Data Entry only required to change default levels) - The percent needed to acquire it's corresponding letter grade.

'Grade' (Data Entry only required to change default levels) - The symbol identifier for the percentage acquired in the course.

Both the Percentage Needed and and Grade symbols can be changed to suit the needs of your academic institution. For example, some schools require a percentage of 85% or higher to acquire an A letter grade instead of the current percentage of 80% as shown in Figure 1.1.

Let's go to the third worksheet called 'Students'. See Figure 1.2.

Figure 1.2























Where:

'Student ID' (Data Entry Optional) - Unique identifier for each student.

'Name' (Data Entry Required) - Name of the student.

'Grade' (No Data Entry - Calculation) - Letter Grade acquired by the student.

'Percent' (No Data Entry - Calcuation) - Percent acquired by student.

'Comments' (Data Entry Optional) - Notes for each student.

You do not have to enter 30 students. You can enter less than 30 students.

Now, let's move on to the final sheet called 'Marks'. See Figure 1.3
(Editor's Note: For the purposes of this tutorial, the 'Marks' worksheet has been formatted to fit this posting. )

Figure 1.3














Where;

The Test information in the pale yellow area on top is populated from the 'Tests' worksheet.

The Grade information is calculated using the 'Grade Scale' worksheet.

The Student information is populated from the 'Students' worksheet.

The only area for data entry in this worksheet is the 'Score' area.

Only numbers from 0 to 9999 are allowed in this area. This restriction is done by Data Validation.

You will notice some of the cells in the 'Score' area are highlighted. This is done automatically by conditional formatting.

Yellow highlighted cells indicate a perfect score for a test (Equal to the Actual Marks in the pale yellow area above).

Red highlighted cells indicate a score higher than the Actual Marks for a test. This could be a situation where bonus marks were earned by the student.

Green highlighted cells (Must be blank) indicates the student missed the test but is not penalized due to a legitimate reason for missing the test.

That's it. I hope this helps some teachers out there. Keep Excelling!