Create a Gradebook on Microsoft Excel: Make a Weighted Points Grade Sheet

This article was co-authored by wikiHow staff writer, Kyle Smith. Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.

This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.

This article has been viewed 238,372 times.

Microsoft Excel is a great program for creating simple grade books! Using a spreadsheet (and a couple formulas) will reduce the time it takes to record and calculate grades. You don’t need to be familiar with Excel to follow our quickstart guide. This wikiHow article will walkthrough how to create a point-based grade book on Microsoft Excel.

• Create columns for student names, assignment grades, total points, possible points, numeric grade, and letter grade.
• Use the SUM function to add each student’s assignment points, then use the “/” division operator to divide their total earned points by the total possible points.
• Weight assignments by assigning more or less points.

Create a New Workbook

• 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”

Create the Layout

• 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.

Calculate Grades with Formulas

• 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.

Create a “total” column header. This column is where you’ll calculate a student’s total points. Place this in the column directly to the right of the rightmost assignment in your grade book.

• 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

Press Enter to confirm the formula. A summation of the first student’s points should appear. For example, if they scored 10 points on five assignments, the number 50 should appear.

• 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.

Create a “grade” column directly to the right of the “possible points” column. This will contain the percentage grade for each student.

• 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.

Assign Letter Grades

• 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.

Weight Assignments

• 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.

Expert Q&A

Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.

You can easily set up another class section by right-clicking your first grade sheet and making a copy in the same workbook. This will keep all of the formulas. Just change the student names! If it's a different course, you'll need to change the assignments and total possible points as well.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow

Always give your grade-book a title, by clicking on the "file" tab, choose "Save As", in the Save As window, choose a location and type a name for your document. Press "Save" when ready to save.

To find out what operating system your PC has, press "Start", right click on "Computer", scroll down and click on "Properties", a system dialog box will appear with basic information about your computer.

You Might Also Like

How to Merge Cells in Microsoft Excel: A Quick Guide

How to Password Protect a Microsoft Excel Spreadsheet

How to Use If‐Else in Microsoft Excel: Step-by-Step Tutorial

Unhide Columns in Excel" width="342" height="184" />

How to Show Hidden Columns in Microsoft Excel & Unhide Column A

How to Insert and Delete Rows in Microsoft Excel: Quick Guide

4 Easy Ways to Add Links in Microsoft Excel

How to Fix Ethernet Doesn't Have a Valid IP Configuration on Windows

How to Create an Inventory List in Microsoft Excel: Step-by-Step Guide