Skip to main content
Glendon Campus Alumni Research Giving to York Media Careers International York U Lions Accessibility
Future Students Current Students Faculty and Staff
Faculties Libraries York U Organization Directory Site Index Campus Maps
TEACHING AND LEARNING

Learning Technology Services (formerly Faculty Support Centre)

News and Events Services and Facilities Workshops Resources About FSC

Using the Gradebook Template

An Introduction

Ask ten people how they manage student grades and you'll probably get ten different answers. Everyone seems to have a different approach. Whether the tool of choice is a gradebook application, a spreadsheet, or pen and paper, everyone is looking for an "easy" way to track their students' grades. But "easy" is illusive. Each approach has merit, but each also comes with a downside. Gradebook applications remove a good deal of flexibility in favour of the vendor's view of grading best practices. Spreadsheets, while flexible, require extensive setup and the creation of grading formulas. Pen and paper methods provide no inherent way to analyze the data they contain.

Since most people own a copy of Microsoft Excel, wouldn't it be nice if there was an "easy" way to use it to manage grades and analyze grades data? If only we could get away without that extensive setup and creation of grading formulas mentioned above. That's what this tutorial is all about. We've done most of the hard work for you. We've provided a gradebook template (gradebook.xls) that you can modify to suit your needs. You only need to add your name and course information...and your students, of course. Most of the formulas for analyzing your data have been pre-created for you. There's really only one formula you need to modify, and we've provided extensive documentation to help you with this step. We've also provided documentation about other aspects of the spreadsheet and the calculations being made behind the scenes, and have provided step-by-step instructions for downloading your class lists and adding them to your Excel gradebook.

This tutorial is all about minimum work and maximum productivity on your part. If you get stuck at any step, or if you require us to make changes to your gradebook to suit your circumstance we'll be happy to assist. Just send us a copy of your gradebook along with a note about your issues or concerns, and we'll email you back the final version, ready to use.

The documentation is divided into two sections. The Basics provides specific instructions about working with and modifying the gradebook.xls Excel spreadsheet template. The Extras section provides general information about various functions and features that may be of interest. If you have any questions, just e-mail us at lts@yorku.ca.

Basics

Downloading Class List

  1. Open your web browser and go to http://classlist.yorku.ca
  2. Enter your Passport York user information.
  3. Navigate to the class you want to download, using either the links to individual faculties or by search for the Instructor's Surname.
  4. Once you have selected an individual class, click the Comma-separated file (.csv) link at the top of the page.
  5. When the dialog window pops up, click Save.
  6. From the Save As dialog box that appears, select the location to which you would like to save the file. (You have to remember this location to import the data into Excel.) You may change the file name if you like. Click Save.

Setting up Gradebook

Stage 1:

  1. Download and open gradebook.xls.
  2. Click in cell A15 and type your name.
  3. Click in cell A16 and type your course number.
  4. Click in cell A18 and type the course term and the year.
  5. Select File > Save.

Stage 2:

  1. Place your cursor in cell H16 and type the name of your first assignment or test. You'll notice that the name is also reflected in row 21 in the student records area of the spreadsheet.
  2. Continue to add your assignments in row 16. The spreadsheet currently accepts assignments in columns H through AH.
  3. For each assignment, in row 17, provide a weighting as a percentage of the total course mark. To the left of the first assignment, in row 17, you will see Weight (must add up to 100%). The weighting currently adds to more or less than 100. When the weighting reaches exactly 100 the text will then read Weight (100%).
  4. In row 18, for each assignment fill in the number you will be marking that assignment out of. This is an arbitrary number.
  5. Select File > Save.

Importing Class List

  1. Launch Excel and open gradebook.xls.
  2. Click on the Classlist Import tab towards the bottom of the page.
  3. Click in cell A1.
  4. On the Data menu, select Import External Data > Import Data.
  5. Navigate to the folder where you downloaded the Class List file.
  6. Select the text file openinexcel.csv (or whatever name you gave the file when you downloaded the list), and then click Open. The Text Import Wizard opens.
  7. Verify that Delimited is selected, and then click Next.
  8. Verify that Comma is selected, and then click Next.
  9. Verify that General is selected, and then click Finish.
  10. Verify that Existing worksheet is selected, and then click OK.
  11. Select File > Save.
  12. If you wish to delete the email addresses, click on the column header D and then press the Delete key on the keyboard.
  13. Drag your mouse to select the first three columns with Student Number, Last and First Name of the student information.
  14. Select Edit > Copy. (Note: The final gradebook.xls provided has 40 rows available by default. If you have more students, you will need to add more rows and copy and paste the formula before continuing.)
  15. Click on the Gradebook tab.
  16. Place your cursor in cell A22, the leftmost column in the first row of student records.
  17. Select Edit > Paste Special... > Values.
  18. Select File > Save.

Inserting Rows

  1. Select the cell before which you would like to insert row(s).
  2. Select Insert > Rows.
  3. Repeat for Steps 1-2 to insert additiional rows.

