Using Excel for Your Grade Book

Download class list

Use template for weighted grading

Make your own Excel spreadsheet

Excel Math 101 (some basic math that is used in Excel)

 

Download class list from GC Online II into Excel

• Find your class list in GC Online II under the Faculty tab, then "View Roster."
• Click on "Reformat as a printable page."
• Using "File, Save as," save this file in your M: drive
  • as a  ______.asp file if using Netscape
  • as a ______. html file if using Explorer

Two options now -- you can create your own Excel spreadsheet or you can use the weighted spreadsheet template.

 

Use the spreadsheet template for weighted grading

Inserting the class list into the template:

• Delete the columns not needed (you want only ID, name, email, class, major)
• Highlight these 5 columns and copy.
• Go to the template, click on the first cell under ID (A7) and paste.
• If background of class list is colored and you want it white, select the area, right click-- reformat cells - pattern - choose "no color"

Using the template:

• The template is a general purpose spreadsheet for weighted grading.
• It accomodates up to 66 students in a class, and up to 17 graded items.
• On lines 81-96 of the spreadsheet are some specific notes for entering data and publishing the results to the web. Ask Paul Meyer Reimer if you have questions.

 

Inserting the class lists:

• Open the class list file in Excel and "save as" an Excel file.  (This puts all of the data into individual cells in Excel.)
• Delete columns not needed
• Size columns
• Show gridlines - (File - Page Setup - Sheet)
• If background of class list is colored, select area, right click-- reformat cells -pattern - choose no color
• Add rows as needed at the top
• Add and rename more worksheets as needed
• Copy and paste name column on other worksheets, keeping student data in the same rows

Set up columns or worksheets for types of scores (quizzes, tests, homework, participation, etc.)

• Label columns         
• Add data
• Add formulas
• Format cells for decimal or percent
• Add summary statistics (min, max, mean, std dev, etc.)
• Copy and paste link from worksheets on to summary sheet
• Make exceptions
• Include weightings
•  
•  
•  

Getting Printout

• Page setup
• Print area
• Hiding columns or rows

 

Excel Math 101

Excel uses computer syntax for operands:

• Division                           /
• Multiplication              *
• Addition                         +
• Subtraction                           –

Excel uses the standard arithmetic order of operations which means:

• Multiplication and Division before Addition and Subtraction
• (mnemonic:  MiDAS  is the Golden Rule of Math)
• Using parenthesis can change the order.
• (2 + 6) / 2 = 4 while 2 + 6 / 2 = 5

To create basic formulas:

• =  begins a formula, e.g.,
  • =b5+b2
  • =d5*e18
  • =e14/a5

Predefined formulas/functions (See "Insert - functions" for a listing)

• SUM – calculates the sum of the cells, SUM(b5:b10)
• AVERAGE – calculates the average of the cells
• MAX – Displays the largest value of the named cells

Relational Formulas:

The formula stays the same when copied – new cell references that are in the same relative position are substituted for previous cell references.

Absolute Formulas:

The formula always refers to a specific cell address, even if the formula is copied.  You must place $ in front of row letter and/or column number.

 

 

updated 1/13/2002