I’m Spreadsheeting My Heart Out, Part 1


Last school year, my principal explained to me that she wanted to do practice prompts in the computer lab to help students prepare for the AIR test. This school year is the first where students have to take the test on a computer. In previous years, this has always been an option, and we’ve always gone with pencil and paper. So now we have to make sure students can transfer their writing skills from paper and pencil to keyboard and screen.

On the AIR test, students will have to read a passage; read a question about the passage, and answer the question with a typed response.

Not gonna lie, I was super duper dreading it at first. It sounded like the opposite of fun. But when you don’t have a choice about what you have to do, you still have a choice of how to do it, so I went with cheerfully. And if I was going to do that, I was going to take ownership of this whole thing as well.

We collaborated with other teachers on a rubric to use, and settled on one with thirteen points, spread across different areas: Organization, Evidence & Elaboration, and Conventions. We looked at the calendar and selected multiple dates to cycle this activity; that way we can make sure students continue to improve, instead of treating it like a one-and-done. I figured out the resources that worked best to suit my needs, my students’ needs, and my admin’s need: a combination of Google Classroom and Edcite.com. My principal picked some passages and I wrote questions for them. And then, over the course of a week, my students came in and took their “pretend” test. Despite knowing it was “pretend,” they took it quite seriously overall. We gave them a paper copy of the rubric, so they could do pre-writing on the blank side and use the rubric checklist on the other.

Then I assessed all their responses against the rubric.

Then I printed all their graded responses out, and stapled them to the rubric sheets they used during the activity. My principal wants to share these with homebase teachers during TBTs.

That was all I needed to do. But it wasn’t all I wanted to do.

I wanted to see where students succeeded and where they failed in the task so I could plan future instruction around it. And since I was keeping a spreadsheet of their scores anyway, I just took it a little further.


One thing you should know about me is that I love conditional formatting. (Also I hid or blacked out columns with identifying information before taking this screenshot.)

So I made a horizontal representation of all the rubric criteria. Then I set some conditional formatting into the field: pink for an empty cell, blue for a not-empty cell. The not-empty cells also turn any contents into the same shade of blue. I just thought that was easier to understand, visually. Anyway, if a student got that point on the rubric, I typed in a “1,” turning the cell all blue. If they didn’t, then I left it blank – pink. I went across the student’s whole row like that. Column T was a double-check, a sum formula adding up all those invisible number one’s. If the number was different than the number in coumn F (copied and pasted from Edcite reports) then I knew I needed to double check something.

Then I took it another step further. The remaining columns towards the right are goalposts for students to reach in the next prompt activities. For example, a student that got 5 points of 13 in the first prompt needs to get 7 points in the next one to stay on track to be considered proficient overall. (We’re aiming for 10/13 for everybody.) I didn’t just pick those numbers out, either. I used a formula that helps with reasonable growth expectations. That kid who got 5 points this time? It’s not reasonable to expect them to get 13 on the next try. But 7? That’s do-able. But if they remain at 5, or worse, dip down lower, then I know that kid might need further intervention to succeed. And I can start that intervention in November instead of February.

Kids who got 2 or fewer on the first task, though – they won’t reach 10/13 points by the end by my formula. They need intervention nowEdited to add: My principal points out that, even if they don’t reach the goal of 10/13, a student who goes from a 0 or 1 or 2 to a 7 an 8 or a 9 has still made incredible growth that merits celebration.

Then, when I changed the view a few times, I realized that many kids were missing the same criteria. Not all, but many. So I wondered, which criteria are the most commonly missed?


I scrolled to the bottom of the data and, under each of the columns, I input a sum formula that added up all the invisible one’s in each column. So I was really glad I used 1’s instead of x’s in that moment! Once added, I looked at which criteria had the lowest numbers. So that highlighted 13 down there? That means only 13 out of over 100 third graders wrote a closing sentence in their response. (The pointer was in a different cell when I took the screenshot. 72 students used evidence from the passage and/or other sources.) So, closing sentences are a weakness for most of our grade, but using evidence from the passage is a strength. I can use this information to help plan my instruction, and I can share it out with other teachers so they can plan their own instruction and provide guidance and support.

So that’s the teacher side of my current spreadsheet mania. Tune in tomorrow to find out how I’m delivering feedback to students!

Virtual Endangered Zoo

I’ve been working with one of our second grade teachers (she of the superior graphic organizers) on a project with her reading/social studies class.

