Census Comparison Worksheet Revisited

Some time ago, I revised an Excel census comparison worksheet created by another genealogy blogger and provided an example of its use on this site. I have had enough requests for a blank copy of my census comparison workbook that I am making it available for download for those who would like their own copy.

Blank census comparison worksheet screenshot

 Download blank census comparison Excel workbook
[Revised 20 Jan 2020 to correct date formatting]

Technically what I have created is called a “workbook” rather than a “worksheet” since I added additional tabs to the original sample worksheet for census images and citations. I will use the terms worksheet and workbook interchangeably though to refer to the download.

The main form allows for the input of five different census enumerations for a single family and can be printed out on a standard 8.5 x11 inch sheet of paper if desired. In my 2016 example, I tracked the Charles and Caroline Carson family of New Jersey through the censuses of 1850, 1860, 1870, 1880 and 1900 and extracted that information onto my form. Note that much of the 1890 U.S. census is unavailable and therefore not included in my example. Doing this type of work forces one to analyze each individual census to pull out the relevant facts. Combining multiple census entries into a single form allows for correlation of the data and enables one to make determinations as to whether the correct family has been identified each and every census year.

I thought in addition to providing the Excel download that it may be useful for some to learn how I use the workbook. If you are in that camp, read on. I upgraded from Excel 2010 to Excel 2016 since the original post so the screenshots may look slightly different than before.

How to use the workbook

If a couple has more than nine children, additional rows can revealed. This form actually includes spaces for a total of 14 children. This couple had fewer children, so I simply hid the rows. To unhide, select rows 34 & 45, right-click and choose Unhide from the context menu that pops up.

unhide cells in census comparison worksheet
Unhide rows to reveal the spaces to track additional children

If a child was not yet born, or if someone died and would not have been included in the census for that year, then I shade the cell in the column so I know that they should not have been counted and I do not have to keep looking for them. Select cells under the appropriate column, right-click, and choose the paint bucket icon. Click the down arrow to the right of Fill color and select any color. I use a light gray.

shade cells of unborn or deceased personsShade cells of unborn children or deceased persons for easier tracking

If you want to add another sheet to the workbook to include an image for another enumeration, click the plus (+) sign near the bottom of the workbook.

Add a new sheet to the census comparison workbookAdd another sheet to the workbook

To rename your new sheet, right-click and select Rename. Type in a new name, ideally something short, but descriptive, like the census year.

Rename new worksheetRename worksheet to “1900”

To move your new worksheet to another location in the same workbook, hold down the left mouse button and drag it so it appears in chronological order.

Drag new 1900 sheet to the rightMove the new 1900 sheet to the right, after 1880

Change the entire sheet background to white to remove the grid lines for individual cells by going up to the area to the left of column A and above row 1 and clicking there. Your entire sheet should turn gray to indicate that it is selected. Then click the paint bucket and choose white for the sheet color. The columns and rows remain on the worksheet, they just are not visible.

Change worksheet background color to white

To add a census image, click on the worksheet corresponding to the year of the census. Click in cell B3, then choose Insert > Pictures from your toolbar and navigate to the folder you stored the image. If you just downloaded it, the image is probably in your Downloads folder.

Insert census image into worksheet
Insert census image at cell B3, just below the census citation

Change it up

This is your workbook, so feel free to edit it accordingly. In the above example, the main form was designed to show a single family across five census enumerations. If using that approach, you would need a separate workbook in the event of a second marriage, especially if there were additional children. If you want to focus on an individual and include all marriages and children from all of those marriages, make those changes.

Here is an example of another workbook I created to show one woman, her two husbands and children by both marriages.

Another census comparison example
This census comparison worksheet shows extracted information for Jane Amanda Carson and husbands Joseph Kuhn and George E. Hart in five Federal census enumerations (1880-1930).

Final thoughts

Remember that you can revise the workbook to include any census listings of your choice, but I would recommend only including those from 1850 and later where each person in the household is listed. Better forms are available to track censuses between 1790-1840 that only list the head of household, with everyone else represented by a tick mark in broad age groupings. An example by Bill Dollarhide is featured here.

Make sure that you download a copy of each census that your family appears in and attach it to the appropriate tab in the workbook. In genealogy, there is no guarantee that the record you find today will be available tomorrow, or that you will be able to locate it again. Unfortunately, websites do go offline, and sometimes genealogy records are later restricted, so download them as soon as you find them. Keeping everything together in one workbook will make your genealogy research easy to find and easy to review at a later date.

Have fun census tracking! Hopefully the effort will yield new insights in your genealogy reasearch.

9 thoughts on “Census Comparison Worksheet Revisited

  1. so if I understand correctly, I should download the image of the census and place it on a blank sheet on the appropriate year but the information of the census, I should transcribe unto the comparison sheet, year by year, Yes? thank you for this, it might save me some gray hairs

    • Hello, Addys
      That is indeed what I have suggested. Taking the time to input the data in a spreadsheet or form forces a person to slow down and spend some time with the record. Pasting a copy of the census into the same workbook (on another tab) allows you to go back and look at it at a later date if needed.

      Good luck!

  2. Thank you so much for providing this workbook! I am new at genealogy but quickly realized that I needed to have a document to help me see census data from multiple years at a glance – so glad I found your page!

What's on your mind?

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