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.]

• Once you have a summary class list page in NOVASIS open in your browser (the list itself is in TABLE format),
• just save it to your hard drive with the file extension HTM or HTML
• and then open MS Front Page,
• and use the FILE menu, OPEN selection to find the file on your hard drive and open it, and
• click on the column containing the names for your class, then use the TABLE menu, SELECT COLUMN option to select the whole column of names and COPY it. [You can unselect the column header "Student Name" by CNTL clicking on that cell if you like, or you can select the column manually by clicking and dragging the mouse on just the names.]
• Next open Excel and paste the column into an appropriate cell somewhere in the upper left (leaving room for header course identifying information, and I like to number my students in the first column: EDIT, FILL, Series) by
• clicking on the target cell for the top of the column (including the NAME header from the original table) and select the EDIT menu, PASTE SPECIAL, and select TEXT and OKAY
• and each name will go in a separate cell in the Excel column you chose.

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.

Disclaimer

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