Roll call attendance tool .csv file

[jbuchner]

I have 360 students who attend labs on different days of the week, I'm not going to grade on attendance, but I want to track easily which students are missing lab. Without having to page through many calendar days or individual students, I'd think the easiest way to do this is through the .csv file, I however want to stop doing this manually, I'd like the sheet to highlight students that have missed more than X number of times, is anyone a spreadsheet wizard that has written a marco or has an example sheet with a set of commands that could do this?

[James · Accepted solution]

@jbuchner 

You can use some built-in Excel tools without typing any formulas or running macros to get this. The downside to not using a macro is that you would need to do it with each file you download, but it's fairly quick.

After opening the attendance .csv file in Excel, do the following.

1. Make sure your active cell is in the data and not in a blank cell. This is the default when you open a file.
2. Click on the Insert command from the menu bar / ribbon.
3. Chose PivotTable and OK. The defaults will create a pivot table in a new worksheet, which is what we want.
4. In the list of PivotTable Fields on the right panel, 
   1. Drag Student Name to the Rows
   2. Drag Attendance to the Columns
   3. Drag Class Date or Timestamp (almost any field would work) to the Values. It should say "Count of [your field]"

My setup looks like this:

On the left side, you will have a Pivot Table that shows the details of how many times each student has been there. It is sorted by student name in "First Last" order, which may make it harder to find someone in a class of 360 students. In my example, the first person was absent once, never late, and present 12 times for a total of 13 days of attendance.

Note that a student will not show up here if they haven't had attendance marked for them.

There are other things you can do.

If you want to sort by the number of absences, then

1. Click on the drop down next to Column Labels and unselect everything but "absent"
2. Click on the drop down next to Row Labels and choose More Sort Options.
3. Sort Descending (Z to A) by count of class date (or whatever column you put into values).

Often, I take the data from a pivot table and paste it into an empty space in the spreadsheet. Then I can use the normal sort functionality. This would allow me to sort by absent without having to deselect everything else.

There's a lot more that can be done, but it gets more complicated. If you want the names in sortable (Last, First) format, you'll either need to split the names (hopefully, but not reliably, there's only two parts to each name) or use the Student ID and a lookup table to get their sortable name. Those are advanced techniques.