We love the U.S. federal censuses, don’t we?  We need them. Right back to 1850, they’re our bedrock — or 1870, if you descend from enslaved persons. Earlier than that, however, we dread them. Fear them, maybe. Avoid them, to our peril. While limited in value individually, however, the early federal censuses become gold when compared to each other. Comparing them is a hassle, though, right? Not any more. I have designed an Excel-compatible spreadsheet tool that makes early federal census tally-matching a game you actually want to play. Let the Early Federal Census Worksheet restore your sanity.

---

The early federal census — where only the heads of households had names

On too many occasions, I took family branches back to the 1850 census, then stopped. The earlier censuses reduced most of the American population to nameless tallies. Rather than struggle with that, I would pick up another family branch. I avoided the hard work and went for the low-hanging fruit. My eventual work with the Beyond Kin Project woke me to the even tougher challenges of many African Americans. Enslaved ancestors remained unnamed in most censuses until 1870.

Despite these limitations, the earlier censuses can bring you vital information. They raise essential questions, if you have the patience and mental acuity to make sense of them, or, if you have a tool that does it for you. You can learn things by lining those heads of households and their tallies side by side, deducing whether a family in 1840 matches one in 1830. You also learn a lot by taking note of the changes in tallies — the places where things do not match.

It is tedious work, though, trying to determine if a”1″ that was put in the “Males: 20 & under 30” category in 1840 is likely to be one of the “2” that appeared in the “Under 5” category in 1820. And where did the other boy, the second of the “2” go? The acidic knots start to form in your gut.

Just as you get your brain around a theory, you have to shut down your work for the night. When you come back to it, you can’t remember what you came up with or why. So you do it over. Or, like me, you find another family line that makes you feel better about your skills. You promise to come back to this one when you have more energy.

The Mark Lowe pre-1850 census method, when hope dawned

My first hope that there was a better way to do this came in a class I took at the Institute for Genealogy and Historical Research at Samford University in 2015. J. Mark Lowe taught one of our sections. Mark is a very engaging teacher, if you have not had the privilege. He had devised a paper chart that lined up tallies for comparison of census data between 1800 and 1840 for the white male and female populations.

He makes the form available online: 1800-1840 Census Comparison Form. If you don’t want to use Excel, I recommend you use Mark’s form. You transfer the tallies to the appropriate boxes in the form, then see how each decade’s census information lines up.

The value of pre-1850 censuses started to make sense to me, after Mark explained this tool. I began to see these records as a truly useful evidence source for the first time. I started incorporating them into my research. The higher-hanging, more difficult fruit was finally in reach.

Creation of The Early Federal Census Worksheet

I’m never happy with paper solutions for long, I confess. With two dimensions, limited margins, and the inability to be two places at once, paper just fences me in. So I started fiddling with a digital expansion of Mark’s idea. I created an Excel spreadsheet and began to add features. In a later version, I added the capacity to track free families of color and enslaved persons. This extended the tool through 1860 — the last U.S. census that acknowledged African Americans as tallies.

I call this tool the Early Federal Census Worksheet (EFCW). (For those who don’t have the desktop version of Excel, I have a lite version of this tool. It is designed for use with Microsoft’s free Excel Online, Google Sheets, or other spreadsheets that can’t handle Excel macros.)

A video preview of the Early Federal Census Worksheet (Premium)

I recognize that some of you prefer to learn by video. Here is a YouTube video with a 9-minute run-through of the Early Federal Census Worksheet:

How the Early Federal Census Worksheet works

Here’s a snippet view with a single census record in it. I’ve logged the white males in the John Hillyer family of Ward 1, New York City, in 1840. Wherever I put a tally number into a cell, my spreadsheet will turn it yellow, to aid in comparison.

Scrolling to the right, you would see sections to enter white females, free black males and females, and male and female enslaved persons. These sections can be collapsed or expanded, using the plus and minus buttons above the spreadsheet.

Let’s say I want to compare John’s 1840 record with 1830 and 1820 records I suspect to be the same family. It might look like this:

The blue lines beneath each record show the age categories that correspond to the censuses. Where the census might say “5 & under 10,” the spreadsheet says “<10.” The person in the “<50” cell in 1840 is likely our head of household. The same person (if these three census records are for the same family) falls into the “<40” category in 1830. In 1820, he must have been less than 30, then. But he is at least 26 years old, allowing us to narrow down his age to something between 26 and 29.

The years across the top reflect the approximate birth years for someone in each age category. A person who is in the “<20” age category in an 1840 census, for example, would have been born in 1820 or later, as you see in the Year labels above the data.

The spreadsheet automatically shifts the view of the age categories for each decade, so that a tally in the “<5” age category in 1830 lines up with the “<15” age in 1840. If a boy was 4 in 1830, for example, he’d be approximately 14 when the 1830 census taker came around. The goal here, then, is to have that child’s records line up on top of each other, decade by decade.

Visually then, you can see how the tallies are lining up. You see a flow of yellow in a vertical line if you have a match. Looking at the family more broadly, you ask, does the family in one decade appear to be a viable match for the one in the next?

If you have a family line up like John Hillyer’s above, count yourself lucky, needless to say. In this case, every male child remains in the household — none dead or otherwise departed in 20 years. The data for white women, free black, or enslaved people strengthens or weakens the case that these three census records reflect the same family.