We have now reached the culmination of our project, and the Virtual Endangered Zoo is now open for business! Each child researched an endangered species of their choice, and built a website about them. Their teacher, Mrs. Pancake, created a hub website where you can easily access all their sites.

This was a fun project that also turned out to be easier than I thought it would be. Firstly, students did research projects earlier this school year, so they already knew research methods basics. Secondly, Weebly For Education was very easy to use once we played around with it. We discussed other ideas such as publishing an eBook, but I thought more parents would be able to see a website than would be able to download an eBook. Plus, Weebly uses responsive web design by default. This means that their sites adjust accordingly when viewed on a smartphone or tablet. My guess is that means even more of our families will be able to see our sites, since not every family has a computer hooked up to the Internet at home, but many may still have smartphones.

In addition to research methods we used in the past, we added a social media element for kids who were up for it. When a student got stuck with one particular detail, we sought out a zoo or aquarium we thought might know the answer. Then, we tweeted them. Students wrote their question on a dry erase slate and I took a photo of them and tweeted at the zoo or aquarium. This got us around Twitter’s 140 character limit, and I think it also displayed to others that these were real kids asking questions.

How long does it take to tweet a zoo? Minutes, fellow educators. Mere minutes, even if you include a photo or a video. (I’m trying to convince more of my coworkers to sign up for Twitter, can you tell?)

On Weebly, we could even embed the responses to our tweets thanks to the “embed code” widget!

(Another thing I really liked about Weebly for Education was its image search. It has its own search engine for images, and if you include a free-to-use image, Weebly automatically appends the site with a Creative Commons attribution. Digital citizenship win!)

I would like to thank the following zoos (particular whoever runs their social media accounts) for their help:

The students who did not use my Twitter account still may have used social media in the form of Youtube. We used specific search terms and checked that videos we put on our websites were from sources we trusted, like the Oregon Zoo or National Geographic.

Students who finished early also entered the Akron Zoo’s snow leopard naming contest that we discovered from looking at their website. So if anyone at the Akron Zoo peeps this, sorry for the sudden influx of multiple entries from my and Mrs. Pancake’s email accounts!


Thank you again to the zoos and aquarium that reached back out to us over social media. I got excited simply because I’m a giant nerd, but our students were excited because they felt like someone out there was listening to their questions and taking the time to answer thoughtfully. It’s hard to put into words how respected that makes a kid feel, to be taken seriously by an adult they don’t already know. So thank you for taking the time to teach us about animals, as is surely your mission, but also thank you for making the effort to reach out to a kid hundreds or even thousands of miles away.

My Personal December Challenge

I am challenging myself to write a blog post for every day this December.


Because over Thanksgiving I realized that writing helps me organize my thoughts and process my feelings. I hosted Thanksgiving for the first time this year, and I wrote out a lot about my plans, ideas, and choices to my brothers and sisters in a private Facebook group. I think it helped reduce the stress of the holiday, and it turned out to be a very enjoyable time.

Because currently I am teaching a writing class for my workplace’s after school program. While I am focusing on creative writing in the class, I think it helps me to be a good role model if I make sure I also make a point of writing when I can. Part of my struggle is worrying about whether a thing is “good enough,” and if I cling to that worry, I may never get anything done.

Because I haven’t been doing the podcast. The single biggest issue I have had with the podcast is finding enough quiet time to record it. I don’t have children of my own to be distracting me, but I do have a spouse and we share a small apartment. I don’t need it to be so quiet to write, but I do need it quiet to record, and unfortunately we don’t have access to an ideal space. The biggest reason I did the podcast to begin with was simply to prove to myself that I could do it; to have that knowledge in case I need it in the future; and to stick with it for at least seven episodes. I did all those things, and I always have the option of picking it back up in the future. Luckily I don’t think I have a big enough audience to be disappointed in me for letting it trail off.

Because WordPress allows me to save drafts and schedule posts for later. I can always begin writing about one topic, save my progress, and end up publishing a different one. I could also write about several topics in one day, then schedule them to publish one at a time.

Maybe if I can do this, I can actually make a New Year’s Resolution that I can keep in 2016!

Trying Twine

Twine is an open-source tool for telling interactive, nonlinear stories. (At least, that’s what the website says.)

I thought I might use it with my after-school group this quarter, as we’re working on creative writing. I could see it as a way kids can easily write and share a choose-your-own adventure type story. If we get that done, I’ll try to share their results (or at least my reflections).

I made one but it’s mostly a vehicle for bad jokes.