Setting up a sheet well for someone else to use, whether that sheet is for students or teachers, can make or break its success. This post will focus on conventions that I use to make my sheets easier for users. That being said, just as curb cutouts started as a way to make sidewalks easier for differently abled people, they actually help everyone. These conventions will help everyone who uses the sheet, including you!
Often organization elements are left with header color and alternating rows. This isn’t surprising because traditional spreadsheets are table based - basically accounting tools. This is leaving a lot of the table, especially if you are working with folks who have limited spreadsheet experience. Take the two examples below of the same gradebook template. This template is meant to be used by middle school students.
Here the goal is to teach students some introductory spreadsheet calculation skills. I would argue that the first example is opaque and even anxiety provoking. The second example uses color to focus attention on important elements, includes a Key to give the user context, and even uses a “Spreadsheet Assistant.” This handy box contains an IF/THEN/ELSE statement that changes as the user begins to build the sheet, giving the student a sequence of steps. Doing it this way takes more set up time, but it will save you and your students so much frustration on the other side. This tutorial will briefly talk about using color, making an IF/THEN assistant, locking elements so that users don’t change the wrong thing, creating a data feed, and will introduce a discussion about distribution.
Lets Talk Color and Merging
We are visual creatures. I love a good table but only when I need to organize lots of data. All those columns and rows can be visual noise if your project is more of an interface. Simply put, making the cell and border colors the same creates a cleaner visual experience. The lines can disappear where they aren’t needed. Cells can be merged to create specific areas with a larger text box, as with the spreadsheet assistant in the example. The border tool can add an underline, bring all the rows back, or simply create a box around an area, like in the Sheet Key example above.
The IF/THEN Assistant
IF/THEN statements are incredibly powerful and can be very complex. These statements can look for matches, include OR statements, employ mathematical formulas, and so much more. The statement that creates the Spreadsheet Assistant can be quite simple. First, consider the steps that you would like your students to follow. Make a chart on your variables tab with a step number column and an instruction column and start to brainstorm a lesson sequence. You can type these directions directly into the IF/THEN statement but I find using a chart easier to plan and edit later.
Once your instructions are laid out, the statement is easy to compose. The syntax of an IF/THEN/ELSE statement is:
IF(logical_expression, value_if_true, else)
My first instruction looks to see if any scores have been added to the score column (I). I’ll do this by using a SUM function for the entire range, just in case a student adds a score next to a date further down the list. If the sum is less than 1, then the assistant box will contain instructions for step one on the variables sheet (Variables!C3):
My ELSE statement will be the condition for the next instruction. In this case, whether or not any percentages have been added to column J. If not, the second instruction will load (Variables!C4):
=IF( SUM(I3:I)<1,Variables!C3, IF( SUM(J3:J)<1, Variables!C4,))
And so on! The nested IF/THEN statement can provide steps for the entire project!
We will use a tool called Autocrat to distribute copies of the gradebook template to each student in your classes. When we do this, you will retain ownership of each sheet and you can control the level of sharing that you students possess. In this case, each student will be an editor. Because of this, you can lock particular sheets, ranges, even specific cells, that the students won’t be able to edit. Some careful consideration should be made into what cells are locked. With our gradebook, we can lock everything in the columns between A and H. Those will be teacher columns. We also want to protect the Variables sheet, at a minimum. When the sheet is distributed to your class, these protections will be maintained. Google provides a nice guide to protecting sheets and ranges here.
Creating a Data Feed
The gradebook has to be useful and easy to use. One way to do this is by feeding in data and reducing the amount of text input for students. We are going to feed in the dates for the school year, the days of the week for those dates, the assignments and tests corresponding to the dates, and the maximum score possible for each. All of this information is fed from a master sheet. The master sheet is a lesson unto itself. This automatically creates a list of dates between two given variables, making it easy to reuse each year. You can read about how to build it here. The data is instantly sent to each student’s sheet whenever the teacher adds an assignment to the master and setting it up is easy. We will be using a function name IMPORTRANGE which allows you to pull data from a different sheet. The source sheet must be shared with the user in order for the import to work. None of this data is sensitive so it will be easy to share the source sheet as read only. We can turn off notifications, too, so that it doesn’t send an email when shared. The syntax for import range is simple:
The first condition is the url of the source sheet, surrounded by quotes. The second condition is the location of the data range in the sheet, also surrounded by quotes:
There will be no data when the student opens the gradebook for the first time. The student must give permission to the connection. I usually create cues to help this process. In our example sheet I write “Click B1 & then “Allow Access” and point to cell B1 which I have made green. When a student does this (even though it is protected and the student can’t change the function) a button will appear requesting him or her to “Allow Access.” The data will feed in once access is granted!
You cannot distribute your sheet to your class until it is completely ready. We will have a separate tutorial later about using the Autocrat extension for this purpose. In the meantime, you can set up a spreadsheet with your class rosters. This sheet will need a row for each student. The data should include the student’s first and last name, and email address at a minimum. Additional useful information could include period number, parent email (you could share it with parent as a read only doc,) learning resource teacher or case manager email, and more. When autocrat is run you will have a handy link to each student’s gradebook added to each row.
Next time we will work with an autocrat and go into the details regarding document distribution.
Create a sheet in the gradebook with useful information for your students. Add a chart with the grading scale, create a reference for math symbols and share where to find them on the keyboard(^ is Shift + "6"), provide a list of tips for writing a math function (eg: All formulas must start with an equal sign)