Academic Computing Services

Using Excel to Manage Grades in WebCT

Often professors or TAs have need to use an external editor to manage their students’ grades, such as Microsoft Excel. This instruction sheet will assist TAs and professors in:

• Downloading the Class Data from WebCT
• Editing the Data in Microsoft Excel
• Uploading the Edited Data to WebCT, and
• Making Grades Available to Students

The advantages to managing grades in this fashion include:

• Retain a local copy of students’ grades on your computer
• View and edit the table all at once in Excel
• Utilize functions such as summation and distribution with which you may already be familiar in Excel, and
• Avoid retyping or reentering data, including students names, once they are available in WebCT.

Note that the screenshots used below are on a Windows XP computer using Microsoft Office 2003. Your system may be configured differently, but the procedure is the same.

Downloading the Class Data from WebCT

To manage WebCT grades in Excel, you must first download a spreadsheet from the class's Grade Book. This will give you a spreadsheet that is properly formatted to copy grades back in to WebCT's Grade Book.

1. Once you have logged into WebCT, If you are not already there, click the Teach tab (1). Then click the on Grade Book (2), in the Instructor Tools section along the left:

   
   

2. By default, WebCT will export only the gradebook columns that are visible on the screen. The gradebook has 5 views, the most commonly used of which are Grades and Members (1). If you want to edit grades you have already imported into WebCT, make sure you are in the Grades view; if you want to add a new column of grades, select Members. This will make importing your changes less confusing.

   Click "Export to Spreadsheet" (2), along the bottom edge of the grade list area.

   Note: This is only available to the instructor -- TAs added to WebCT do not have this option unless the have been added with Instructor privileges, even if they have access to the Gradebook. The instructor should e-mail iwdc@ucsd.edu if TAs need this privilege.

   
   

   (For more advanced users, you can select Custom View, click the "Reorder Columns" button, and make visible only those columns you wish to make changes to. Note that the User ID should always be visible, as you will not be able to import your changes without it.)

3. This page will give you extra options about the spreadsheet you create. You do not need to change any of the default options. Click "Export" to contiune.

   
   

4. Internet Explorer will ask what you want to do with this file. Click "Save", so that you can select a location you can access later, such as your Desktop or your "My Documents" folder.

   
   

5. You will want to give the grade sheet a name indicating what class they are from, and click "Save".

   
   

Editing the Data in Microsoft Excel

1. Open the file you just saved, either by double clicking it or using the 'Download Complete' window, to open it in Excel. It will contain all the information about your students.

   You can now edit any grades or information from the Grade Book in Excel.

   To add a new grade to the Grade Book, click on an empty column in the first row, and type in the grade column's name. In the example, I entered Quiz One, and typed in my three students' (manifestations of my multiple personalities) their grade.

   
   

2. Before you import your changes back into WebCT, delete from the spreadsheet any columns you do not want changed. Specifically, if you have a Calculated column such as for the final grade, importing that column will cause WebCT to use the current scores for that column, rather than calculating it based on your formula; this would prevent it from updating based on changes to the gradebook. You must, however, leave the User ID column in place -- WebCT uses this, not the Student ID, to match grades to students.

   Also, Cut and Paste the User ID column so that it is the last column in your gradebook. This prevents an incompatibility between how Excel saves CSV files, and how WebCT reads them. (Specifically, in any given row, Excel omits empty cells. This creates a spreadsheet where different rows have different numbers of cells. WebCT, on the other hand, expects ever row to have the same number of cells.)

3. When you are finished, save your changes by going to the File and selecting Save. Excel will warn you that the file "may contain features that are not compatible with CSV (Comma Delimited)." Click Yes to keep the workbook in this format. You can then close Excel. (It will ask you again if you want to save your changes, even if you didn't make any. Click No.)

   
   

Uploading the Edited Data to WebCT

1. Return to the WebCT Grade Book. Above the grade list area, click "Import from Spreadsheet".

   
   

   Click "Browse"

   
   

2. Select the grade file you just saved (in my example, "BICD100grades.csv"), and click "Open"

   
   

3. Click "Upload".

   
   

4. This page will make sure that the columns in your spreadsheet ("Import Columns") match up with the columns in the Grade Book ("Grade Book Columns"). "Last Name","First Name", and "Role" cannot be imported, to avoid making unintended changes to the Grade Book; likewise, for any columns you didn't intend to update, you should select "Do not import" from the drop-down menu. This is especially important to prevent WebCT from permanently overwriting calculated columns. Make sure any new columns (such as my new column "Grade One") are set to "Add as new column." Click "Import" once you have made sure that all Import Columns are matched to a Grade Book Column.

   
   

5. Now, WebCT will warn you about any columns that were not imported. Click OK to return to the grade book.

   
   

Making Grades Available to Students

1. Your grades have now been imported, but there is one more step: Release the new grades to students, so they will see these grades in their "My Grades" tool. First, click on the "Grade Book Options" button, and select "Column Settings".

   
   

2. To change an option on the Column Settings page, just click on it. The first setting is to change the column type to Numeric (This may be skipped if you do not intend to let students see grade statistics, or calculate final grades). Click on the word "Text", under the column "Quiz 1" in the row "Type:". When you do this, a window will pop up with the available column types. From the list, choose Numeric, and click Save.

   
   

3. WebCT will show you what the grades look like, both before and after the conversion, so that you can make sure no essential information is lost (For instance, if you tried to convert a column with letter grades to a Numeric column, this would give you a chance to see that the letter grades would be lost). Click "Apply" to continue.

   
   

4. Return to the Column Settings page. First, click "Released to Student" (1), and then "Grade Column" (2), to allow WebCT to handle this column as a grade and display it in the student's My Grades tool. If you would like students to see anonymous statistics about this grade, click "Release Statistics" (3). A window will pop up with three options: None, Average Only, and All.

   If you select All, the following statistics will be made available to students whose grades have been entered:

   • Count (number of grades included in the statistics)
   • Average
   • Median
   • Maxiumum
   • Minimum
   • Standard Deviation
   • A Histogram of scores
   
   
   

If you have any questions with this process please feel free to send e-mail to IWDC at iwdc@ucsd.edu or call 858.822.3315.