Siam Hasan Khan
“No Macro used. No installation is necessary. Just download and start using.” – Siam Hasan Khan (Template Developer)
Created using Excel 2016 version
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 t
Percentage Progress Bar in Excel (3 Suitable Ways)
Template, you will be able to monitor 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 the 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.
Read More: How to Show Percentage Progress Bar in Excel (3 Suitable Ways)
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.
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 is 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 about 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 is 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 besides 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 the first three Number
- Check the dropdown lists of your worksheet if you somehow edit the Grading System worksheet and Name List with ID & CGPA
Similar Readings
- Excel To Do List with Progress Tracker (4 Suitable Examples)
- How to Create a Progress Tracker in Excel (3 Handy Ways)
- How to Track Attendance in Excel (with Detailed Steps)
Student Performance Worksheet
In this worksheet, you can check each student’s 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 the 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
Read More: Progress Bar in Excel Cells Using Conditional Formatting
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 an 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 suggestions on how we can improve this template in the comment section and let us know your feedback.
Related Articles
- How to Create a Progress Bar in Excel (3 Easy Methods)
- How to Make Progress Chart in Excel (2 Simple Methods)
- How to Track Project Progress in Excel (Download Free Template)
- How to Track Multiple Projects in Excel (Download Free Template)
- How to Show Percentage Progress Bar in Excel (3 Suitable Ways)
- How to Make a Progress Monitoring Chart in Excel (with Easy Steps)
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?
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.