Using Excel 2007 for Progress Tracking in the Classroom

Today's author, Danny Khen, a Program Manager on the Excel team, discusses a solution he recently built using Excel.

The Manual Spreadsheet

Spreadsheets are the bread and butter of running the business world, where PCs are ubiquitous. The possibility, indeed even the need, to use spreadsheets in an environment where access to a computer is not taken for granted seems absurd to us, as business users and technology professionals. We do still print reports for some output scenarios, such as executive reports and meeting handouts. But as a whole, using a computer for day to day interaction with a spreadsheet application is so obvious that it's almost embarrassing to state.

Well, meet the preschool classroom.

I won't risk talking about the "typical" preschool classroom in the early 21^st century, because I only know one of them - the one my youngest daughter used to go to, and where Michal, my better half, teaches. Let's cautiously say that at least some of them are not yet IT-enabled. This particular one does not have a single computer on premises. And most staff members are novice computer users at best. Which does not mean that they don't have excellent reasons to use Excel (don't we all?); they are just not always quite aware of those reasons.

Enter Michal: a Montessori preschool teacher. Michal has a CS degree (*** Laude - there, I said it, I can safely come home tonight) and a previous software development career and, as a computer user, she just loves Excel. So she set out to solve some of the tracking and reporting needs that she had as a teacher by using Excel, the tool of her choice, and applying it to her, well, IT-challenged work environment.

Progress Tracking (Take 1)

The problem

The Excel workbook that this article discusses is used to track a teacher's work with each child in the class and the progress each child is making. This is a Montessori school which, for the purpose of this article, means that there are several learning areas, each broken down into many steps, and that each child progresses at their own pace. Teachers regularly give 1:1 lessons to children and need to track which steps they have covered with each child during a lesson and how the child is progressing towards mastering each step. Finally, it is important to be able to collect this information into a report. This report will be presented as supplementary information at the parent/teacher conference at the end of the school year.

In short, the classic story: Data Entry; Tracking; and Reporting.

The solution

The parts of the problem that Michal approached first were data entry and tracking. The main challenge is that several teachers have the need to collaborate on collecting, writing down and monitoring the data and there are no computers in the classroom. Michal designed a solution by which each input sheet and its associated report are one and the same and collaboration is done on paper. It goes like this:

