Student Grade Sheet Excel Assignment

[Author’s note: Daniel Ferry, an Excel MVP, recently updated the Excel 2010 gradebook template. Now you can easily print all student reports with the click of one button. This new button required VBA script, which creates macros in the file. You’ll want to click Enable Macros when prompted during download. These macros have been tested and validated by Microsoft.]

Earlier in the year I worked with Beth Melton, an Excel Microsoft Most Valuable Professional (MVP), to create a super easy and visual gradebook template in Excel 2010. It’s built for teachers with little to no Microsoft Excel experience. In fact, all of the grade calculation formulas are taken care of for you. We built the template in Excel 2010, because of all the new 2010 data visualization features. It even includes a printable student progress report!

This short video shows you how to download, use, and customize the template. Or, if you prefer to scan through instructions, look below for the step-by-step procedures.

Download the template

1.    Open Excel 2010.
2.    Click the File tab.
3.    Click New.
4.    In the Office.com Templates search box, type gradebook and then click the search (magnifying glass) button.
5.    Click the Gradebook with printable student progress report template.

 

6.    In the information pane, click Download. The template will open as a new Excel spreadsheet.
7.    Click File and then click Save As to save a local copy of this file.

Note: You can also download this template from www.office.com/templates.

Delete placeholder data

After downloading the template, you’ll  see three rows of student names, three columns with assignment names and grades, and some helpful tips in light brown callouts. We added the placeholder data to the template to help you see what it should look like. For example, in the screenshot below notice how the letter grade and percentage is calculated based on the total points possible and the individual student’s total points earned.

To customize the template, you’ll need to:

  • Read and then delete the light brown callouts.
  • Fill in your own data, such as the name of your class, your name, and the names of your students. To add more names to the spreadsheet, you’ll want to insert more rows into the Excel table (instructions below).
  • Fill in assignment information, such as the assignment name, due date, and total possible points.  To add more assignments to the spreadsheet, you’ll want to insert more columns into the Excel table (instructions below).

Important: Each assignment has a matching entry in the Total Possible Points table.You must keep data in at least one student name row, one assignment name column, and one Total Possible Points cell populated with data to preserve existing formulas. 

Add student name rows to the template

  1. Click the row number next to the last student name in the Excel table.
  2. Drag the cursor down to the row number you want to insert. For example, if you have 26 students in your class drag the cursor from row 7 to row 30.
  3. Right-click the highlighted rows and select Insert.

Add assignment columns to the template

  1. Click the last assignment name in the Excel table to select that cell and then drag your cursor to the right and highlight the columns you want to add. For example, to add 3 new columns to the table, click Assignment name3 to select that cell. Then drag and drop your cursor to column L.
  2. Right-click the highlighted rows, click Insert and select Table Columns to the Right.

Manually calculate a student’s grade percentage

To manually calculate a student’s grade percentage, do the following:

  1. In the row for the student, select cell under the percentage (%) column. The formula will appear as: =IFERROR([@[Total Points Earned]]/TotalPoints,””)
  2. Replace TotalPoints in the formula with the new numeric entry and press Enter
  3. If this is the first time a manual change has been made to the percentage entries in the workbook, click Undo to reverse the automatic calculated column. The Undo step is not required for subsequent changes.

Note:  All students who have manual grade percentage calculation will have an error indicator (green triangle) in the top left corner of the cell. It’s a good idea to leave the error indicator as a visual confirmation of the change. Note that the error indicator will not print.

How to print a student progress report

1. From the Student Summary workbook, click cell B8 and use the drop-down arrow to select a student’s name. The bar graph will update with that student’s grade data.

2. Click the File tab on the ribbon. 

3. Select the Print tab, and then click the Print button.

If you want to quickly print a report for every student in your gradebook, open the Student Summary worksheet. In the Student Summary worksheet, click Print All.

I hope this gradebook template saves you time. Have a great 2011- 2012 school year, teachers!

–Jennifer Bost

Assessment Types - Points

Recently redesigned for extra simplicity and ease of use. Get the gradebook:

Assessment Typeshas fixed percentages for all the homeworks, classworks, tests, etc
Directlyhas all assignments directly go towards the running average
By Pointsassignments each out of so many points; more points = bigger weight
By Percentsassignments each out of 100%; you can still weight assignments

With the gradebook you can:

  • Calculate the running average
  • Chart distributions of scores
  • Print grade printouts for one student or everyone
  • Weight grades
  • Customize curve, excuse, and allow retakes
  • Instructions in the grade tracker

For Windows Excel 2007+ or Mac Excel 2011+. The pretty sparkline charts show in Excel 2010+.

Please like and share if this is helpful!

The workbooks are macro (code) free. You can safely ignore "formula references blank cells" warnings.

Charts and Grade Distributions

Easily graph distributions and chart scores. There's a prebuilt distribution for the overall running average:

The Printouts

The gradebooks have an easy-to-customize printout you can print for everyone or just one student. See video directions.

Be Creative!

You can chart scores across gender, race, absences, or any other custom category.
  • Try a boys vs. girls challenge.
  • Use data to get $$ to help your low income minority students.
  • Show how showing up and doing work relates to outcomes.

Video Tutorial Help

See older versions

Older Version with Mastery

This older version (for Excel 2007+) tracks both grades and mastery. Mastery is the percent of learning goals for which students have met standard. There are also links for other older versions (essentially all grading "directly" but also include summary scores for each assessment type.) Note that grade printouts have been made much easier in the newest versions at the top of this page.

Excel 2007: Gradebook By Points | Gradebook By Percentage
Excel 2010: Gradebook By Points | Gradebook By Percentage
Older Version with Mastery: Gradebook by Points



I value your privacy. You can read the story of this gradebook. License. Last updated: July 2015.

0 thoughts on “Student Grade Sheet Excel Assignment”

    -->

Leave a Comment

Your email address will not be published. Required fields are marked *