Tracking Student Progress Excel Template (Free Download)

“No Macro used. No installation is necessary. Just download and start using.” – Siam Hasan Khan (Template Developer)

Created using Excel 2016 version

Download Template

License: Personal Use (Not for resale or distribution)


Let us know (in the comment box) your criteria or the problems that you’re facing while using this template. We shall update the template.

In this article, I will make a “tracking student progress” Excel template. By this template, you will be able to identify a student’s progress in every term. The overall performance of all the students will also be carried out by calculating the GPA difference between the achieved grade and targeted grade. The overall performance, the number of failed students and the number of passed students information will be plotted in the performance over time graph to see the progress of the students.

Overall Guideline

In this template, we have 5 worksheets. The first worksheet tells you how to work with the other worksheets, what are the notable things in between the worksheets. Look into the picture below for getting an overall idea about how things will work.

Tracking Student Progress Template

Grading System Worksheet

In this worksheet, I insert the data of Grades and grade points based on some numerical values. You can add your own grading system here. After completing the grading system table, two name ranges were created from the Name Manager under the Formulas tab which are the GradePoints and Grade.

Name List with ID & CGPA Worksheet

This is the worksheet where you put your student information. The student information contains his ID, Name and current CGPA. My table contains information of 23 students. You can have more than 20 students in your class. So, make a list of all the students. After getting all the information, I created two name ranges from the Name Manager which are the ID and List.

Grade and Number List Worksheet

This is the worksheet where you will find all the student information in detail. After inserting the IDs in the ID column, you will see the other information in the student information section gets automatically updated based on the student ID. The cells of ID column have a drop-down list option so that you can insert the ID easily. In this worksheet, you can only insert values in the first three Number columns other than the ID column. I calculated the Final evaluation result taking 20% from both Midterms and 60% from the Final Term. If you have only one midterm than you can edit the final evaluation result by editing the formula of the last Number column. Delete/insert the columns if necessary if you want to work with one/more midterms. After completing the final evaluation result you will see the number of passed and failed students in each term. These are generated using formulas. Suppose you want to check which students get “A+” after the final evaluation. For this, select the Grade beside the Highlight a student information after the Final Evaluation section. The rows will be highlighted with the green lines for the students who get the “A+” after the final evaluation.

Note:

  • Before editing the worksheet don’t forget to unlock it as it is locked by default. While it is locked you can only work with the ID and first three Number
  • Check the dropdown lists of your worksheet if you somehow edit the Grading System worksheet and Name List with ID & CGPA

Student Performance Worksheet

In this worksheet, you can check each student performance in each term by calculation the difference between Achieved Grade and Targeted Grade in the Performance (AG-TG) column. Conditional Formatting is applied in the Performance (AG-TG) columns so that you can easily track the performance change. The sum of this column will evaluate the total student performances every semester. It is calculated in the Overall Performance row. The maximum cells of this worksheet are linked with the Grade and Number List worksheet without the columns of ID and Targeted Grade. The cells of these two columns have drop-down lists and they are not protected. So, you can insert data only in these two columns while the worksheet is protected.

Note:

  • Before editing the worksheet don’t forget to unlock it as it is locked by default. While it is locked you can only work with the ID and first three Targeted Grade
  • Check the dropdown lists of your worksheet if you somehow edit the Grading System worksheet and Name List with ID & CGPA

Performance Over Time Graph Worksheet

In this worksheet you will get a Performance Over Time graph based on the overall student performance, the number of the total passed students and the number of the total failed students. These values are linked with the Grade and Number List worksheet and the Student Performance Worksheet. So, changing any values in these worksheets changes the values of this worksheet.

Conclusion

After reading this article you will get the idea about how this template will work. Don’t forget to download it. This is specially created for the teachers so that can easily calculate the results and evaluates the student progress in each term. Hope you will like this article. Please give us suggestion on how we can improve this template in the comment section and let us know your feedback.

Related Articles


Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
Hello!

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

2 Comments
  1. Reply amadupoulis12@yahoo.com'
    Pouliwe Amadu July 5, 2018 at 8:29 AM

    On tracking students’ performance, Is it not possible to use v lookup (referencing from the grading system sheet in the workbook) to automatically grade students’ score instead of using grade (for each student one after the other) created using data validation?

    • Reply
      Siam Hasan Khan July 5, 2018 at 10:40 AM

      Yes, it is possible. I insert the grades, grade points and number as most people want to see all of these 3 data. Here the students’ performance is calculated by differencing the Achieved grade and Targeted grade. You can insert the students targeted grade point instead of his targeted grade and calculate the differences between achieved grade point and targeted grade point to calculate the student`s performance.

    Leave a reply