You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today's author, Danny Khen, a Program Manager on the Excel team, discusses a solution he recently built using Excel.
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 21st 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.
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 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:
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:
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 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:
<Several nights pass>
Here's what I did (complete solution attached to this article):
A student report looks like so:
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.
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.
Thank you for taking the time to put this post together. My son's school has similar challenges. I believe your solution might be useful for them as well.
In their case, they have 18 children per classroom, 6 of whom are autistic. 1:1 sessions are tracked for every student, but for the autistic students additional information is tracked throughout each day.
I think your tracking system and report producing macro will be a dream come true for them.
Bruce, thanks for the kind words. For a good cause such as this one, I would be happy to put some more time of my own into helping them out to tailor this into their specific needs, if they came up with some ideas. You can contact me thru the blog's email link (blogs.msdn.com/.../contact.aspx) with an email address if you wish to get in touch.
I am sure template impressed the Teachers. However there is lot of scope for Improvement
1)Keep the Data Entry Tool, Data and Reports in Seperate files - Even if the school has one computer and one person operating it.
Data Entry Tool - Should be a Template where you can select the subject, sudent etc - Which Pushes Data in to the Database - When you click on the Update Template
Reporting Tool Pulls Data form the Database
Database which stores data in a list.
2)Make use of Dynamic Name istead of Staic Names (Use Index /Counta, rather than Offset)
3)Use Match/Index instead of VLOOKUP
4)Use the still undocumented DATEDIF to compute difference between two dates
4)In code when refering to sheet names use their code names rather than their display names
5)When you want to copy a sheet with more than 255 characters in a cell - like the comments cell dont copy the sheet. Select all the cells, add a new file paste
Thanks for these suggestions. I did not intend to completely rewrite the solution, make it suitable for other usage patterns, or make it 100% robust. I just took an existing workbook and added a few improvements to make it a bit more useful for that set of users. My main point with this post was to demonstrate a somewhat non-standard usage of Excel. Your proposals would certainly make sense in other cases, unfortunately they are beyond the scope of the resources I intended to invest in this project...
Looks interesting :) One question that I could not figure out: What's the mechanism that populates the students drop down list in the ReportInput form?
The mechanism here is data validation (Data tab -> Data Validation). In this case, the validation type is "List", and the source data reference is "=Student_Names", which is a name defined to point at the (hidden) Name column in the students table on the Students sheet. That hiddent column combines first and last names.
This is amazing!! I'm a Montessori Lower el teacher and have been scouring the internet for ideas on how I could do this. There are web based programs out there but a) they cost money which I nor my school has, and b) all of my data is already in excel and the thought of copying and pasting the hundreds/ thousands of lessons I already have typed up in excel stresses me out, since, I already tried it. My only issue here is I'm not an excel guru at all. I can probably figure out the the student sheet and possibly even the tracking sheet. I'm not sure I'll be able to get the parent report to work, but I'm sure as heck going to try!!! You could totally hold a class on this at Montessori Conferences everywhere!! Again, thank you for posting this!!!