You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
[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.
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.
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:
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.
To manually calculate a student’s grade percentage, do the following:
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.
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!
Thanks for the gradebook. It'll be very useful! Is it possible to print the whole class set of reports in one go without having to select each pupil individualy?
@je_stather: We're so glad you find the template useful. I'm sorry to say that currently the template doesn't allow you to easily print all student summary reports at once. I've contacted Beth Melton, the Excel MVP who created the template, and we're figuring out how we can quickly add this functionality. Thanks for leaving your comment and helping us improve this template!
I like this template, it has a lot of potential. One thing I have came across that it could use is the ability to sort the assignment columns by the assignment date row. Is this an easy tweak?
@Super20G: Thanks for your comment. Unfortunately the template doesn't support that use. There's an assumption that the assignments will be added to the grdebook in sequential order. Since the Total Possible Points and the Student/Grading data are in two separate tables, it isn't an easy tweak to make.
Out of curiousity, can you explain why you would want to sort the assignment columns by date? Thanks for any clarification you can provide.
@je_stather: Thanks to your feeback, the template has been updated! You can now print the whole class set of reports with the click of one button.
Beth Melton, the Excel MVP who created the template, worked with fellow MVP, Daniel Ferry, to add this "Print All" functionality. I'll update the blog post soon reflecting these changes. But for now, you can use the same template download link to get the newest version.
Daniel Ferry wrote an excelent description of the vba used to print the individual pages for each student. This description it use to be at
What happened to the post???
Thank you for bringing the issue to my attention, Terry. You should now see Daniel Ferry's blog post, "When a macro won't cut it, try a VB script" available on the Excel blog.
Hello, I cannot get the student progress report chart to update the Student Points line with the new assignments I entered. The blue line (and dots/scores) only go through the original 'assignments' that were entered with the template. How does one update the formula to include ALL assignments (e.g. additional ones)? Thanks!
Hi Ruby, The Student Progress chart is designed to update automatically when a new Total Possible Points for an assignment (in row 3) and a matching assignment name and student grade (starting in row 5 directly below the Total Points) are entered. If you have verified you have both of these pieces then it’s possible a formula was inadvertently deleted in the template. As an alternative to troubleshooting the issue you are currently encountering, you may want to try downloading a fresh copy of the template.
hi their, I have just recently downloaded the template and cannot find if their is a function to excuse students from assignments or add bonus assignments that will not affect the other students in the grade book?
Same question as the previous user - how do I excuse a student from an assignment - if a new student moves in to my class part way through the school year, I cannot assign a grade of zero for assignments he was not present for. What is my workaround?
There isn’t a built-in way to exclude a student from assignments. However, you can manually modify the formula for the student’s percentage grade. That formula is:
=IFERROR([@[Total Points Earned]]/TotalPoints,"")
For example, if a student is excused from a total of 40 points then you modify the formula for that student only to:
=IFERROR([@[Total Points Earned]]/(TotalPoints-40),"")
You can also do the same for adding additional points.
To modify the formula:
1. Select the cell below the % column for the student you want to modify.
2. Click in the Formula bar and make the needed formula change.
IMPORTANT: Be sure to include parentheses around the TotalPoints calculation or it will result in an order of operations error. The revised portion of your formula should look something like this: (TotalPoints-40)
3. Press Enter to update the formula.
IMPORTANT: Pressing Enter may automatically update the formula for ALL students. This is a feature of Excel tables you’ll need to override. If the formulas for other students changed, do one of the following:
* Click the AutoCorrect Options button (lightning bolt) that display next to the cell pointer and then click “Undo Calculated Column”.
* Click the arrow to the right of the Undo button to display an Undo list. If the first entry in the list is “Fill” then click the item to undo the automatic filled formula.
Note that the first time you override an automatic calculated column in a table you shouldn’t need to continue to override it upon subsequent changes. Also, for those students with a different calculation for their percentage grade a small green triangle will display in the upper left corner of the % cell.