Sunday, September 04, 2016

Excel for Course Management - Tips and Tricks

It occurred to me as I was setting up my own course grade book that every instructor needs to do something of this sort but that there is probably no training on those things that would make life much easier.  Way back when we were contemplating first going to an enterprise learning management system, winter 2003, I had the thought that if we could give large class instructors various spreadsheet tools that we designed, then they could use that for the grade book and simply use the lms to import csv files with the grades and then render them in a way students could use.  What I discovered then is that knowledge of Excel for this practical purpose was much more limited than I had expected, particularly in disciplines that otherwise would not use Excel and this wasn't just true for humanities courses but was also true even in the STEM disciplines, such as Chemistry.  I doubt things have changed that much since.  And now I am aware that many instructional designers don't have this knowledge either, as it really has nothing to do with pedagogy, so where would they learn it?

I thought I'd share a few things that might help the typical instructor, and perhaps with that start a thread about what other things people would like to know in this regard.

Let me begin with a movie that I made this morning about going from NetID to Email Address and then going in the other direction as well.  The idea is to process a string of these in one fell swoop, using built in Excel functions for this purpose.  What the video demonstrates is the right set of functions and the correct syntax to use.  For somebody who already knows Excel, this is all pretty trivial.  For those who don't, they might find it remarkable how easy it is to do this sort of thing.

This next one is about making a smart histogram based on the data, better than what the LMS will produce, so for example to show students how the class did on a particular exam.   You must download the Excel workbook from the link to use it.  Then in column A you paste in the scores, starting with cell A1.  At present it is set up to accommodate as many as 1000 scores.  If the instructor wants more than that, the spreadsheet formulas can be edited to do so.  Note that the spreadsheet is password protected but the password is blank, so it is easy to unprotect.   The other information that the instructor types in is the Column Heading, which is put into cell I1, and the maximum possible score, which goes into cell I7.  Everything else is then generated from that information.  The instructor can then take a screen shot of the graph and post that in the class Web site.  There is no need to post the actual Excel file.

This next Excel Workbook is about computing a Smart Sum, for example from a set of n scores delete the bottom m scores and then sum the remaining n - m scores.  Many instructors want to use this sort of grading scheme, but probably have to do some manipulations to generate the result.  Here the spreadsheet does all the work.  The instructor simply has to insert the data and voila.

The last one is useful for considering survey data, where each record is in its own row and the survey has multiple questions, with answers to questions in different columns. It is called Choosing a Row of Data and is used to elevate an individual's response to the survey and then insert that information elsewhere for it to be used in some way.  The real action in that workbook occurs inn the middle worksheet called formulas.  Though the spin button goes from 1 to 1000, the thing is set up to handle a maximum of 50 rows of data at present.  Though the process by which it could be extended to more rows should be evident.  On the data worksheet, only the first 9 rows have responses to the paragraph question, but it is enough to illustrate how the thing works.  The last worksheet shows how the response renders within the form.

It seems to me that a Library of these type of items should be shared globally and that the various units that support online learning on the respective campuses serve as custodians of that Library, so as new uses get requested and somebody produces a way to address that use, the solution then can be broadly distributed and others can learn about the availability of that particular Excel module.  I don't want that Library management function to be my responsibility, but I probably would find it interesting to design additional spreadsheets that can do other things. 

No comments: