Pre-1850 census drama: a free tool for your sanity

We love the censuses, don’t we?  We need them. Right back to 1850, they’re our bedrock. Earlier than that, however, we dread them. Fear them, maybe. Avoid them, to our peril. While limited in value individually, however, the pre-1850 censuses become gold when compared to each other. But comparing them is a hassle, right? Not any more. I have designed an Excel tool that makes pre-1850 tally-matching a game you actually want to play. Let the Pre-1850 Census Analysis Tool* restore your sanity.

The pre-1850 census — where only the heads of households had names

On too many occasions, I took family branches back to the 1850 census, then stopped. Rather than struggle with the pre-1850 censuses, in which most of the U.S. population was reduced to a nameless tally, I would pick up another family branch, for which the 1850-and-later censuses had riches to offer. I avoided the hard work and went for the low-hanging fruit.

But the pre-1850 censuses can bring you vital information and raise essential questions, if you have the patience and mental acuity to make sense of them — or 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, promising 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 last summer. J. Mark Lowe taught one of our sections. Mark is a very engaging teacher, if you have not had the privilege. He has devised a paper chart that lets you line up tallies for comparison of census data between 1800 and 1840 for the white male and female populations.

Click to expand

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 to take a bit of the drama out of pre-1850. You transfer the tallies to the appropriate boxes in the form, then see how each decade’s census information lines up.

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 high-hanging, difficult fruit was finally in reach.

An Excel tool for pre-1850 census ease

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. I call it the Pre-1850 Census Analysis Tool. (For those who don’t have the desktop version of Excel, I have a lite version of this tool, designed for use with Microsoft’s free Excel Online or other spreadsheets that can’t handle Excel macros.)

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 as more information is added.


If you were to scroll to the right, you’d see the spaces to enter white females, free black males and females, and male and female slaves. These sections can be collapsed or expanded, depending upon what you want to see.

If 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 earlier Hillyer records are fictional, designed for educational purposes.

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 is at least 26 years old, allowing us to narrow down his age.

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 — 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. Spreading out the view to show matches in the white women and any freed-people or slaves would also strengthen or weaken 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 ask about marriages, deaths, and widowed parents or orphaned relatives moving in. There is much to learn from changes in the tallies.

Though 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. 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 the same man as John Hillyer of 1840 New York, because of the way the family tallies line up.

Narrowing down possibilities in pre-1850 censuses

One of the best uses of the Pre-1850 Census Analysis Tool 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 right man, Jim Anderson, in 1830.

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.

Click to expand.

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.

Getting the Pre-1850 Census Analysis Tool

I am offering this tool to everyone who signs up for the Golden Egg Genealogist newsletter. I know, you hate it when we bloggers ask for your email address, but a mailing list is the lifeblood of blogging. And it is a very small thing to ask, if you are finding value in this work.

Here are my promises to you:

  • I will not share your email address with anyone outside my organization.
  • I will not overwhelm your mailbox with email.
  • You can opt-out at any time you please.

Those who are already on the list should have already received a newsletter with a link to the Pre-1850 Census Analysis Tool. If you did not receive it, notify me at

Opening the Pre-1850 Census Analysis Tool

When you receive the link to the  Pre-1850 Census Analysis Tool, choose either the regular version for the desktop edition of Excel or the lite version, if you plan to use Microsoft’s free Excel Online or a spreadsheet software that cannot use Excel macros. Click on your preference, and you’ll be taken to a screen that looks a bit odd, for reasons beyond my control. It looks like the spreadsheet is open on your screen, but it is actually just a picture of the spreadsheet.

Click to expand.

If you’re using a PC, use the “Open” button in the top-right corner of the window. You can use the down-arrow just beside it to open the spreadsheet either in Excel or in Excel Online. If you’re using an Apple product, I’m told the button you’ll use is a “Download” button, rather than “Open.”

Click to expand.

If you see a message indicating that security or macros need to be enabled, take that option. For those who have a macro-enabled spreadsheet, you will be able to use a keystroke combination to rapidly build the structure for your census analysis. Those without macro capabilities can still make great use of the tool —  just having a couple of extra steps.

Using the Pre-1850 Census Analysis Tool

