Making a Grade Book with Excel
spaceroverview  design tips making it examples software tips references

 

Making It

Now it is time to create a class Grade Book that will compliment the grading method you have chosen to implement.

Choosing the Best Template

For this assignment you will have three partially developed templates to choose from.

  1. Click on the template link below that is best suited to your preferred grading method.
  2. Click Save on the Download File dialog box
  3. Save it to your H: drive (Save as dialog box change Save In)
  4. Be sure to give it a descriptive label (example: General Chemistry Gradebook.xls), and
  5. Reopen the file with Excel

Fill-in Templates:

Grades by Average

Grades by Points

Grades by Percentage

Customizing Your Excel Grade Book Template

Now that you have chosen a template, it is time to customize that template.

Personal and Class Information

Customizing your Grade Book template is easy. The first thing that you will want to do is to change the generic labels for your college, name, and class to reflect your custom information. To change any of these, place your mouse pointer over the text to be changed and click the left mouse button. Delete the existing text and replace it with your information.

Grading Scale

You can customize the grading scale included with your Grade Book template by changing the percentage values in the row labeled "Average." The percent value over each letter grade represents the lowest possible percentage of total points that a student can earn and receive that grade in the course. The same system applies to the values for the GPA.

To change the default values in the Grade Book template, click in the table cell that contains the value to be edited. Delete the current value and enter the lowest percentage value that would earn a student the corresponding letter grade.

If you do not grade on a +/- scale, you can delete these letter grades and their corresponding "Averages and GPA's." However, this must be done with care. This table is used to automatically calculate letter grades and GPA's in the student table. If you do not delete columns properly, the Grade Book template may not be able to correctly calculate letter grades or GPA's.

To eliminate a letter grade, use your mouse to select the three cells containing the letter grade and its corresponding average and GPA. You can do this by holding down the Shift key on your keyboard and then clicking each of these cells. Next, open the Edit menu at the top of the window and select Delete. In the Delete window that opens, make sure that you click the button next to "Shift cells left." Then click on the OK button. The grade that you delete should disappear and all of the cells to the left of it in the "Grade & GPA" table should shift left to fill in that space.

Adding Assignments and Values

For this assignment you will be adding ten assignments to your Grade Book For each assignment, you will need to enter a name and a grade value. The grade value reflects the assignment's value relative to the final grade. What these values are will depend on the method you have chosen to calculate your final grades.

Adding Students to the Roster

To add students to your roster, click in the first empty cell in the "Student Name" column, then, enter the student's name (if you enter it last name first, you will be able to sort your roster alphabetically once all of the students are entered). Next, enter the student's ID number in the cell next to his/her name. You can enter up to forty students on the roster for this assignment your roster will include 14 students.

When you are done entering students on your roster, you will be able to sort the list by student name or ID number.

Overriding Automatically Calculated Grades

While you will not be using this function for today's assignment, it may be useful to you when you use this Grade Book in your classroom. There are several entries in the Grade Book template that are calculated automatically for you (average or score, letter grade, and GPA). Occasionally, it may be necessary for your to override the automatic calculation; for instance, you might choose to change the letter grade of a student who worked exceptionally hard but missed the next highest grade by a narrow margin.

To override a value in any automatically calculated cell, simply click on that cell, delete the current contents, and enter the value that you wish to have displayed. Keep in mind that once you have done this, the formula that was contained in the cell will be erased.

Content Requirements

Your Grade Book should include the following:

  • Customized Course/Instructor Information
    • Teacher's name
    • Course name/Grade level
    • School term
    • School name
  • Customized Student Information
    • Students' names
    • Students' ID numbers
  • Customized Assignment/Grade Information
    • Assignment names and values
    • Assignment averages
    • Highest and Lowest assignment scores
    • Student grades on each assignment
    • Student averages (%)

Be sure to include all the data provided in the "sample data sheet." You will need to rearrange the assignments and the data in the columns into a more logical order. They should appear in the following order:

    1. Homework 1
    2. Project 1
    3. Test 1
    4. Homework 2
    5. Project 2
    6. Test 2
    7. Homework 3
    8. Project 3
    9. Test 3
    10. 9-weeks exam

Technical Requirements

Use the data provided in the sample data sheet to complete the Grade Book assignment in Excel.

Your Grade Book should meet the following technical requirements:

  • The font color of all Course/Instructor information must be changed from black to another color of your choosing. This includes:
    • Teacher's name,
    • Course name/Grade level,
    • School term, and
    • School name.
  • Column headings should be bolded and have an applied background color (other than white).
  • Data should be sorted so that the students' names are arranged alphabetically. Hint: Be sure to include the students' IDs and grade data along with the students' names so that student information will move with the student.
  • Formulas should be generated and entered in the "Class Summary" section of the Grade Book that calculate each student's average and class averages for each assignment.
  • Formulas should be generated and entered in the "Class Summary" section of the Grade Book that calculate the highest and lowest scores for each assignment.
  • Unused rows and columns should be deleted.
  • A print area should be set so that the students' names are not printed.
  • A "column" graph charting the class averages for each assignment should be created and inserted in the spreadsheet.
    • Be sure to include a Chart Title and labels for the X and Y axis.
    • Move the chart directly below your worksheet data so your Grade Book will be visible.

After you have finished creating and editing the document, you will:

  • save the finished document to your folder on NTNET (H: drive), then
  • print out a copy of the Grade Book and chart to hand in to your instructor (be sure the print area is set so that the students' names are not printed). Don't forget to put your name and Lab Section on the print out.

Next Section a