You may be seated

Let me introduce you to my partner.  He’s a public school teacher in North Philadelphia, a Torah scholar and a deeply empathetic listener.  A few weeks ago, he explained his classroom seating chart and student behavior grading system and I have bad news for you:  the data were not alright.  He had come up with the creative system of projecting a seating chart onto his classroom whiteboard — and grading students by writing on the whiteboard.  Then, he took a cell phone photo to reference later.  However, it was painstaking to type grades from his photo album into his gradebook!

On our way home, he said, “If only there was some coder who could make an application that tallied seating chart points for me…” and then trailed off.  I’m no coder, but I decided then and there: that person was going to be me!  Now, I know I’m a cape-wearing Excel Expert, but this project was a challenge so I had to role up my cape-sleeves and learn some new skills.

This is my most technical blog post yet (fair warning!) — so why should you care about this project?

• I want to support grassroots changemakers and culture jammers to develop systems that actually work!  The days of square pegs in round holes are numbered … if we let them.
• If you are a teacher, you might want to adopt something similar!  Feel free to contact me if you would like a copy of the Seating Chart Wizard project and I can walk you through how it works.
• By putting in hard work to make a better data system, my partner’s students’ behavior improved by 10%.  That means more learning and less classroom discipline.  To me, that’s a huge win!
• I never would have built this system if someone in my life hadn’t asked for help.  If you have an idea for the spreadsheet of your dreams, I hope you’ll write to my advice column!  Seriously.  Ask me your thorniest, most outlandish data questions and we’ll see what we can do together!  (This pitch brought to you by my amazing sister!)

Seating Chart project wishes:

1. Desks arranged in pods of 4
2. Space to include up to 3 grades per student
3. Ability for teachers to edit the chart in real time
4. Ability for teachers to assign points in real time
5. Ability to sum points and provide daily score per student
6. Desks turn green when student has earned 10 points
7. Ability to re-set all points every day

 

The Basics + a cool tip

First, I adjusted the Column Width in Google Sheets to make the standard grid look more like a pod of desks, with room to type in points for each student.  I wanted all of the Name columns to have the same width as each other and all of the number columns to have the same width as each other, so I used a special Paste Column Width Only feature to help the formatting match.  With these steps, I had accomplished requirements 1-4, but my work was just beginning!

 

 

 

Formula Fever!

Every day, my partner’s students can earn up to 15 points: 5 for sitting in their assigned seats, 5 for completing their Do Now assignment and 5 for other classwork (other teachers could change the 3 scores to other things they want to measure.).  My next step was to add up these numbers (in a new tab or a new Sheet).  This would make gradebook data entry much easier, because he could sort the student names in alphabetical order and see their daily points.

I used “guess and check” to write good formulas.  For example, Student 1 received 10 points for “Seat.”  Those 10 points are in the cell C1 in the Seating Chart tab.  On the Data tab, the cell B2 represented Student 1’s seat points.  It has to equal whatever is in C1 on the Seating Chart tab.  I used formulas to copy every single name and every single point over to the data tab.  If formulas are not your friend yet (you’re not alone!).  Here’s a tip that really helped me:  the Show Formulas button!  You can access it under the View menu in your standard GoogleSheets toolbar.  Here’s a peek at what my formulas looked like, including the Daily Grade sum.  You’ll notice that most formulas say ‘Seating Chart’!LETTERNUMBER which means that they are referencing the Seating Chart tab and then a particular cell.  

One fish, two fish, red fish, blue fish

We decided it would be REALLY special if the desks and the students’ names would turn green when the students earned 10 points, but turn red if the student wasn’t on track.  This was a perfect case for Conditional Formatting!  Conditional Formatting is a tool in Excel or Google Sheets that let’s you create criteria for turning cells different colors.  In our case, we wanted to the desk to turn RED if there was one zero OR two zeros.  (So if a student completed their classwork but did not sit in their assigned seat, their desk would still be red, and visa versa).  If the student did both, their desk turned green.  We could not use simple math rules (greater than or less than 10) because students could earn 5 points from classwork and turn their desk green without completing the other two!  So we needed to use another set of principles this time, called Boolean operators.  Getting deep into Boolean operators is outside the scope of this blog post, but they’re really cool and useful!

Here’s an example of what my conditional rules looked like:

This part isn’t fun, but it’s the truth:  I had to set up Conditional Format Rules for every single cell!  It took a long time.  And then I changed my mind about the colors, so I went through and edited them all again.  Despite doing some testing, a few desks were still wrong when my partner used the tool in his classroom.  So he tagged me in a GoogleSheets comment so that I could address the problem as soon as I had time.  C’est la vie!

Wiping the slate clean

Every day, my partner needed to start fresh with new data in his Seating Chart.  All he has to do is copy and paste the Data tab into a new tab (so that he can save it if he needs it, before erasing the data in the Seating Chart tab!).  Then, he can do a Find and Replace to delete all 0’s and 5’s and replace with x’s so that the Seating Chart is clean and ready to go.

Looking ahead

Both of us are happy with the results and we hope to get feedback from more teachers about how to improve the tool.  We dreamed up plans with a friend who happens to be a local Astrophysics PhD candidate (over some takeout Chinese food…) to host a little “hack” this summer to expand the Seating Chart wizard and make it even better.  So if you’re reading this and you want to give the project a whirl, borrow our template, provide feedback, or contribute to Seating Wizard 2.0, reach out!