Moving Your WebCT Gradebook to Excel

This documentation contains instructions on how to download your gradebook from WebCT and import it into Excel to allow for more complicated calculations. It will also cover how to create a weighted gradebook in Excel using point structure. The final area covered is exporting it from Excel and importing back into WebCT to be viewed by the students.

Lab 1: Downloading the WebCT Gradebook

• Go into the WebCT gradebook and click on CONTROL PANEL > MANAGE COURSE > MANAGE STUDENTS.
• Make sure all your students are shown by selecting ALL from the dropdown menu next to "Page:".
• Under the OPTIONS: RECORDS dropdown menu choose DOWNLOAD then click GO.
• From the DOWNLOAD STUDENT RECORDS window, choose TAB next to "Record Separator."
• Click DOWNLOAD.
• Choose SAVE FILE AS.
• If given the opportunity to rename it you can name it anything you like, just make sure that the extension stays .txt

Note: Mac Users: If you are using IE you will need to rename the file. It will end in .pl and you'll need to change it to .txt.

• Click SAVE.
• You may now wish to minimize WebCT.

Lab 2: Creating an new Excel Gradebook with Weights

• Open Excel and from the FILE menu choose NEW WORKBOOK .
• Click on the SHEET 1 tab and type your weights using your format of choice.
• Right-click on the tab that reads "Sheet 1" and choose RENAME ( Mac Users : Control-Click to get this sub menu)
• Type in the new name: " Weights "

Lab 3: Importing the WebCT Gradebook into Excel

• Click on the SHEET 2 tab.
• From the DATA menu choose GET EXTERNAL DATA > IMPORT DATA ( Mac Users : choose GET EXTERNAL DATA > IMPORT TEXT FILE) .
• Choose the .txt file you just downloaded from WebCT then click OK or OPEN .
• From the TEXT IMPORT WIZARD :
  • Choose DELIMITED then NEXT
  • Check TAB then click NEXT
  • Make sure everything looks good then click FINISH
  • When asked "Where do you want to put the data?" choose EXISTING WORKSHEET and make sure the cell reads " =$A$1 ." If it doesn't, type it in.
  • Click OK
• The data will now fill into the empty spreadsheet on SHEET 2.

Lab 4: Modifying the Gradebook

Add Column for Homework Total

• Click on column to the right of the insertion point and from the INSERT menu choose COLUMNS (click on column "J" for the purpose of this exercise)
• Click on the first cell in the column and name it "HW Total."

Add the HW Total Formula

• Click on first cell under the HW Total cell.
• Click on AUTOSUM (Σ) . It should automatically select all the homework cells for that row.
• Hit the Enter key on the keyboard to set the formula.

AutoFill the Formula for All Rows

• Click on first cell and, using the autofill box at the bottom-right of the cell, drag to auto fill all cells.

Lab 5: Using Formulas and Functions

Drop the Lowest Quiz Grade

• Insert a column after the last quiz.
• Name it "Drop"
• Type in the following formula: =MIN(M2:P2) where M2:P2 represent the cells of the quiz grades.
• Autofill this formula into the remaining rows.

Create the Adjusted Quiz Grade Column

• Insert a column called "Adjusted quiz total" after the "Drop" column.
• Type in the following formula: =SUM(M2:P2)-Q2 where M2:P2 represents the sum of all quizzes and Q2 is the dropped grade from the "Drop" column.
• Autofill this formula into the remaining rows.

Create the Final Grade Column

• Click on the first cell in the existing column named " Final Grade" . If one doesn't exist then create a column named "Final Grade."
• In the first cell type the formula: =(J2+K2+L2+R2)
• Autofill this formula into the remaining rows.

Create the Final Grade Percentage Column

• Go to the next (last) column and name it "Final Grade Percent"
• Type in the formula: =(T2/Weights!C7)*100 . Where Weights!C7 represents the total number of points from the "Weights" spreadsheet.
• Click on the formula cell then choose FORMAT > CELLS .
• Choose NUMBER and 1 decimal place.
• Click OK
• DO NOT AUTOFILL YET

Lab 6: Creating an Absolute Reference

• Click on the formula cell.
• In the upper FORMULA BAR you will see the formula =(T2/Weights!C7)*100
• For a cell reference to become an absolute reference it must contain two "$," one before the letter reference and one before the number reference. Therefore, =(T2/Weights!C7)*100 becomes =(T2/Weights! $ C $ 7)*100
• Autofill this formula into the remaining rows.

Lab 7: Saving the Document

• From the FILE menu click SAVE to save the file in its spreadsheet format.
• Now choose SAVE AS from the File menu to save it as a tab delimited text file for WebCT.
• Next to FORMAT choose TEXT (TAB DELIMITED)
• Click SAVE
• At the next window, click OK
• Then in the next window click YES

Lab 8: Upload the Gradebook Back to WebCT

• Go into WebCT
• In gradebook, under OPTIONS: RECORDS choose IMPORT STUDENT DATA then click GO
• Click BROWSE
• In the UPLOAD FILE area click BROWSE again to choose the Excel text file.
• Pick .txt file then click OPEN
• Click UPLOAD
• Click ADD SELECTED
• Make sure separator is TAB
• Click IMPORT
• WebCT will recognize that you are importing new columns and will prompt you to create the new columns. Make sure the dropdown menu is set to CREATE NEW and click CONTINUE . Do this for each new column.
• At the IMPORT CONFIRMATION window click on CONTINUE
• You will be prompted to select a column type for any new columns you created. Choose NUMERIC for each new column then click CONTINUE .
• You should now the gradebook with all the new columns, unfortunately, added at the end. You can rearrange them from within the ORGANIZE > MANAGE COLUMNS area if you wish.

Note: You will notice that all the Excel formula columns have been converted to raw data. They will not be live formulas inside WebCT, only the formula's value.

Copyright © Instructional Technologists: July 27, 2006 • College of Charleston, Charleston, SC 29401 - 843.953.5569