The final day of the “A Week With…” series of Google Sheets walks you through how to make a grade book that you can share with students. If you’re school doesn’t have an online grade book that students can see updated grades at any time. (My philosophy: If I can check my bank account at any time, students should have access to their grades – it might have somethings pending but it typically updated within a few days.)
April 18th – “A Week With…” Google Sheets creating a Gradebook
Creating a grade book isn’t an overwhelming task – just putting together the skills we’ve learned here and using two new formulas to link sheets and spreadsheets together to give access. In “pseudocode” (or plain english), we are going to create one spreadsheet that you can input all the grades onto and update as more grades come in. Then you’ll create an individual spreadsheet for each student, share it with them (and parents if requested) and link it to the original spreadsheet and if you give yourself enough room, you only have to worry about the original because the shared will update.
In the GIF above I take you through a sample grade book I’ve setup. Rows 1 – 7 give all the information including the number of assignment (row 1), description of what it is (row 2), marking period (row 3), category because they are weighted (row 4), date (row 5), possible points (row 6) and a “=HYPERLINK(” formula to the presentation so the student can have a reference point for that grade (row 7).
Rows 8 – 18 are students, and then the columns are the assignments. I have a few sheets on the bottom:
- Grading Sheets are links to the individual students
- Extras are assignments that I’m not using
- Vertical is a spreadsheet that has a “=TRANSPOSE(” formula (in cell A1) to flip the grade book from horizontal to vertical, which was feedback and easier to read for students
In the GIF above, I preview the Vertical sheet which uses the “=TRANSPOSE(” formula (in cell A1) which takes the vertical columns and makes them rows. Then I preview the “Grading Sheets” sheet with uses the “=HYPERLINK(” formula to link to another spreadsheet which I’ve shared with students. Every student has the same “=HYPERLINK(” command in cell A1 because it’s just the general information from the first 7 columns, which were the first 7 rows above. The students score is the “=HYPERLINK(” and importing only their column (the example has column H with rows 1 through 92 to give myself plenty of room). If a student tries to change anything, the scores disappear so they can’t fool the system.
In all honesty it took me a little while to get this right while getting feedback from students. In the “Student 1” spreadsheet the student can see all the information as they scroll down the page, which makes sense when you are the only one there, but in a multiple student view it makes sense for the students to be rows that I can easily fill in scores. This also leads into a great conversation about user experience and students can write about this, cross curricular mic drop.