Excel Tips for Gradebooks
Pat Pettit
Millikin College

 When using Excel as a grade book there are many ways to set up your spreadsheet.

 Of course there will be the usual cells with names listed.  Make sure you put the last name in one column and the first name in another column.  That way you can sort the spreadsheet alphabetically.

Column headings should reflect the assignments.  This will also give you the ability to sort the sheet by any column. The data can be sorted by:

1. Select all the data.  (The reason you need to select all the data is to make sure the rows remain intact when you sort by column.)

2. Click on Data on the Menu, then Sort, and then select the Columns that you want to sort by.   Say you want to sort by Last name and then First name, you select them in that order.

Weighted Grading and Using and an Absolute Reference in Your Formula

  If you want to weight grades you can use percentage that you insert into a cell, say the cell above the column of grades to be weighted, and then use a formula with an absolute reference that will use that weight in all the formulas you replicate down the column.

 An absolute reference is preceded by a $.  This will make that column or row reference remain unchanged when you copy the formula to other cells.  You can put a $ in front of the column (A,B,C…..) or the row (1,2,3…) or both to keep them from changing when you replicate the formula down or across.

 With this setup you can change the weight for an individual grade and the formula is changed with it.  You can also add an assignment and then add it to the formula and then copy it down the column for all the students in the rows.

 A sample spreadsheet with data and formulas is in the attachment in Excel.  Sheet 1 is the sample gradebook template and Sheet 2 is the sheet with the formulas revealed in the cells.