Creating An Online Student Planner Using Google Sheets

     I don't know about you, but I loooove being organized. I am a fan of color-coding, check boxes,  and label makers. So as someone who is highly into executive functioning, I get in a tizzy when people aren't. As an educator, this has been compounded with online learning.

    In a normal school year, I would be teaching my students how to use their planner. How to cross things off when they're finished, use a binder clip to hold old pages together for easy referencing, and how to have a routine for writing things down. The start of this school year has made this system difficult and, four weeks in, I only just now feel like I've brainstormed an appropriate solution. Enter my lesson plan that did not self destruct: the online student planner created with Google Sheets. This lesson plan was cocreated with one of the teachers on my grade level team. If you'd like to use a copy of this student planner, you can click here to make a copy for yourself and edit it as needed.

     My school uses Google Classroom as our learning management system. I usually love all things Google, but the digital divide that my students experience made using Google's Calendar function not the ideal solution. As in my previous post, I needed something that would work with what my students were familiar with and didn't need a lot of upkeep. But since being organized is really difficult to model and teach online, it also needed to be as comprehensive and student proof as possible. Which brought us to creating a planner using Google Sheets.

    There are a lot of templates Sheets has created, including an Assignment Tracker (have you ever scrolled down that far to see it?). This template was a great starting off point, but it seemed mostly for older students. Middle schoolers need way more structure. And, it lacked my favorite thing: check boxes.

Sheets' Assignment Tracker Template

 

    So here's what my grade level teacher and I did. First of all, we didn't reinvent this wheel too much. This template has many fabulous things about it, including the data validation drop down menus which are linked to a subjects tab at the bottom. So, we kept that and edited the subjects tab to include all of our students' core subjects and added those extras you would find in a planner such as "Reminders and Special Items." We also wanted to use icons so that students would have a quick visual reference, so we included an emoji for each subject.

How we formatted the Subjects tab from the Sheets template

 

    Next, we edited the main tab to be our monthly planner where the details of the assignment could be added. When a student writes in their homework, it would look like this.

How the planner would look when a student fills it in

    Then it was time for the check boxes. We wanted the check boxes to be "active" so that when something was checked off it would also strikeout the assignment name and change colors, indicating that an assignment was completed. To accomplish that, we had to use conditional formatting. Conditional formatting works as an "if -- then." IF a cell is filled a certain way THEN something else will happen. So we wanted the cells with the check boxes to work like this: IF the check box is checked off THEN the assignment name turns a different color and uses strike though. The result would then look this:

Conditional formatting add strikeout through text

    The final things were the finishing touches. We duplicated the tab and changed the month names to match our distance learning schedule. To protect our hard work, we added in protected ranges so that students wouldn't mess with the data validation and conditional formatting. We also used the "hide sheet" function to hide the subject sheet from view. 

    Our grade level will be giving this to our students as a Google Classroom assignment, which will automatically make a copy for each of our students.

    If you'd like a copy of this finished product, you can click here to make a copy for yourself to use.

    It is entirely possible to utilize a planner like this for your project management or other planning tasks. For instance, you could change "class" to the name of a person or department and change assignment name to task name. It's really about adapting it to your needs.

    As always, I'm happy to help if you need it. If you use it, drop a comment and let me know how it goes!