Our New Assessment Calendar

Posted on Sep 20, 2015


Historically at LPCUWC we centralize student assessments on a paper calendar in the Staff Room.  Not all assessments were consistently updated, testing conflicts were seen but unresolved, and its utility has been limited.  In May 2015 our Director of Studies asked me to look at the a spreadsheet that would be the assessment calendar for the new year.  Over a weekend I shaped the Sheet, making it easy to work with and easy to maintain, and layered with functionalities that help both teachers and students.  This is the corresponding How-to Guide, designated for students and teachers.

Even though this was designed for in-house use, the basic principles would work for other schools.  The For Administrators section describes how to customize the Sheets for your institution.  Lastly, the For Tinkerers section opens up the innards of the calendar for you to see and tamper.


The assessment calendar (for each year group) exists as two Sheets document, one for teachers to enter assessment details, and the other master for students to see their own assessments.  These two documents are linked, so that whenever the teachers’ version is updated, the changes are immediately reflected in the students’ version.

Changes in teachers' version is synchronized to students' version.

Changes in teachers’ version is synchronized to students’ version.

The Teachers’ Version facilitates input and visualizing the assessments present for the entire year group.  Broad navigation is available as the outside block, and detailed navigation as the inside block:

Color-coded, multi-grained navigation

Color-coded, multi-grained navigation

Special days outlined in the Assessment Policy is highlighted in yellow, and holidays are shaded in gray.  When an assessment is entered it is automatically colored according to the type.  To facilitate entering assessments, the common options can be selected from a drop-down menu, and if you are a keyboard kind of person, autocomplete is also available.

Color-coding of days and assessments; data entry facilitation.

Color-coding of days and assessments; data entry facilitation.

A special representation is used to help visualize assessments.  In the following graph, the flow of time is indicated on the horizontal axis, such that later months appear to the right of earlier months.  Vertically, different subject groups are displayed on its own line (color-coded as with the broad navigation above; core (ToK and Extended Essay) displays as Group 0).  Each assessment item appears as a block, which displays the details on the right panel when clicked.

Timeline feature in the Teachers' version

Timeline feature in the Teachers’ version

The Students’ Version has three parts.  The first part, on the upper left, lets students choose their own subjects.  They can do so from a drop-down menu, or by typing and then using the autocomplete assistance.

Once the subjects are chosen, the Task List on the upper right is automatically populated with assessments that are relevant for that student.  Each entry is shown with the details (e.g., scope of test), and the number of days before the item “arrives” is color coded (green: a week or more away, amber: getting close, red: within 3 days).

The same timeline for visualizing assessments can be found on the bottom.

Features of the students version

Features of the students version

In the next sections we will look at doing some common tasks.

For Students

Here are the links to the spreadsheets:

Creating your own version

The first thing you want to do is to create your own private Sheet from the links above.  To do that,

How to create a customized copy

How to create a customized copy

  1. click on File,
  2. select Make a Copy,
  3. Choose a sensible name, and
  4. Update your subjects

You should now see the assessments populated in both the Task List as well as the Timeline.  If you need a video walkthrough you can watch this here:

What about future changes?

Any changes that any teachers made is automatically pushed to your Sheet.  You are done.

For Teachers

One thing that could go wrong

With the Teachers’ Version, one of the common hiccup come in the form of this error:


This usually means that you are trying to access a portion of the sheet that has not yet been downloaded.  You should reload, and then try navigating using the detailed cells (as opposed to using the groups and big jumps), or waiting longer before moving around.

Accessing timelines

There are two timelines that you can access.  The first one is embedded in the first sheet (next to Week 3), and the second one is a full-window display which you can access from the tabs on the bottom.


Adding an assessment

To add an assessment, navigate to the date, and find your subject group.  You can then enter the information (subject, teacher, block, comments) by typing (and using the autocomplete), or using the drop-down selections.  If you noted a red triangle on the upper-right, it means that the value was not expected.  You are welcome to keep them; subject being the only exception, the spreadsheet will second-guess you but respects your decisions and not get in your way of having a joint test with two teachers.

Subject is the only item here that takes extra care, and I needed to be opinionated here because this value is what links the students’ selection on their sheet to your input.  Each subject that is accessed must be on its separate line.  For example, if there is a test in a combined chemistry HL/SL block, the test must be entered in two separate lines (as “Chem HL” and then “Chem SL”).  If you enter an invalid value (e.g., “E Lang Lit”) the spreadsheet will reject the entry right away.  The only exception here is for “Maths (all level)” in which all students are assumed to be enrolled in.

The Teachers cell lets other colleagues know who they should speak to if there is a conflict.  This is not displayed in the timeline or the students’ sheet except for Group 1 and 2.

The Block cell is open to interpretation.  It can be used to indicate the blocks that have a test, as well as that the test is in a group block.

The Comment box let you add in any note that you wish to be visible to the students.  This can include the percentage of which the test is worth, the scope of the test, or just “don’t panic”.

Adding a new type of assessment

You can already add a new type of assessment by directly typing it in the assessment box.  If you wish to have the new type of assessment in the drop-down box, contact the Director of Studies.  (If you are the Director of Studies, see the For Administrators section.)

Adding a new subject

Please contact the Director of Studies.

For Administrators

Enabling the hidden sheets and columns

This spreadsheet is driven by several hidden sheets and columns.  To modify less trodden paths, you will need to access these hidden sheets and columns.

To access a hidden sheet, go to View -> Hidden Sheets, and select the relevant sheet.


To access a hidden column, click on the arrows separating two column headers.


Modifying teachers / subjects

To add a new teacher or subject, you need to unhide the Options sheet.  From this point onward, it is a simple matter to modify the values.  Note that subjects are grouped.  This grouping enables the drop-down menu to display only humanities for the Group 3 subject choice box.  If there is a subject that must be in two groups, you can place them in both groupings.  Having duplicated values does not affect the other functionalities.


Adding a new type of assessment

To add a new type of assessment, follow the procedure for modifying teachers / subjects.  Unhide the Options sheet, and add, edit, or delete values from the first column as appropriate.

To add an appropriate color code for an assessment, go back to the Data Entry sheet, and select a cell in the assessment column.  Right-click, select Conditional formatting.  Choose Add another rule, and set the new rule as desired.

Adding a new Special Day

To add a new Special Day, enter the comments on the Day Comments column.  Then select the entire row and change the background color.  The indication of Special Days are entirely cosmetic; the right for teachers to override special days / holidays is fully respected.

Adding new rows for a day

Adding new rows for a day requires first to break the merged cells.

Adding a new semester

Let’s worry about this later.

For Tinkerers

Getting to the Tinkering mode

How does the color-coding work?

How does the drop-down selection / autocomplete work?

How does the timelines work?

How does the comments in the timelines work?

How does the linking of the spreadsheets work?

How does the “show only relevant assessments” work?