Google Sheets: Create Easy To Use Logs For Participation, Restroom Use and More!

 

Participation points are a big part of the way I teach. The problem is keeping track. In the past, I've printed slips of paper with my roster and gave kids tally marks. Being absent minded, I often lost those papers. I tried carrying my iPad around and marking a Google Form to tally points. That was time consuming. I have even done a strategy called feedback tokens where I printed out hundreds of business card size tokens and gave them to students for participation, etc. and had them "cash" in at the end of class. That worked, but was a management nightmare. 

Now that students are back face to face, I've been looking for a way to digitally track participation points. I did a few Google searches and found quite a few apps for chores, etc., paid services, but nothing free, that would meet my needs. I tried using a spreadsheet on my iPad, but trying mark kids in each cell was cumbersome. As I kept searching, I stumbled across a post by Alice Keeler. Duh! Of course Alice would have a spreadsheet hack for this. Why didn't I go there first. She loves to say, "The answer is always a spreadsheet". In this instance, she is absolutely correct!

Alice posted a sample of what I am trying to do with a spreadsheet using checkboxes. Click here to view Alice's post.  It hit me! Create a spreadsheet like Alice did, using checkboxes, access it on my iPad and I can mark points for students quickly and easily using my Apple Pencil. As I went about developing my participation points spreadsheet, I thought this would also be good for tasks such as keeping track of student restroom use as well. Since you can duplicate sheets, my restroom log was two clicks away.

Start by creating a spreadsheet and entering your students' names. Title the column to the right of the student name column Total. Going right, title each column 1-however many points you might give during a given class. In my class, I have it go as far as 20. I probably won't ever give that many, but I like to have more than what I need. In each cell, add a checkbox by going to the Insert tab and selecting checkbox. In that first cell with a checkbox, click and drag to add checkboxes to all cells in which you want checkboxes. Click here to make a force copy of my sample.

If you're not a spreadsheet ninja, like Alice, here is where you're going to get a little fancy. In the cell for the first student in the Total column, type this formula =COUNTIF(C2:V2,TRUE) and push Enter.  This allow show you a tally in the Total column of all the checkboxes checked in a row. The formula =COUNTIF(C2:V2,TRUE) may need a little tweaking depending on your spreadsheet. C2:V2 represent the first and last cells in a row I want to automatically tally. Your first and last cells in a given row may have different letters so you will have to adjust. 

After using the formula in the first student's row in the Total column, like with the checkboxes, click and drag down to the bottom of the column. This will apply that formula to each student's row.

Below is what it looks like when you click a checkbox. You can see the total column shows how many checkboxes in a student's row have been checked.

 

If you want to quickly recreate this sheet, formulas and all, for other classes or tasks (I did this for my Restroom Log), go to the tab at the bottom of the sheet, click the arrow next to the sheet name click Duplicate. This will add another sheet to your file. 

In this case, with a newly created sheet, I deleted all but three columns. For something like a Restroom Log, I give students three restroom breaks a semester. This will help me have, at a glance, a record of how many times kids use the restroom.

Take a look below at what it looks like in real time.

I access this spreadsheet on my iPad and use my Apple Pencil to check the checkboxes. I recommend using a tablet or touchscreen device for this, phones included. This makes it easy to circulate the room and give points, etc.

Anything you want to track or take a tally of can easily be done with a spreadsheet using checkboxes and the formula =COUNTIF(C2:V2,TRUE). Big thanks for the inspiration to Alice Keeler. If you're looking for a treasure trove of ideas for spreadsheets, Google Classroom, Jamboard and more, check out Alice's website alicekeeler.com. Follow her on Twitter by clicking here.

If you have any questions and would like a follow up, contact me via Gmail or Chat at ajuarez@techcoachjuarez.com. My book, The Complete EdTech Coach: An Organic Approach to Digital Learning, co-authored with my wife Katherine Goyette is now available on Amazon. Click here to purchase. It is published by Dave Burgess Publishing. Be sure to follow the hashtag #OrganicEdTech and #CVTechTalk for updates.