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.

Census Comparison Worksheet

It has been some time since I posted about the Charles and Caroline Carson family of New Jersey. I needed to remind myself where I left off with respect to the census information I had collected thus far. I needed a form to see my census data at a glance. With only a bit of searching on Pinterest, I found a promising census comparison worksheet, posted by Jenny Lanctot who writes the “Are My Roots Showing?” genealogy blog.

census comparison worksheet from Jenny Lanctot, found on Pinterest

Jenny has graciously made her census comparison worksheet available for download at this link. Thank you, Jenny. I like this form, in that there is room to record information from up to five different census enumerations for one couple and up to fourteen of their children. It is similar to the way I have previously laid out census extractions in a table in Microsoft Word, but with more columns. More columns equals more data points for correlation, which is a very good thing when you are writing up your research with accepted genealogical standards in mind.

I downloaded her form and began doing the data entry in short order. It did not take me long to realize that I actually wanted to see a bit more detail than the form allowed for, so I began tweaking it just a bit. In the column on the left where the couple’s marriage information is recorded, I added a row to record the marriage officiant by name and role. I added an additional row below Twp. (for Township) to note a smaller jurisdiction, abbreviated as P.O. (for Post Office), since that level of detail is included on some census enumerations. Finally, on the main tab, I also added a row for the street address when known, as this is important for tracking our urban ancestors.

completed census comparison worksheet for the Carson family, 1850-1900The main table in my census comparison worksheet allows me to visualize 50 years of census data at a glance for one nuclear family (in this case, Charles and Caroline Carson of New Jersey). I have hidden the ribbon (using CTRL+F1 in Excel 2010) and the rows near the bottom for more siblings to make the completed worksheet easier to see in this screenshot.

In my example, I chose to only include United States Federal census information, but you could just as easily create a table that includes state census enumerations or non-population schedules such as agriculture or manufacturing. I also chose to input ages, birthplaces and occupations to improve the ability to compare across census years.

The biggest change I made was to include additional tabs in my workbook, one for each census year extracted on the main page of the form. I renamed each tab to correspond with the year, then I attached an image of the actual census page that I had previously downloaded. Lastly, I included a carefully crafted source citation so that I can simply copy and paste it into other documents or blog posts when needed.

census comparison worksheet with census image and citationI added more tabs in my workbook to include an image of the census page and a source citation, one tab for every census year on the main table. This image from the 1870 Federal census shows Caroline Carson living with an unidentified male named Lewis Rainier in Mercer County, New Jersey. We have yet to learn what relationship the two shared.

This is a time-consuming process, and one that I am unlikely to do for every family that I am researching, as I normally would simply extract the relevant census information into a note linked to a census “event” in my RootsMagic database. But, for those families that present brick wall problems, or for those families that I am writing about, it is a useful endeavor. If you have a genealogical problem you have worked on for years without a resolution, then I would recommend compiling your data in a format that allows you to visualize and correlate information differently, such as a census worksheet, a timeline or mind map.

 

Updated 22 March 2018. Follow-up post has link to download the census workbook and instructions for its use.
http://ancestorroundup.com/census-comparison-worksheet-revisited/

It Was a 7: One 1940 Census Code Revealed

Post-enumeration coding of 1940 census data for marital status

Like many over-eager genealogists, I was online the morning of 02 April 2012 – the first day the 1940 census was available to the public. Within a matter of days, I had located a handful of relatives on the 1940 census by first locating their enumeration districts. It wasn’t long before I began finding information in this census that I hadn’t encountered previously despite having done extensive census research in the past. For example, why was I finding the numeral “7” in the marital status column, instead of one of the more common abbreviations of marital status, such as S[ingle], M[arried], W[idowe]d or D[ivorced]?

1940 census with marital status column 12 highlighted1940 U.S. Federal census entry for Oliver Bingaman of Shoshone County, Idaho, highlighted in yellow.

