Guest post by Kristin Stephens-Martinez, Assistant Professor of the Practice in Computer Science.
When planning out my class, I found it frustrating that I couldn’t easily layout what concept fell on what day. So to help me map out the concepts, I decided to build my own concept ordering tool in Google Sheets (clicking on this link will create your own copy of the google spreadsheet that you can edit).
The spreadsheet consists of two sheets: “Concept Order” and “Times and Constraints.” I recommend opening two browser windows to show each sheet separately. Originally, I tried to get everything on one sheet, but it was not possible for what I wanted to do.
Below is how to use each sheet. After both sheets are filled out, you can go back and forth between them to either recorder the concepts (“Concept Order” sheet) or add more constraints (“Times and Constraints”) until you are satisfied. Moreover, the sheets use conditional formatting to easily highlight pertinent information. Look up how to use conditional formatting if you want to add your own or change the colorings.
Concept Order Sheet
The purpose of this sheet is to list out all of your concepts in the order you plan to cover them and how much lecture time you believe that concept needs. The columns after the black Column C are an aid to help you see how much of the semester’s lectures you’ve “spent,” when a concept starts and ends, and how many lectures that concept will appear in.
Once you’ve finished filling out the concepts, this sheet is for easily moving concepts around without much typing or copy+pasting.
Column meanings
A. Concept – The name of the concepts
This column has some additional conditional formatting to help with seeing what is going on. Anything with the text “Assign” will be highlighted in yellow, “Review” in purple, and “Buffer” in grey.
B. Lecture Time – How long that concept will take to cover in class
C. Any columns beyond this are auto-filled and should not be changed
D. Lecture Time Total – How many lectures will have passed by the time this concept is fully covered
This is colored using a gradient from green (0) to yellow (23) to red (26). The purpose is to easily see how many lectures have been covered so far and when you are running out of lecture time. This assumes two lectures a week and two midterms taking up two lectures.
E. Lecture Start – The lecture this concept will be introduced
This highlights odd numbered lectures in blue and even numbered in green to help see when lectures number changes.
F. Lecture End – The lecture this concept will be completed
G. Lecture Span – The number of lectures this concept will appear in. This is highlighted where 1 is green, 2 is yellow, and anything above 3 is red.
How to use the sheet
- Fill in column A with the name of each concept. I recommend using short names so it’s easier to see everything.
- Fill in column B with how long it will take to cover that concept.
- Do not change any other columns.
- Rearrange the order of the concepts by “grabbing” a row and moving it to the desired position. “Grabbing” a row means hovering your mouse over the row’s number, holding down your mouse button, moving the mouse to your desired position, and then letting go of the mouse button.
Tips for using this sheet
If you cover concepts more than once, you will need to make a separate row for each time you cover the concept and label it something like “Concept N Part 1” and “Concept N Part 2.” This will ensure the constraints work and help you differentiate between the concepts.
Times and Constraints Sheet
This sheet is to help ensure the concepts appear in the appropriate context for your students. For example, if concept B must appear before students start a particular homework that is released the day of lecture 2, you can add a constraint that says “Concept B should be seen by lecture 2.” Another example is if you plan to have a midterm review you probably only want the review to appear in one lecture, therefore you can setup a constraint like “Review Midterm is a total of 1 day(s).”
As you fill-in and update the order of your concepts on the “Concept Order” sheet, the spreadsheet will automatically check your constraints. The first set of columns on this sheet are a reflection of the “Concept Order” sheet, but they should not be changed. They are there as an aid to fill in the constraints.
Column meanings
A-G. Same as in the “Concept Order” sheet
H. Constraint Okay? – This will have TRUE if the constraint is fulfilled and FALSE if the constraint is being violated.
This is highlighted where violated constraints are highlighted in red and okay constraints are in green.
I. Comparer – This describes the constraint being checked in column H and should be read as “[Item 1] [Comparer] [Item 2]”. The short name of the comparers map to the below list. So for example in the screenshot above for the first row “Concept B should be seen by lecture 2.”
- By Lec – __ should be seen by lecture __
- On Lec – __ should be seen on lecture __
- Total days – __ is a total of __ day(s)
- Same day as – __ is on the same day as __
- Before or on – __ is before or on the same day as __
J. Item 1 – A concept the constraint is being applied to
K. Item 2 – A concept or value for the constraint
How to use the sheet
To use this sheet it is important to always keep a copy of the H and I cells of the constraints you plan to use. The formulas in column H is how these constraints are checked. If necessary copy those cells to the bottom of your list of constraints as a backup.
- Choose the constraint you want to use and copy its H and I cells to the constraint’s row.
- Fill in column J and K for item 1 and item 2 of your constraint.