Families rarely stayed so intact, unfortunately. It is often in the changes, however, that we learn to ask vital questions. When a family member seems to disappear from the tally or a new one appears, we have questions. We ask about marriages, deaths, and widowed parents or orphaned relatives moving in. There is much to learn from changes in the tallies.

John Hillyer’s tallies have not changed, except for new children born after the last census was taken. His name and location do reflect differences, but the family tallies line up. Our John lives in New Jersey in 1820. But the next two decades show what might be him, but now in New York — an adjacent state. We can flag J. M. Hilyer of 1820 New Jersey as a valid possibility to be John Hillyer of 1840 New York.

Narrowing down possibilities in early federal censuses

One of the best uses of the Early Federal Census Worksheet comes in evaluating multiple people with the same name in the same decade to narrow down the potential ancestors. Let’s say you know the 1840 Heard County, Georgia, census has your ancestor James B. Anderson. And you know the corner of Heard where he lived was still Troup County in 1830, and with the tool, you have the probable man, Jim Anderson, in 1830. He disappears before that.

You know from later censuses that he was born in North Carolina, but you don’t know when he came to Georgia. So where was he in 1820? You have to evaluate all the potential James Andersons in Georgia, South Carolina, and North Carolina — and possibly other places — to see where he might be.

In the example above, you see multiple men in the 1820 censuses with names that could be a match to the James Anderson of 1840 and Jim Anderson of 1830. Only two, however, are tallied as the right age to be a potential match — James Anderson of Jones County, Georgia, and J. B. Anderson of Rowan County, North Carolina.

The next step would be to compare other family members to see if there appear to be synchronicities. And once you’ve narrowed it down to the final group of viable prospects, you use other documents to eliminate the non-contenders.

Get your copy of the Early Federal Census Worksheet

Hopefully, you already recognize the value of the EFCW and want your own copy. It is available from the Golden Channel Publishing online store in one of two formats:

Early Federal Census Worksheet (Premium)
This version works with the desktop edition of Microsoft Excel on either a PC or a Mac. It automates the format of each decade’s census record with a simple keystroke combination: Alt+g on the PC or Option+Command+g on the Mac. It turns the year headings vertical to make a more concise view. Premium only costs $5 more than Lite, and is worth it if you have Microsoft’s desktop version of Excel.

When you purchase the Premium edition, you will also be given a copy of the Lite edition, should you need it. Google Sheets is able to display the Premium edition’s vertical year headings, but cannot interpret the Alt+g macro. Google Sheets users might prefer the Premium edition because it is narrower across. You would simply need to copy and paste the census templates over, as you do in the Lite version.

Early Federal Census Worksheet (Lite)
The EFCW Lite edition works on Microsoft’s Excel Online spreadsheet. It also works on most other spreadsheet applications that can read an Excel spreadsheet. It cannot have its year labels turned vertically, so the spreadsheet is much wider. And it does not use macros. So you will copy and paste your census templates from the Templates tab. They will not be automatically brought in with a keystroke combination as the Premium edition does.

Getting started with the Early Federal Census Worksheet

When you download your spreadsheet, store a copy in a protected place. You can make as many copies as you want for your own personal use.

If you see a message indicating that security or macros need to be enabled, take that option. If you have the Premium edition, you will be asked to key in your last name. This registers yourself as the owner of this spreadsheet’s content.

Adding census data to your EFCW sheet

To begin to populate your macro-enabled spreadsheet, get into the first open cell under the Year column. Type in the census year you want to process — any decade from 1790 to 1860. Tab over or click in the Head of House cell. Press <Alt+g> (<Option+Command+g> for the Mac) to pull the census structure to the line you are on.

If your software can handle Excel macros, it will pull the 1840 census structure for you, looking like this:

If you chose the Lite edition, you can easily copy the census structure from the Templates tab.

Using Excel’s standard tools to enhance the work

Several of Excel’s standard features can make your work much more meaningful. I have found the comments, row hiding, and highlighting features particularly helpful.

The Comments feature allows you to attach text to any cell that will help in your analysis. Take, for example, the 1840 census line for James B. Anderson. If you have the 1850 census for this ancestor, you have names you can attach to the tallies in 1840.

Let’s say that the 1850 census tells you that James B. Anderson has a son named Frederick, born in 1822. You can right-click in the cell beneath the 1820 birth year, which contains a yellow “1” representing James’s oldest son in the household in 1840. Choose to Insert Comment, then type anything you think will be helpful to you in analyzing the spreadsheet.

If you are analyzing a large number of people in a spreadsheet, you can choose to hide rows that are either in the way or have already been eliminated as possible matches. You select the rows you want to hide then right-click and choose, “Hide.”

If you want to flag particular potential ancestors in some color-coded way, you can select the line and change its color with highlighting. (The Mac version of Excel might have a slightly different method.)

Summing up

Once you play around with it a bit, you’ll see just how easy this is. It will allow you to analyze similarities and differences with little effort. You can keep track of what you are learning and questioning. The spreadsheet becomes a robust record of how you drew particular conclusions about your family. You may put the materials away for a long while, as we often have to do in our genealogical work. When you can come back, you can quickly restore your memory of what you drew from the censuses.

I hope you’ll find this as beneficial as I have in my work. Do let me know if you have problems or if you discover better ways to use the tool.

*Disclaimer
I can't promise this tool will work on every system, with every combination of software. I offer it with a confidence that it will help most of you. If it is not working on your system, I will want to refund your money--and to hear what happened, so I can learn and improve things. Email me at donnacoxbaker@gegbound.com if you  have any problems.

---