• The sheets are designed in Excel and printed out, one per main Montessori learning area.
• The printouts are hung on the wall in a corner of the classroom (hidden from visitors' eyes).
• Whenever a teacher observes a child progress on a topic, she marks it in the appropriate worksheet cell on the relevant printout.
• Once a month or so, Michal yanks the sheets of paper off the wall, takes them home, updates the master copy on her computer, and prints new versions of the sheets for hanging in the classroom. This update process is not very time consuming; it has to do with writing 1, 2 or 3 in the cells that teachers changed during the passing period.

As she was working on this, I introduced her to Excel 2007, and she immediately saw the potential of one new feature in particular - the icon sets that can be used as easy-to-visualize KPIs. Here's a sample of what her sheets look like, after several update cycles (student names are fictitious of course):

Teachers mark down day-to-day results in the blank cells. Michal then updates the machine version with the status - the numbers 1, 2 or 3 - and Excel's conditional formatting does the rest.

One interesting fact about this solution: it breaks the normal design pattern of separating input data from nicely formatted sheets. Input is done on the formatted sheets and the sheets are structured in a way that optimizes manual input and tracking at the expense of having source data that's easy to process. For example, it is rather hard to build BI-style reports using this data. It would be much easier to build a PivotTable of some sort using this data if the input was organized in records such that there were columns for name, area, topic, lesson, status and so on and a row per "event" (lesson given). But in this solution, which focuses on optimizing for paper usage, the sheet is organized as a sort of manually laid-out cross-tab.

Notice also the usage of Page Break Preview mode (the "Page 1" watermark; the blue page borders). It is important to be able to lay things out and adjust widths and heights based on the need to print the sheets.

The reporting part of this phase of the solution was still completely manual: Michal designed a report template in Excel, and teachers would photocopy the template once per student and fill it out based on the tracking sheet results. Remember, I am talking about filling out the form in the pen-and-paper sense. The process is error prone and errors do indeed occur. Here's a portion of the report template:

Success!

Incredibly enough, with all its manual weaknesses and error prone report creation, the solution was enthusiastically received and has been successfully used for over a year now by Michal's fellow teachers. Here's the feedback from the field:

• The solution is easy to explain to new (even IT-challenged) teachers.
• Promotes communication among teachers.
• Input is simple and quick; low bar to usage (no real reason to avoid doing the input).
• New teachers or subs walk into the classroom, and easily figure out at a glance what lesson needs to be given to each child, as if they really knew those children.
• Teachers in other classrooms asked for, and received, empty copies of the tracking sheets; they added their students' names manually on the left, and have been using the sheets for pure manual tracking - without even the benefit of periodic cleanup - and are very happy with it.

Progress Tracking and Reporting (Take 2)

The problem

The workbook above was a great start and has been successfully used for over a year now. Still, Michal knew about various improvements she wanted to make but just didn't have time for. So I offered to put some of my time into it and asked her to give me the functional specifications or, in human speak, to tell me what's still missing and what else she would like this solution to do for her. She came up with a few things:

• The workbook has several sheets, one per main learning area (Math and Sensorial are two used in the article examples). The student names are completely manual; it is hard to add/remove students and synchronize the different sheets.
• Teachers need to be able to see some supporting information about the students. For example, it is important to know if a child is in kindergarten age; it would be helpful if we could calculate this from his date of birth and indicate it on the sheets.
• Writing up reports for the end of year conference is a huge, laborious task. Michal wished they could be generated. She wanted to be able to print a report page per child; and she wanted to keep snapshots of the children's progress.
• Whatever we did, we had to keep in mind the key scenario: these are printed sheets, hanging on the classroom walls and written on by the teachers. This is the spirit of the solution. Without it, the solution loses its power.

The solution

<Several nights pass>

Here's what I did (complete solution attached to this article):

• Added a Students sheet. It has a table with students' names, as well as dates of birth, and calculated ages (displayed as years and months - using /12 fraction format).
• Using the current school year, I calculated for each child if they are in kindergarten age.
• The tracking sheets refer to the Students sheet and add conditional formatting to student names - showing them in bold if they are kindergarteners.

• Note that the source student table can be sorted and filtered to one's heart's content. Other sheets always use =VLOOKUP in order to fetch a student's info from the table; reordering the table does nothing to the order of rows on the tracking sheets. Why is this important? Right! Manual data entry. To avoid confusing teachers, the student list on the tracking sheet should not change from print to print. But whoever uses the machine version may well want to reorder the student table, e.g., to sort by age.

• I added support for report generation. It has 3 main parts:

• A report input sheet, on which a teacher enters some parameters for a student's progress report, such as the date and the student to report on. In addition, the input sheet has a table with the textual comments that the teacher adds for each child.
• A report sheet. This is based on the template in the original solution (with some more formatting), but it pulls data for the selected student from all around the workbook - the various tracking sheets and the report input sheet. The status terms of "Introduced", "Working" and "Mastered", located next to the learning steps, map to the icons on the tracking sheets.

• This is fairly rudimentary. E.g. I look up a student by name on the tracking sheets (good), but I then grab a topic's status for the student by hard-coded column indexes to VLOOKUP (bad). This could definitely be improved.

• Bulk report generation code. This is a VBA macro that creates a new workbook, goes over the student list, and generates one report sheet per student, as snapshots that show data for each student. I also added a button that runs the macro on the report input sheet. The generated workbook can be printed out and the printed pages distributed on the conference night; the workbook can also be saved as a results snapshot file.

A student report looks like so:

Success?

Still remains to be seen, as this new version has not been used yet. Michal is happy about the better maintainability and robustness of this workbook. She is particularly excited about the bulk report generation. Being able to generate dozens of reports in seconds, rather than hours, opens up possibilities she did not have before - such as reporting back to parents in shorter intervals.

Conclusion

I hope I was able to demonstrate how Excel can be used in a somewhat unconventional way, combining manual input and tracking with report automation. Excel is flexible and powerful enough to even lend itself to such a hybrid solution.

Attached to this article is a copy of the progress tracking and reporting workbook, which has all the elements discussed here, including the VBA code to auto-generate multiple snapshot report sheets.