Importing Novasis Class Lists into an Excel Spreadsheet for Grades

One would think a common activity like grade bookkeeping would be supported by some common approach across campus. However, this is not the case.
[See UNIT workshop comment below, though.]

Spreadsheets are the way to go and Excel is on everyone's desktop/laptop computer. To get a grade spreadsheet started for every class each semester one needs to be able to insert a column of student names into the spreadsheet from the HTML file classlists of NOVASIS. [The problem is that browsers don't allow you to copy columns from a web table, at least not Netscape.]

If for some reason you don't feel comfortable yet with FrontPage, you may also open the saved HTML classlist file in MS Word instead and similarly select and copy the column.

If you want to have your own class lists, you can edit the saved .htm file to get rid of the stuff above and below the table that you do not want, and eliminate any columns you do not want, and add new ones. For example, I use a signup sheet at the beginning of each semester to identify who is attending class each day until the Add/Drop period finishes.

Using Excel Spreadsheets to Manage Your Grades

We all have different categories of grade inputs for each student so one has to be flexible in adapting any Excel template to your particular needs. But from a basic example, one can handle most situations. The following Excel file has internal comments on the second sheet trying to explain the setup of the first (prefinal) and third (final) sheets and the formulas used. [I typically put multiple classes of the same course on separate sheets in the same file.]

example Excel grade spreadsheet [pdf output]

UNIT offers an occasional workshop on Excel XP: Calculating Student Grades if you need more help than this template.

If you insert your class list column of names directly into such a grade sheet, you just need to make sure you have enough rows available to paste into without running into the stuff below the actual grade data table. Then you have to restore the columns which have average or weighted average formulas in them by copying and pasting.

Importing grades into the WebCT Gradebook

The only certain way for students to be aware of their running class average and your grading scheme is to publish it in the WebCT gradebook set up for each class. This is the only feature of WebCT that I use since I believe in the open courseware idea of MIT: we should all be sharing what we do with each other in our teaching methods and classroom activities. If you still prefer to manage your grades with Excel, which does not have that clunky interface between you and what you want to do, you can easily upload your grades to WebCT from Excel.


There have to be many more advanced Excel users on campus who can do better than my example. Where are they?