I’ve highlighted column 12 in the 1940 census in red where marital status was recorded. Note those who reported they were married (M) where the M was crossed out and a 7 penciled in. As we shall see later in this piece, instructions to enumerators were only partially followed for this household. In the case of the author’s grandfather, Oliver Bingaman, highlighted in yellow above, no obvious wife was enumerated with him, and yet there was no alteration of his marital status as originally recorded.

Fortunately, I was able to track down a blog entry by Meldon J. Wolfgang III, wherein he described this very scenario and some of the backstory behind this particular census code. Read his post here.

Mr. Wolfgang’s blog entry contains a link to a very valuable resource: the IPUMS-USA website. Wolfgang explains

“IPUMS is not a genealogy site; it is the acronym for the ‘Integrated Public Use Microdata Series’ and is part of the University of Minnesota’s Population Center. Data geeks, statisticians and epidemiologists – all of whom use census data – are the primary users of this site.”

Even though genealogists per se are not the intended audience, serious genealogists will nevertheless find a wealth of information about the decennial United States Federal census on this site.

IPUMS USA website | Census microdata for social and economic research
IPUMS-USA census data website – http://usa.ipums.org/usa/

To locate the procedural history that explains the post-enumeration coding done by the staff of the Census Bureau, navigate to this link: http://usa.ipums.org/usa/resources/voliii/enumproc1940.pdf

It is under the General Population Coding section that we find the explanation for the coding of marital status:

Source: Jenkins, Robert M. Procedural History of the 1940 Census of Population and Housing. (Madison : University of Wisconsin Press, 1983), p. 63

To summarize, if the enumerator left the marital status column blank, then the Census Bureau staff in Washington D.C. would attempt to complete the field based solely on other elements they read on the page. If no spouse or children were living in the household, the coder assumed the individual was single. If, however, the person had no spouse in the home and there were children present, then one of two codes was entered, either 1) widowed, if the individual was 55 or older, or 2) 7, if the person was under age 55. Even when a marital status of M[arried] was recorded, clerks were instructed to interline the M and replace it with 7 if no spouse was included in the household, thus resulting in this entry: M 7. This distinction with respect to age is important. In a nutshell, if you find a marital status of M crossed out and replaced with Wd, don’t assume the spouse is deceased; look for that individual in another household. If you find a 7 in the marital status column, or an M crossed out and a 7 penciled in, then look for the spouse in another household.

As Wolfgang pointed out in his post, the census coders had no special insight into any particular household. If something you’re seeing in the 1940 census doesn’t track with the research you’ve done on a family, or with your own personal knowledge of events, then having a better understanding of these codes may help explain any anomalies.

We have examined a bit of the history of census coding for a single column of data (marital status) in a single census year (1940). Imagine what additional information you may be able to glean from other entries if you read the relevant instructions or procedural history as well. The IPUMS-USA website is a great place to start.

***
Note: In his blog post, Meldon J. Wolfgang III referenced a talk he gave in the days leading up to the release of the 1940 census. Judy G. Russell, CG, (aka the Legal Genealogist) was in the audience and gave a brief synopsis of his talk, and an overview of the IPUMS-USA website, on her blog.

1940 Census Indexing Update

The Genealogy Insider blog by Diane Haddad on April 10th gave us a nice update on 1940 census indexing to date by various entities. I was intrigued by the beautiful map on the FamilySearch.org 1940 census page, showing percent completion of indexing efforts state by state. Place your cursor on any state to view how much of that state’s census has been indexed. Below, you’ll see that 92% of the 1940 census for Kansas has been indexed  by volunteers in the ten days since the census was released. Amazing effort!

Map of U.S. showing census indexing efforts to date

It is important to remember that this map represents indexing efforts of one group only. If you don’t find your state represented, check out some of the other links in Haddad’s blog to see what has been done by other groups. Join the volunteer indexing effort at https://the1940census.com/getting-started/.