grade assignment in excel

PRO Courses Guides New Tech Help Pro Expert Videos About wikiHow Pro Upgrade Sign In
EDIT Edit this Article
EXPLORE Tech Help Pro About Us Random Article Quizzes Request a New Article Community Dashboard This Or That Game Forums Popular Categories Arts and Entertainment Artwork Books Movies Computers and Electronics Computers Phone Skills Technology Hacks Health Men's Health Mental Health Women's Health Relationships Dating Love Relationship Issues Hobbies and Crafts Crafts Drawing Games Education & Communication Communication Skills Personal Development Studying Personal Care and Style Fashion Hair Care Personal Hygiene Youth Personal Care School Stuff Dating All Categories Arts and Entertainment Finance and Business Home and Garden Relationship Quizzes Cars & Other Vehicles Food and Entertaining Personal Care and Style Sports and Fitness Computers and Electronics Health Pets and Animals Travel Education & Communication Hobbies and Crafts Philosophy and Religion Work World Family Life Holidays and Traditions Relationships Youth
Browse Articles
Learn Something New
Quizzes Hot
Happiness Hub
This Or That Game
Train Your Brain
Explore More
Support wikiHow
About wikiHow
Log in / Sign up
Computers and Electronics
Spreadsheets
Microsoft Excel

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

A simple way to record your students’ grades

Last Updated: August 26, 2024

Create a New Workbook

Create the layout, calculate grades with formulas, assign letter grades, weight assignments, expert q&a, things you'll need.

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 viewed 244,082 times. Learn more...

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.

Things You Should Know

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.

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 .

Step 4 Add class information to your first sheet.

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”

Step 1 Start creating the grade book 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!

Step 2 Enter the numbers of the section’s students.

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.

Step 3 Enter the names of the section’s students.

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.

Step 1 We’ll use the SUM function to find each students’ total 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

Step 4 Press Enter to confirm the formula.

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.

Step 6 Create a “possible points” column directly to the right of the “total” column.

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.

Step 7 Create a “grade” column directly to the right of the “possible points” column.

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 .

Step 1 Create a “letter” column directly to the right of the “grade” column.

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.

Step 1 To weight assignments, change the possible points for each assignment.

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.

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. Thanks Helpful 0 Not Helpful 0
Refer to Excel's extensive "Help" menu when in need. It offers tools on creating statistics of data. Thanks Helpful 0 Not Helpful 0
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. Thanks Helpful 0 Not Helpful 0

Be sure to confirm that the equations you created for your grade book are calculating correctly. Thanks Helpful 4 Not Helpful 1
Make sure you save your progress throughout to prevent losing information as you work. Thanks Helpful 2 Not Helpful 1
Always save a backup of your grade book and maintain hard copies. Thanks Helpful 1 Not Helpful 2
Computer with Windows 7, XP, or Vista
Microsoft Office Excel 2010

About This Article

Send fan mail to authors

Is this article up to date?

Featured Articles

Make Your Android Screen Black and White or Grayscale

Watch Articles

Terms of Use
Privacy Policy
Do Not Sell or Share My Info
Not Selling Info

wikiHow Tech Help Pro:

Level up your tech skills and stay ahead of the curve

Learn to automate and grow your business with spreadsheets

Error Troubleshooting
Free PDF Cheat Sheet
Ultimate Cheat Sheet spreadsheet
Microsoft Excel Templates
Google Sheets Templates
Full dashboards course
Basic dashboard tutorial
Hire an expert
All Excel Lessons
Excel Templates
Excel Formulas
Excel Formatting
Excel Error Troubleshooting

Excel Gradebook Templates (Points + Percentage)

Top image for the Microsoft Excel gradebook templates, points-based + percentage-based by SpreadsheetClass.com

If you are looking for a Microsoft Excel gradebook that will help you easily record and calculate grades for your students, then you will find exactly what you need on this page!

The Excel gradebook templates will allow you to keep track of your class grades in a very organized and efficient way. Spreadsheets make it fast to enter student attendance which is why when I was an academic coach I used spreadsheets to track my student grades, as well as every other type of student data that I could get my hands on.

This page contains the Excel gradebook templates, which were created specifically to be used in Microsoft Excel. Click here to get the Google Sheets versions of the gradebook templates .

Points based + percentage based version

There are two different gradebook templates that you can choose from, depending on whether you want to record points, or percentages. The points-based version allows you to enter the points per assignment, and then automatically displays the percentages on a separate tab. With the percentage-based version, you can directly enter the percentages for each assignment.

Points based (with Automatic percentage tab) (.xlsx)

This version of the gradebook template will allow you to enter the points possible & points earned on one tab… and it has an additional tab that will calculate the percentage of each assignment for you.

Percentage based (.xlsx)

This version of the template allows you to record the percentages of each assignment.

Click here to see the attendance templates for Excel .

Excused assignments

In the template you will have the option to mark assignments as “Excused” in the points-based gradebook template. If you have a student that you would like to excuse an assignment for, simply enter the letter “E” into the appropriate cell for that assignment / student, and that assignment will not factor into the calculations.

Gradebook color coding

Both templates have automatic color coding for the average grades of each student (Column B), and the average grades for each assignment (Row 1). This conditional formatting will allow you to easily identify students who are struggling, or assignments that may have been too difficult or too easy.

Points-based gradebook template for Excel

Get the template (.xlsx)

In this template, you can set the “Points Possible” for each assignment, and then record the points that were earned for each student, on each assignment. After entering the points possible and the points earned, the total points earned for each student/ assignment will calculate, and so will the average percentage.

In this template you can excuse a student from any assignment that you want, by entering the letter “E” for the score. That assignment for that student will not factor into the calculations.

You will also be able to see the overall class average at the top.

Instructions:

Enter student names in column A
Enter assignment names in row 4
Enter the points possible for each assignment in row 3
Enter the points earned for each student, on each assignment
Optional- Click the second tab to view assignment percentages

Example of the Excel points based gradebook template

This points-based template also has a second tab that doesn’t require any editing, and will automatically calculate the percentage of each individual assignment for you, as shown in the image below.

Example of the percentage per assignment tab on the points based template (Tab 2) for Excel

As you can see in the image above, the second tab of the percentage-based template shows the percentages that are associated with each score you entered in the form of points (on the first tab).

The total number of students in your class / in the template, will display at the top of the sheet.

This template was created by and is owned by Corey Bustos / SpreadsheetClass.com

Percentage based gradebook template for Excel

In this template, you will be able to directly record the percentage that is earned for each assignment. Whether you are calculating the percentages yourself or if you have a learning platform that reports percentages directly, this will allow you to record the percentage based scores for your students.

After entering the percentages earned for each assignment, the average score for each student, each assignment, and for the class overall, will calculate automatically.

Enter assignment names in row 3
Enter the percentage earned for each student, on each assignment

Example of the Excel gradebook template (percentage based version)

The total number of students on the sheet will display at the top of the sheet.

I hope that you enjoy these templates!

Click here to see more Microsoft Excel lessons .

Or check out the Microsoft Excel templates .

Google Sheets Gradebook Templates (Points and Percentage)
Google Sheets Templates for Teachers
Free Microsoft Excel Templates
Attendance tracker templates for Microsoft Excel
24 of the Best Free Google Sheets Templates
Attendance tracker templates for Google Sheets

Welcome to Spreadsheet Class!

My name is Corey, I have been building spreadsheets for companies for over 7 years, I was a data specialist at an online school for 5 years, and I created this website to help people learn how to use spreadsheets.