Keeping track of lessons — Excel
As you might have noticed if you read my first post about using Evernote, I’m a bit of a nerd, and I like keeping things organised.
Initially I wrote my lesson plans on paper, and recorded key details about the lessons in the spreadsheet (as well as my reflections in Evernote), so I could look back. This spreadsheet also acted as a checklist at four key points; did I have what I need to start planning? after I had planned the lesson did I include key things? were all the resources made and printed? then just before I taught — was everything ready?
After the lesson I graded myself against my University criteria. Whilst I don’t believe grading lessons for established teachers is a useful process (more about that some day), I think it is useful for trainees, and I found this self-reflection useful. At some point I did graph this data, and you could see dips when I was ill or busy with University assignments, as well as at the end of each half-term…
As an NQT this all got a bit simpler, as I was no longer required to complete lesson plans. My first week I thought “I’m a teacher, I’ve got this” so I just modified the powerpoints/made my own powerpoints, and wrote a few details on my requisition form (for non-science teachers — science teachers need to order their practical equipment from technicians about a week ahead, on a req form). After a few days it was clear this wasn’t enough, so I built a quick spreadsheet, which evolved into something like this:
Each period of each day has a row, then lots of columns: Class, lesson title, Aspire & Challenge (Outcomes at my school), and then a box that combined them with some extra words ready to copy onto the board (a requirement).
Next up a column for practical equipment required (this was copied across onto an automatic req sheet), a reminders column for me (stuff I needed to remember during the lesson), To-do (things I needed to do before the lesson). The reminders and to-do boxes automatically turn red when they have something in them, so hopefully I didn’t miss anything. I turned rows green when they were done, or grey if I didn’t need to (such as ITT’s teaching lessons) or the class out on a trip.
You can see columns for Starter, Activities, Plenary, and that these are blank, as over time I stopped using these as the planning/teaching process became more automatic for me.
Last two columns are for a quick review of the lesson, and a box to cross when I’d marked that lesson to keep track of it.
Again, it might seem a lot, but it’s so flexible compared to a paper planner:
Automatic requisition form Just change the date in the top left corner, and it pulls the classes, titles and equipment from the main sheet into a format the technicians were happy with.
Plan sheet I copied across titles from schemes of work, coloured out lessons if the other teacher was delivering that topic, then bolded them once I’d taught them. Great to have all of this in one place, and flexible and easy to shuffle around.
Keeping track of marking: Because I crossed off each lesson when I marked it, I could produce this dashboard summary really easily with a pivottable or two. It shows how many lessons I hadn’t marked, and how many days since I’d marked. Both boxes change colours as I approached the schools marking policy! This screenshot is from my phone, again showing how flexible an Excel markbook is.
Filter to see a class: Marking books or revising with a class is easy, because you can filter the main sheet by class, and show them the lesson title and outcomes. Great for kids who have missed some lessons too.
Print a week or two: It’s so easy to print a block out (Just select it, and then use Print > Print Selection in the settings), so you can carry around this week, scribble on it etc.
Keep it on OneDrive or Dropbox: If you keep it stored in the cloud, on OneDrive or Dropbox, then it automatically backs up, you can access it from anywhere and you don’t need to worry about losing/forgetting/damaging it. You can also access it on your phone, which is handy in staff meetings when you get told you need to remind kids to do a thing in Lesson 1 next Tuesday, or if you need to note a trip is going on.
Again, shout if you’ve got any questions! @mrbakerphysics.
I’ve shared it on Dropbox. Let me know if you find it useful or have any questions: (File updated for 23/24 dates)