Grading and Teaching Articles

How to Create a Weighted Gradebook in Excel - Part 1

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.

Excel Gradebook With Weighted Grading Categories

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.

Excel Gradebook Adding Student Headers

Next we'll add a few more category sections for our additional weighted grading categories.

Excel Gradebook Category Placeholders

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.

Excel Gradebook Weighted Category Definitions

Next let's add placeholders for an single grade.The placeholders are #Dt, #Grade, #Weight, and #Cat.

Excel Gradebook Grade Definition

No class would be complete with just one grade, so let's add a handful more resulting in placeholders for nine distinct grades.

Excel Gradebook Grade Placeholders

Here is a mapping for the grade placeholders:

Placeholder Mapping
#Dtdate for the grade
#Gradedescription of the grade
#Weighthow much the grade is worth
#Catone of our category ID values

Now we can start to fill in the placeholders with actual grades.

Excel Gradebook Replace Grade Placeholders

For our complete example, I have filled in 3 homeworks, 3 quizzes, and 3 tests.

Excel Gradebook Completed Grade Definitions

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.

Excel Gradebook Completed Headers