This is the first part of an article in a series of articles related to creating a software gradebook using Excel. This article is focused on how to create a gradebook that can calculate weighted grading averages. The gradebook we will create will be able to handle grading categories that contribute different amounts toward a student's final grade. If you are looking for a simpler grading solution that doesn't require using weighted grading categories, you can check out one of our previous articles to learn how to create a simpler software gradebook in Excel. If you decide you are not interested in creating a gradebook at all and are looking for someone else to do the work for you, you can try our gradebook software for free..
If you've decided you'd like to create your own software gradebook, continue reading. Here is what our final gradebook will look like.
First of all we'll open a new empty document in Excel and add a few headers for our students and our first grade category. We will also add 2 placeholders for our category name and category id. Later we will replace the placeholders with an actual category name and id.
Next we'll add a few more category sections for our additional weighted grading categories.
Now that we have a few grading categories, let's give them some names. Replace each #CatName placeholder with an actual grading category name. Homework, Quizzes, and Tests are the categories I chose, but feel free to use whatever you would like. Each category will need a unique category ID, so replace the #CatID values with 1, 2, and 3. Notice that I also added weights for each category. In my case, homework is worth 20, quizzes worth 30, and tests worth 50 toward the student's final grade.
Next let's add placeholders for an single grade.The placeholders are #Dt, #Grade, #Weight, and #Cat.
No class would be complete with just one grade, so let's add a handful more resulting in placeholders for nine distinct grades.
Here is a mapping for the grade placeholders:
|#Dt||date for the grade|
|#Grade||description of the grade|
|#Weight||how much the grade is worth|
|#Cat||one of our category ID values|
Now we can start to fill in the placeholders with actual grades.
For our complete example, I have filled in 3 homeworks, 3 quizzes, and 3 tests.
We have now added all of the headers to our software gradebook. This concludes part 1 of our article on creating a weighted gradebook in Excel. This is only one possible way of creating a software gradebook. Stay tuned for part 2 where we will improve our gradebook by adding formulas for automatically calculating our weighted grade averages.