This document is meant to provide an easier way for faculty to see their Roll Call Attendance results with a minimal amount of effort. It's in many ways a supplement to my longer document in the Community, Using the Roll Call Report: A Guide for People Who Do Not Use Excel, where I tried to explain how to use pivot tables in Excel to produce the most likely things you would like to extract from the Roll Call Attendance report that you can generate from Canvas.
This document could have been titled Using the Roll Call Attendance Report in 5 Easy Steps (or Thereabouts). What I have tried to accomplish here is provide you with a shareable Google Sheet that you can save to your own Drive account. It has ready-made pivot tables for you to use to get results similar to those described in my other document, but with next to no effort involved on your end. So you not only do not have to go anywhere near Excel; you really don't even have to know Google Sheets, other than how to save and open one. More importantly, for me, there are no "Mac vs. PC" issues, since it uses Google Sheets on the web.
Little did I realize that the previously linked document or this post would even be relevant at this point in time! Like many of us, I had hoped that another solution to Roll Call Attendance would have been introduced by now, much less an easier to use report. In the meantime, I hope that those of you do not wish to delve into spreadsheets find this a useful shortcut to getting attendance reports.
What You Need to Know
The only requirement for the solution outlined below is a Google Drive account and the ability to copy/paste. Period. Specifically, you will be using Google Sheets within Google Drive, but even if you do not know Google Sheets, it will make little difference! If you can:
Open a file
Copy and then paste
You are basically set.
I am sharing a Google Sheet that takes advantage of pivot tables. When I wrote my original document linked above, I admit I had no idea Google Sheets had pivot table capability, since I am (still) an Excel guy more than a Google Sheets guy. But once I discovered this capability—and I am by no means an expert in Google Sheets pivot tables—I jumped right in.
What to Do
For people who prefer to watch a demonstration first, I created the following video overview of what to do. It is closed-captioned (by me…not by YouTube!) if you need captions:
These steps are also outlined on the first sheet of the shared Google Sheet that I made available, but you to have to get there first, so here's what to do:
Save your own COPY of this sheet to your Drive account, since this not an editable sheet. You will need editing capabilities to copy and paste your roll call attendance report into it.
Head to your copy of the Google Sheet you just retrieved and click on File>>Open and then click Upload on the ensuing dialog box to upload your saved attendance report that Canvas sent to you. This will open your report in a separate Google Sheets tab. The attendance report from Canvas is in the .csv file format, and that will open up just fine in Google Sheets in a separate browser tab.
Select the entire sheet you just opened (the .csv file of the attendance report from Canvas) by clicking the white box between A and 1. (There is a screen capture of this on the Steps to do First! sheet.)
Click Edit>>Copy.
Click on into the copy of the sheet that you just copied from mine, but make sure you are in the sheet called OriginalCSVData. Make sure you are in cell A1. (You should be automatically.)
Click Edit>>Paste.
You're done! Look at the next three tabs for the pivot tables created. The first sheet (Steps to do First!) of your copied sheet explains what the three tabs will do, as does the video posted above and the text below.
What the Shared Sheet Has
The other tabs are pivot tables based on the data that you just copied into the OriginalCSVData tab.
The first tab, NumTimesPresentAbsent gives you, for every student, the total number of times you marked them Present, Absent, or Late in Attendance in Canvas. This is likely the most important data for you.
The second tab, ListofDaysPresentAbsent gives you, for every student, a listing of every day you recorded their attendance and their attendance status that day. This is good for those times you wanted to check on a student's attendance for a specific date.
The final tab, RecordedDaysbyStudentName, is handy to verify if you missed a date altogether or if you missed taking attendance for a specific student. Admittedly, if you missed a date altogether you will have to scan through all of the dates to see for yourself. Only you know (I hope!) the dates that you had class, so if you missed a date altogether this sheet will not help much unless you realize--scanning the columns--that you missed a date. If, however, you missed taking attendance for some students but not others, the sheet uses conditional formatting to color the cells green. The number 1 in those cells does not, in itself, signify much of anything other than the fact that you took attendance. It does not mean that that a student was present or absent; it simply means you took attendance. Therefore, if you see any white-colored, blank cells for a date, it means you forgot to mark a student that day while you marked some others. This is good for double-checking yourself and to ensure that the Total number that you see in the first sheet, NumTimesPresentAbsent, is the same for all students. (The video may provide some additional information if my explanation here is confusing!)
This should work with all attendance reports, given that, according to the documentation, all generated reports have the same eleven columns of fields. The only time a report may not work is if you use badges and the report includes a field for badges, in which case you must remove the badges field. But I tried this with several attendance reports that I ran at my own institution, and this Google Sheet should work!
I hope this is useful for those of you who need a relatively quick-and-dirty way to see the results of your roll call attendance reports.
Thank you so much for posting this - I was getting ready to sit down and make a spreadsheet template to deal with attendance and am so excited that you posted this because it is better than what I had started outlining for myself.
Thank you, this works well and I appreciate the effort put into making this spreadsheet.
What I fail to understand, though, is why a simple tool is not provided within Canvas itself. This is jumping through hoops just to find the days that a student has missed or been late. The spreadsheet must be recreated several times through the semester, which is too many steps. I'm going back to straight Excel tracking until something better comes along that works INSIDE of Canvas. Thanks.
Thank you, @deborah_oakley1 , I appreciate your comments. I agree; it's bad that this is not provided in an easier-to-use format, especially since (ironically) students can look up their own reports through the interface, as you likely know. If you have not already come across it in the Community, you can read the many tales of woe surrounding the current tool here: https://community.canvaslms.com/ideas/1090-attendance-overhaul . I would like to believe that Instructure is working on something behind the scenes or is busy trying to find a third party provider, given the many K-12 schools that use Canvas.
Thanks, Ken. Actually I didn't know that a student could check their attendance (but why not faculty?!). Good to know that this is a shared concern and that the developers are hopefully working to improve it.
Thanks again Ken. Clearly I am going to have to practice with Pivot tables. I know your pivot table works, but it wasn't working for me because of the way my course is set up with sections.
I tried deleting what looked to be the extraneous columns inserted for the section, but that didn't help.
What finally did work was starting over and eliminating the extraneous columns BEFORE copying the data into the copy containing the pivot tables. That worked.
This look amazing but mine isn't working. I wonder if it is because I have crosslisted sections? could that be what is breaking it?
Could you take a look and tell me how to fix it please? I have a link where you can edit it if that is easier but would like to learn how to fix it so I understand the pivot table.
I am currently the Associate CTLE Director, Teaching & Learning Technologies at Dominican University in River Forest, IL. I provide faculty with workshops on Canvas as well as one-on-one assistance, video overviews, help with online instruction, etc. I have a Master's in both Library & Information Science as well as Computer Information Systems. I started out as a librarian, so that may account for why I'm happy to help others in the Community addressing various issues; that good ol' service attitude goes with the territory of once being a librarian!
I have lived in the state of Illinois my entire life.
