Student Grade Sheet Excel Assignment Worksheet

[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

A2: Spreadsheet

Proportion of term grade 10%, Tuesday February 14 at 5 PM

For this assignment you are to start with the raw grade data in the: [Initial spreadsheet].  The starting sheet contains the fictional grade information for a CPSC 203 class and includes grade points (which range from 0.0 - 4.0) for all 6 course components (Columns C - I) as well as identifying information for the student (ID and faculty). Blank cells indicate that the student didn't attempt a component while zeros indicate that student attempted a component but did so poorly that no credit is to be awarded. The grades are listed in ascending order of student identification number and this ordering cannot be changed. There are two worksheets: 'grades' and 'cutoffs' the former containing the raw grades for a lecture and the latter the cut-offs for grade scales. You should not change this division (e.g. move the cut-offs information into the grade sheet).

You will start this assignment with a grade point of zero. As you implement the features listed below your grade for this assignment will increase by the amounts specified in the italicized text in the brackets. Unless otherwise specified the row/column references refer to the 'grades' worksheet.

  1. Creates an Excel formula (in "Column J") to calculate weighted term grade point for each student. The value is each component is show below. You may want to look at the first set of notes "Introduction to the course / administrative information" if you don't know how calculate a weighted term grade point. You should not calculate the term grade point by using a pre-defined Excel formula e.g. Sumproduct(), instead you should define the formula by typing all the calculations into Excel yourself. (This feature is worth 0.3 grade points)
  • Partial A1 proportion of term grade = 5%
  • A2 proportion of term grade = 10%
  • A3 proportion of term grade = 10%
  • A4 proportion of term grade = 10%
  • Partial A5 proportion of term grade = 5%
  • Midterm proportion of term grade = 25%
  • Final exam proportion of term grade = 35%
  1. Augments/modifies the table in the 'cutoffs' worksheet  (original information specified in Columns 'A' and 'B') so that it can be used with the VLOOKUP() function (Feature #3) to map term grade points to term letter grades. (This feature is worth 0.3 grade points)
  1. Using the modified table from #2, the letter grade for each student will be determined and shown in  Column K (grades worksheet) using VLOOKUP(). (This feature is worth 0.6 grade points)
  1. Uses the Excel AVERAGE() function to calculate into Row 51 ('grades' worksheet) the average grade for: each course component (assignments and exams), term grade point, the latter of which was calculated in #1. (This feature is worth 0.4 grade points)
  1. (Repeats #2 & 3 but with a different set of cut-offs). Modifies the second data table in the 'cutoffs' worksheet (original information specified in Columns 'F' and 'G') and performs a 'lookup' of this data. Similar to Feature #2, for Feature #5 you modify the second table  into a form that can be used by another employment of VLOOKUP(). The new cut-offs and the result of the alternate cut-offs (number occurrences of each term letter grade) should show the same information about cut-offs  (minimum grade point needed for each letter grade) as the original table but clear labels will be needed to clearly differentiated the two cut-off tables to the viewer. You should build the tables yourself rather than using the automated 'What-If Analysis' option built into Excel (see image to right). (This feature is worth 0.9 grade points)
  1. Uses the COUNTIF() function to count the number of occurrences of each term letter grade with both cut-off scales ('cutoffs' worksheet: first set of cut-offs specified in Column A & B  while the second set is specified in Column F & G. The count of the occurrences must be displayed in the 'cutoffs' worksheet (because the two set of grade offs are stored here it's more convenient for the viewer to see the effects of the different cut-offs here as well). (This feature is worth 0.8 grade points)
  1. Uses two 'column charts' (see the icon to the right) to display the number of occurrences of each term letter grade (choice of graph is appropriately chosen and clearly labeled) for both cut-off scales. (This feature is worth 0.4 grade points). There should be a separate column chart for each of the cut-off scales so the reader can clearly distinguish the effect of the two scales.
  1. Uses a Pivot table to display the average term grade point for the 6 faculties . (This feature is worth 0.3 grade points)
  1. Bonus feature: If you are awarded perfect marks with Features #1 - 8 and no style penalties then you can be awarded an assignment grade of 4.0. Completing the bonus feature can allow for a grade point over 4.0.

You are to work with a spreadsheet that includes personal contact information for several people. Each row of Column A includes the contact information for a single person (e.g. A1 is all the information for Peter Griffin). Each contact will have an address field, a name field and a title field. Each field will be separated by a colon: <address>:<name>:<title> Each field can consist of zero or more characters. Example data is included in the [unformatted spreadsheet]. Column A ("Examples of data") includes some samples of raw data in this format. Your program should handle any data as long as it follows the previously specified format. However you can assume that the data in column A will never exceed 256 characters in length including the two colons. Since you can rely on the two colons always being included in the raw data then width of data in Column A will range from 2 - 256 characters. Column B ("Title and name") will include: the title (characters after the second semi-colon in the string in Column A) and combine it with the name field (characters between the first and second semi-colon in the string in Column A). The final string in Column B should include a space between title and the name. This process of extracting and recombining substrings must be done using the built in features of Excel and must work with any raw string in this format. Enter the function or combination of functions that you think will properly process the data into the first four rows of Column B. You will receive no credit if your spreadsheet only handles some cases or if you manually enter the data into column B. (This feature is worth 0.3 grade points)

Style and formatting deductions (your minimum grade point is 0.0)

  • Formula addressing: Inappropriate use of absolute vs. relative formulas will result in a penalty of 0.1 grade points (applied once for all occurrences of when the wrong type of formula was applied and not for each occurrence).
  • Colors: They are used to visually highlight the highest and lowest term grades (A+ and F). You should use the conditional formatting feature so you don't manually change the font colors and so that colors will automatically update as grades change. Alternatively you can map colors another category of information (e.g. faculty). Penalty for omitting the use of colors, mapping too many colors (count as a rule of thumb a half a dozen distinct colors as max not counting shades of colors) or choosing colors with insufficient background/font contrasts : -0.1 penalty for any occurrence
 
  • Contrast: insufficient contrast between headings and grade information: -0.1 penalty for any occurrence
 
  • Repetition: inconsistencies occur with text in headings or the cells with grade information (or both): alignment, fonts, font effects, font sizes: -0.1 penalty for any occurrence
 
  • Proximity: items that are related are located nearby each other, unrelated items are separate -0.1 penalty for any violation of this principle
  • Fonts and font effects: The number of fonts and font effects/sizes exceed: 3 (max of 3 types of fonts, max of 3 font effects/sizes):  -0.1 penalty either maximum is exceeded

Points to keep in mind:

Comments

Leave a Reply

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