Teaching Computer Science: Census Spreadsheet

The offices of the US Census are closed, and their website is basically not working at the moment as a result.  But I found a way to teach the 7th graders about spreadsheets today, using data from the 1790 census.  You could do it too!

Each kid in the 7th grade American history class is writing a report about one colony in America in 1760: New Jersey, New Hampshire, Massachusetts, and so on.  I had each student get the broad-based census information from their state in 1790, and I’ve taught them how to use a spreadsheet program to generate a 4% decline in population from year to year, backwards into colonial times and 1760.  We had to do the mathematics for the 4% compound-interest decline with brute computing force, assigning each row of 30 columns to a year — I’m not that proficient mathematically to set up a 4% compound-interest problem for 30 years years, alas! — but we’re working out the data.

We then estimated that the broad categories of population available in the 1790 census would remain the same from year to year — adult white males over 16, adult white males under 16, all women, other free persons, and slaves.  These are dastardly categories if you’re interested in racial justice.  And frankly, the data we generated is garbage — nowhere in the world experiences 4% growth or decline anywhere, ever, while holding true to the specific categories assigned in the census in a given year.  But the numbers are still a damn sight better than a WAG (wild-assed guess).  

And in the meantime, students are getting to use a spreadsheet, and learning how to work from a data set over time.  The history teacher is thrilled. While the students are currently suffering from the technological challenges of doing mathematics horizontally and vertically simultaneously, and challenged by the concept of formulae operating off of cell values rather than variables… Wow.  A big computing hurdle is being cleared, one digital jumper at a time.

I’ve said before in this series of posts on teaching computer science, that “Digital Native” doesn’t mean what we think it means. It means “A person who was born after the widespread distribution of personal computing power,” and it doesn’t mean, “person who understands how to use computers naturally.”  All these skills have to be TAUGHT, in the same way as we who grew up before computers’ wide availability learned them.  

Liked it? Take a second to support Andrew on Patreon!


  1. Eh, looping n times is a more common and useful design pattern for middle schoolers to learn than the mathematics specific to compound interest.

    But if I’m reading Wikipedia right, I think this might be the formula in spreadsheet-ese:
    Where B2 is a cel that contains the current population and the result is the population 30 years ago assuming growth of 4% a year. For example, if there’s a million people today there were ~294,000 people 30 years ago. If you want to check it, take the result of that formula and feed it to this one:
    the result should be equal to B2 (possibly with some wiggle from floating point math and/or rounding).

    I love spreadsheets. I’d hate to have a job involving nothing but them but they’re fun to work with from time to time. Now show them pivot tables and say “Yer a wizard, Harry!”

    • Curtis, that’s brilliant. I had seen those tables before, but I didn’t know what they were called, and so I didn’t know how to go about coding one because I didn’t know what I was going to have to look up.

      I rarely do anything more than create flat tables and sort them, but I think the idea of pivot tables is brilliant. Now I have to learn how to code a couple, and see what I can learn from the information.

      But what I should really do is create a pivot table from this data-set they’re already working with — the 1790 census information — and show how the problem of slavery is already being encoded into the American political DNA. The pivot table can sort states by regions (New England, Middle Atlantic, South) for example. I wonder what other factors I could put into that table… A new powerful spreadsheet use!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.