Devon Noel Lee
Using Spreadsheets for Genealogy Research
No matter the program you use, family historians benefit from adding spreadsheets to their research toolbox. Whether you're using Google Sheets, OpenOffice Calc, Microsoft Excel, Apple Numbers, or other programs, leverage the power of digital tables to manipulate your family history data.
What Data Can Be Tracked?
A spreadsheet is a collection of tables where information is arranged in columns and rows. If you can create a table, you can track that information in a digital spreadsheet program.
The following list is a sampling of ways I have organized my research.
Census Record Research
Tax Record Research
Land Record Research
Column recommendations include Calculated Age, Event Date, Event Type, Description. Color code your events, so they stand out. I used green for vital events and blue for life events for relatives.
In this table, I wanted to emphasize life after this ancestor's parents' died, so I used a different color. Your table can also include historical events.
For those who want an extra way to track your sources beyond a software program and a research plan, a research log of records and results may come in handy. Bare minimum, date of search, location, description of the source, comments about the source.
The one you see here contains more columns than perhaps necessary, but it made sorting and analyzing my progress easier.
Whether you have a basic record checklist, or my brick wall busting guide, you can use a template to track records for an individual. You can then convert this checklist into a research progress report, as some of my colleagues do.
Citation Master List:
Cari Taplin maintains a master list of source citations. If you're not using citation forms in RootsMagic or Family Tree Maker, this idea might work for you.
As explained in this post, How to Data Mine the Deceased. I captured all of the enumerated individuals in Lycoming, Pennsylvania, in 1850. Then I could cluster family groups and discovers answers to questions I never thought I would ask.
Early US Census Record Tracking:
In this video for FHF Xtra channel members, I walked through the difficult process of researching early US Census records.
>>> GRAB your FREE US Early Census Tracker as shown above. <<<<
I used the spreadsheet above to track families that use ticks and tallies beside the head of household's name.
Some websites provide data that you can leverage a spreadsheet to analyze. Particularly the St. Keverne, Cornwall, England parish registers are available in a downloadable table. You can then filter and sort the index to the church records to find your family.
Tax Record Research:
When researching in an area that taxed its citizens, you can extract information and compare data across several years for multiple individuals. Create column headings the reflect types of taxes paid in a specific location.
Land Record Research:
Since few land records have searchable indexes, you can use tables for property research to track what to search and the research results.
For this example, I can record the book and page names and the parties listed in the index. Then, I can detail the property description, value, and other relevant information for my research.
Tracking Genealogy Proof:
A fellow researcher had a great table that tracks her evidence. She sorts notes about her documenting into four generations, the event to prove, and the supporting document below.
While I like her example, I prefer a simple arrangement of relevant facts in this fashion.
Spreadsheets Help Process DNA Matches
Genetic genealogy involves processing many data, and tables can help a researcher sort, filter, and record notes about DNA matches.
I created a surname table using Google Sheets. This table is a quick, compact reference guide for your genetic surname to the 4th generation. Patronymics and adoptions complicate the creation of this cheat sheet, but it can be done.
If you need to create a quick tree, Andy created this video to show you how to visualize relationships quickly with a family tree made using Google Sheets. It's not designed to create a full family tree. It's simply a reference tool to visualize relationships on the fly.
Once you have your DNA match, you can create a Leeds Method chart to isolate how your DNA matches align with your four grandparents.
You can create a shared matches table, similar to what Margaret O'Brien does.
With the help of the AutoCluster Tool from MyHeritage, you can use a spreadsheet to view how your clusters relate. Then add additional details that assist in your investigation.
Advanced genetic genealogy techniques, such as visual phasing, require Google Sheets, Apple Numbers, or Excel.
Wath the Spreadsheets for Genealogy Webinar
You can now watch this webinar Genealogy Spreadsheets Tips and Tricks.
In this video, you'll follow Andy as he covers the basics of creating a Google Sheets file. You'll learn:
Different ways to add data to a table
How to sort and filter data
How to color-code columns and cells
How to quickly copy data from one cell to another
Videos Mentioned during the webinar:
US Early Census Record Training (Member's Only Webinar)
Other Videos about Genealogy Spreadsheets: