Tracking Students’ Progress

  • Grade (term 1)
  • Grade (term 2)
  • Charts (term 1)
  • Charts (term 2)
  • Competences
  • Assess participation (term 1)
  • Assess participation (term 2)
  • Comments
  • Report (term 1)
  • Report (term 2)
  • Attendance
  • Special Needs
  • Accounts
  • Create groups
  • Student Picker
  • Seating plan
  • To insert the date, press cmd or ctrl + ; (be sure to go to Help > Keyboard shortcuts and activate Enable compatible spreadsheet shortcuts).
  • To display a time or a date (which will be automatically updated every day), use the functions =NOW() or =DATE(). To format the date, head to Format > Number > Date.

Grades & Competences

Enter your grades. The spreadsheet calculates the average. You get the lowest and highest grades or number of grades below, say, 10.

  • For grades, choose “Greater than” or “Less than or Is equal to”.
  • For assessing participation (see down below), pick “Text is exactly”.

Charts

With charts, results become more visual. You can create a chart for each assessment or a chart for all the results during the term (Insert > Chart).

Assess participation

Quickly assess participation by selecting in a drop-down menu the appropriate observation. A grade is automatically generated accordingly.

=IF(logical_expression, value_if_true, 

IF(logical_expression, value_if_true,

IF(logical_expression, value_if_true, value_if_false

)

)

)
=IF(B7="très bonne participation", 2,

IF(B7="bonne participation", 1,

IF(B7="suit mais ne participe pas", 0.5, 0

)

)

)

Reports

Produce a full report by selecting your appreciation in a ready-made bank. Then you just have to copy and paste the whole thing.

Attendance

Imagine you are out of school with your students (when the pandemic’s over of course). You want to make the roll call quite quickly so you don’t want or can’t launch Pronote. But you can share the sheet with the vie scolaire. Very handy during a fire drill.

Special needs

Like I said at the beginning of this article, I want everything to be in one place. So I used the IMPORTRANGE function to import on my spreadsheet the content of another spreadsheet.

Seating plan

My colleague Jean showed me this nice trick. Using the INDEX/MATCH functions, you can create a seating plan. Very handy when you want to place students randomly and mix them up a little.

  1. On the first one (the sheet is labelled “Liste”), you will find a list of your students. For each student, a random number is allocated. Using Filters, you can change the order of the names so they are mixed up. To assign a random number use this function: =RAND() (it returns a random number between 0 inclusive and 1 exclusive).
  2. On the second one (“Tables”), you have a simple sitting plan with only numbers.
  3. Finally, on the third one (“Plan”), you have the same sitting plan but (and this is where the magic happens) but instead of numbers, you have a function using INDEX and MATCH. It places random students!

Create groups

Following the same method as mentioned above, you can create groups of 2 to 6 students.

Pick a student randomly

  1. RANDBETWEEN
  2. COUNTA
  3. INDEX

Conclusion

I’m not a spreadsheet specialist and I’m pretty sure you must have plenty of ideas of what we could do with a spreadsheet to not only track students’ progress but to assess them as accurately as possible and help them become autonomous students as well.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Yann Houry

Yann Houry

Teacher and Director of Academic Research & Innovation @ Lycée International de Londres Winston Churchill