Adding Assignments

  1. Place your cursor in cell H16 and type the name of your first assignment.
  2. You'll notice that the name is also reflected in row 21 in the student records area of the spreadsheet.
  3. Continue to add your assignments in row 16. The spreadsheet currently accepts assignments in columns H through AH.
  4. For each assignment, in row 17, providing a weighting as a percentage of the total course mark.
  5. To the left of the first assignment, in row 17, you will see Weight (must add up to 100%) the weighting is more or less than 100.
  6. When the weighting reaches exactly 100 the text will then read Weight (100%).
  7. In row 18, for each assignment assign the number you will be marking that assignment out of. This is an arbitrary number.
  8. Select File > Save.

Calculating Final Grade

The final grade will be calculated by examining the mark a student received on each assignment, as a percentage of the total mark available for that assignment, and then multiplied by the weighting for that assignment. All assignments will then be summed to arrive at the final mark.

Final mark = The sum of
(assignment 1 mark / assignment 1 total available mark * course weighting for assignment 1) PLUS
(assignment 2 mark / assignment 2 total available mark * course weighting for assignment 2) PLUS
AND SO ON AND SO FORTH

In our gradebook.xls workbook, this formula becomes:

=SUM((H22/$H$18*$H$17)+(I22/$I$18*$I$17)+(J22/$J$18*$J$17)+(K22/$K$18*$K$17)
+(L22/$L$18*$L$17)+(M22/$M$18*$M$17)+(N22/$N$18*$N$17)+(O22/$O$18*$O$17))

Note:

  • The use of the $ sign in the equation. The $ sign specifies that the referenced cell is always in a specific location.
  • The formula in gradebook.xls calculates from column H to column O. You'll need to expand or delete part of the formula according to the number of exams or assignments you have.

To compute each student's individual final grade by yourself, follow these steps:

  1. Point to cell E22.
  2. Type = (equal sign) to start the formula.
  3. Type the function SUM(). The formula should now look like =SUM().
  4. Within the parenthesis type the formula for the first assignment. In our case, (H22/$H$18*$H$17). It is important that this formula is contained within its own set of parenthesis so that Excel will perform the correct order of operations on the calculation. Our formula should now look like =SUM((H22/$H$18*$H$17)).
  5. For each additional assignment, complete the formula as above and add it into the parenthesis for the SUM function, separated by a + sign.
  6. Once all assignments have been entered, press the Enter key.
  7. Copy and paste the formula to the final grade fields of all other students.

Most formula errors occur when the mathematical operators are not entered in the proper order of precedence. The following is the order of precedence for mathematical operations in a formula:

*, / multiplication, division
+, - addition, subtraction

You can change the order of precedence by enclosing segments of the formula in parentheses. Excel first performs all operations within the parentheses and then performs the rest of the operation in the appropriate order.

Note that each open parenthesis must be matched by a closed parenthesis, or Excel will not accept the formula

Copying Formulas

When you copy a formula, the cell references within the formula change to reflect the row and column the formula is being copied to.

  1. Select the cell that contains the formula.
  2. Select Edit > Copy.
  3. Select the cell(s) you want to copy the formula to. (Click in a cell and drag the mouse to select multiple cells.)
  4. To copy the formula and any formatting, select  Edit > Paste.
  5. To copy the formula only, select Edit > Paste Special... > Formulas.

Extras

Calculating Average

  1. Place your cursor in a cell below the last student record, and in the same column for which you would like to average marks.
  2. Click on the arrow to the right of the AutoSum icon (backwards E) and select Average.
  3. If the correct cells were automatically selected, press Enter.
  4. If the correct cells were not automatically selected, click and drag your mouse over all the cells to be averaged and press Enter.
  5. Copy and paste the resulting formula to the other assignments.

Calculating Median

  1. Place your cursor in a cell below the last student record, and in the same column for which you would like to calculate the median mark.
  2. Click on the arrow to the right of the AutoSum icon (backwards E) and select Median.
  3. If the correct cells were automatically selected, press Enter.
  4. If the correct cells were not automatically selected, click and drag your mouse over all the cells to be averaged and press Enter.
  5. Copy and paste the resulting formula to the other assignments.

Calculating High Mark

  1. Place your cursor in a cell below the last student record, and in the same column for which you would like to calculate the high mark.
  2. Click on the arrow to the right of the AutoSum icon (backwards E) and select Max.
  3. If the correct cells were automatically selected, press Enter.
  4. If the correct cells were not automatically selected, click and drag your mouse over all the cells to be averaged and press Enter.
  5. Copy and paste the resulting formula to the other assignments.

Calculating Low Mark

  1. Place your cursor in a cell below the last student record, and in the same column for which you would like to calculate the low mark.
  2. Click on the arrow to the right of the AutoSum icon (backwards E) and select Min.
  3. If the correct cells were automatically selected, press Enter.
  4. If the correct cells were not automatically selected, click and drag your mouse over all the cells to be averaged and press Enter.
  5. Copy and paste the resulting formula to the other assignments.