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.

  • Timelines

  • Research Log

  • Record Checklists

  • Research Progress

  • Data Mining

  • Census Record Research

  • Tax Record Research

  • Land Record Research



Timelines:


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.



Research Logs:


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.


Record Checklists:


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.



Data Mining:


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. <<<<


Tracing the Reeds of Lycoming, Pennsylvania

I used the spreadsheet above to track families that use ticks and tallies beside the head of household's name.


Recordset Research:


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.


Some records document head and slave taxes.

Some records document real estate property taxes.

Another tax list documents real estate and personal property taxes.

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.


Land record research for Genesee County, Michigan

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.


Evidence to establish the birth of an ancestor.

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.



The numbers in the clusters were added manually.

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.


Phasing requires a spreadsheet.

DNA segment analysis using a spreadsheet.


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:


Save to Pinterest to remember all the options.


Other Videos about Genealogy Spreadsheets:


Note: To leave a comment, you will be asked to sign in with your Facebook or Google Account. This action will help reduce spam comments on our site. I hope you'll understand.

Follow
Connect

Have more questions, contact us.

© 2016-2022 by FHF Group LLC.

Read our disclosure statement.