(I apologize for the title of this post.)
In my eight or so months of teaching, I’ve been shocked to discover that I’m apparently fairly retrograde in at least a few areas. I think I’m more willing to lecture for longer periods of time than are some of my colleagues; I’m not a big fan of unproctored, at-home assessments; and I take attendance at every class.
As the end of the semester approaches, my attention turns to that last item. I use ANGEL’s PIN system to handle attendance for me (ANGEL provides a PIN for me; I put it on the board; students enter the PIN back into ANGEL; and ANGEL records the results), which more or less works well. The problem is that the default attendance report ANGEL provides me is essentially useless:
The default ANGEL attendance report
As you can see, there is no tabulation of any kind, which defeats the entire purpose of the attendance regime (I would like to know how many times a student did not attend class; radical, I know).
It took me a while to figure out how to solve this problem, so I thought I’d share it here. First, from the Attendance Manager, click the Export link. I prefer CSV format:
Export to CSV format
Next, open up the file in Microsoft Excel:
The raw export
Every row in the spreadsheet is a student name and the student’s attendance entry on that day (assuming the student entered anything; if he didn’t, then there is no entry!). I’m not sure why the export is prepared in such a non-useful way (at least from the perspective of my use case, which I’m assuming is the most common use case). However, we can very quickly and easily create a PivotTable which displays the data in a far more pragmatic fashion. I’ll assume that you’re cool and are using the Mac. Click the Data tab in the ribbon, then click the arrow next to PivotTable, and select “Create Automatic PivotTable”:
Creating an automatic PivotTable
Instantly, you’ll be presented with a great report that provides tallies of student attendance by day (in the screenshot below, I’ve hidden some rows and columns so that you can get a sense of the entire PivotTable):
The final PivotTable
There’s a column for every student, and the last row in the column shows the number of times a student was present in class. If I need to know the number of times a student was absent, I can get a count of the number of days where attendance was collected by counting the date rows in the first column, and finding the difference.