Free gradebook template for Excel 2010

re: Free gradebook template for Excel 2010

Beth Melton — Fri, 09 Nov 2012 15:54:34 GMT

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.

re: Free gradebook template for Excel 2010

mmbaragar — Wed, 07 Nov 2012 18:54:02 GMT

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?

re: Free gradebook template for Excel 2010

ausalllon — Sat, 13 Oct 2012 21:01:18 GMT

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?

re: Free gradebook template for Excel 2010

Beth Melton — Sun, 29 Apr 2012 17:41:58 GMT

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.

re: Free gradebook template for Excel 2010

travelgrrl2002 — Thu, 26 Apr 2012 17:53:14 GMT

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!

re: Free gradebook template for Excel 2010

Jennifer Bost — Tue, 07 Feb 2012 19:32:17 GMT

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.

re: Free gradebook template for Excel 2010

Terry Patteson — Tue, 07 Feb 2012 12:58:41 GMT

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

blogs.office.com/.../excel-2010-macro-versus-vba-script.aspx

What happened to the post???

re: Free gradebook template for Excel 2010

Jennifer Bost — Fri, 21 Oct 2011 19:09:16 GMT

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

re: Free gradebook template for Excel 2010

Jennifer Bost — Fri, 14 Oct 2011 18:52:14 GMT

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

re: Free gradebook template for Excel 2010

Super20G — Fri, 14 Oct 2011 14:42:23 GMT

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?