I recommend you go ahead and save a copy of the spreadsheet to your hard drive that you’ll keep as your empty template. Then copy it, giving it the name of the ancestor you are working on, filing it in a place meaningful to your recordkeeping. Make a new copy for every person you are researching with the tool.

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 1840. Tab over or click in the Head of House cell. Press <Alt+g> (<Option+g> for the Mac) to pull the census structure to the line you are on.

Click to enlarge.

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

Click to enlarge.

If you are unable to use the macro or keystroke combination, 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.

Click to expand.

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 G4 cell in Excel — the tally 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 Apple versions 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, and to keep track of what you are learning and questioning. The spreadsheet becomes a robust record of how you drew particular conclusions about your family. Even if you put the materials away for a long while, as we often have to do in our genealogical work, you can come back and 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.

Sign up below and enjoy!


Subscribe to the Golden Egg Genealogist

and receive a link to the Pre-1850 Census Analysis Tool

* indicates required

I can't promise this tool will work on every system, with every combination of software. I offer it "as is," with a confidence that it will help most of you. If it is not working, I would like to hear about it, in hopes that I can improve it. But there are no guarantees. You load it on your (presumably virus-protected) system at your own risk. 

GEG_Single Wrap

Related posts:

Share this in your circles...

16 thoughts on “Pre-1850 census drama: a free tool for your sanity”

  1. I haven’t received the link. Wonderful though. I will have to go back and re-verify my findings, from 1790 to 1850. thank you.

    1. Hi, Helen. I found your email address on the mailing list and sent you copies of both spreadsheets. Let me know if you get them. Thanks!

      1. Oh, Suzy, I just checked the email list and your name hasn’t been added yet. I’m guessing that it’s waiting for you to respond to a confirmation email. If you did respond, or haven’t received the confirmation email, email your information to me at, and I’ll add you to the list manually and send the spreadsheets. Thanks!

  2. I must have done something wrong when I tried to download Pre-1850 census excel sheet. I got the other one. Anyway, is there a way I can get the link again. Thanks

  3. I’m so sorry, Mary. Several have encountered troubles. I’m about to send you the spreadsheets by email. Let me know if you get them. Hope you enjoy! –Donna

  4. Thanks so much. This is a great spreadsheet. However it will not allow me to insert comments to the cells. I’m assuming that is because the worksheet is protected? If I try to unprotect it, it requires a password.

    1. Thanks for letting me know, Denise. You are the second to report it, and I have at last found the problem. I will be replacing the spreadsheets online in a few minutes. I’ll resend the links to everyone on the mailing list. Let me know if you continue to have trouble.

      1. I just downloaded both the Narrowing Dates Calculator and the Pre-1850 Census Analysis. First of all, thank you very much for making your hard work available. However, the census Excel sheets that I downloaded are password protected. I’m also wondering if I’m doing something wrong with the Narrowing Dates Calculator because when I enter the dates in for the 1880 census, I’m getting strange dates and with other dates I’m getting #VALUE! in the boxes.

        1. Karin, I am so sorry to just be seeing this. I don’t know how I missed it. I’m sorry you’re having trouble with it. Can you please email me the spreadsheet with the #VALUE problem, so I can see what might be causing it? Send it to The password protection should only be blocking the cells you should not type in, but I can look at that, also. Thanks — and apologies!

  5. For some reason I can’t seem to be able to download the Pre-1850 Census Analysis Tool or the Pre-1850 Census Analysis Tool Llite. or the Pre-1850 census drama: a free tool for your sanity.
    Could it be because my husband has Windows 7 on his computer and I have Wingows 10 on my laptop? Do I need to change to my laptop?

  6. Donna; I downloaded the Excel spreadsheet and it will be a huge help for my research. I am also finding that the Pre-1850 Census Analysis Tool is password protected but it’s only the template tab that is locked, all cells are un-editable. If this was intentional, it’s understood and I can build it out myself from the template provided. The Census worksheet is completely accessible and I can start from there, no problem. Thanks again.

    1. I’m so glad you are finding a value in it, David. And you can take off the password protection with “GEG.” I just protect it to keep people from accidentally deleting cells they need. Thanks!

Leave a Reply to DonnaCoxBaker Cancel reply

Your email address will not be published. Required fields are marked *