Create A Gradebook On Microsoft Excel Easily Track Grades

Alternatively, there are free templates for grade books online! Click More templates on the home page to search for “grade book” templates. You may see different templates depending on what version of Excel you’re using. The web app version of Excel has two grade book templates, one for points-based grading and another for percentage-based grading.

For more general spreadsheet info, check out our guide on making a spreadsheet in Excel.

Double click “Sheet1” at the bottom window. “Sheet1” should be highlighted. Type a name for the sheet. For example, “Section 1. ” Press Enter.

Click cell A1 to select it. Type the instructor’s name and press Enter. This will move your selection to A2. Type the class name in A3. For example, “World Geography. ” Type the section number and meeting time in A4. Enter the term in A5. For example, “Fall 2022”

This guide will cover one way to arrange your grade book. However, there are infinite ways to format an Excel spreadsheet. Try different templates and formats to find what works best for you!

Select A7 and type “Student Number. ” Row 7 will contain the column headers for each type of information you have in your grade book. Select A8 and type the number 1. Press Enter to enter the number in the cell and move down one. Type the number 2 in A9. Click and drag the cursor from A8 to A9. Both cells should be highlighted with a box around them. Hover your cursor over the lower right corner of the box until the cursor becomes a plus +(this is called the fill handle). Click and drag until you have a list of numbers for each student in your section.

Select B7 and type the column header “First Name. ” Select C7 and type the column header “Last Name. ” Type in the students’ first and last names in columns B and C.

You’ll insert the students’ assignment grades under each column. Note: For this guide, we’ll use a point-based grading structure, meaning each assignment is worth a certain amount of points.

Note: Make sure to check your spreadsheet calculations by doing a few calculations by hand. This is a great way to catch any errors in your spreadsheet. Read more about the SUM function in our complete guide.

Alternatively, you can type in the range instead. For example, if you have 5 assignments in columns D through H and the student names start on row 8, you’ll enter D8:H8

Select the cell with the total summation formula. Click and drag the fill handle (the square in the bottom-right of the selection) down to the last student to automatically apply the sum formula to each row. Using our earlier five-assignment example, let’s say there are 10 students. The second student summation should now have a range of D9:H9, the third student D10:H10, down to the last student with D17:H17.

Enter the possible points in each cell below the header. For example, if each assignment is out of 10 points, and there are five assignments, the possible points will be 50.

The “/” symbol will divide the total cell by the possible points cell, giving you the student’s grade as a value under 1. For example, if a student earned 45 total points out of 50 possible points, their grade will be 0. 90 (90 percent).

You can change the grades to percentages by selecting the grade values and changing the data type to “percentage. ” Find this by navigating to Fields tab > Properties group > Data Type.

Compare the student’s grade to your grading structure. Insert the letter associated with that student’s grade. For example, if an “A” is 89 to 100 percent and a student scores a grade of 93, place an “A” in the “letter” column.

For example, a 20-point assignments has double the weighting of a 10-point assignment. To weight entire categories, make their points add to the percentage of the total grade you want the category to represent. For example, if your course has 100 total points and you want five quizzes to represent 20 percent of the total grade, each quiz should be worth 4 points.