Using Spreadsheets for Genealogy Research


grayed genealogy spreadsheet with title spreadsheets for genealogy

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


Genealogy spreadsheet timeline

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.


Genealogy research log example

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.

Free Genealogy Research Check List

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.


Spreadsheet for genealogy featuring 1850 Census information data mined from Ancestry

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.

Free Early US Census Record Tracker Google Sheets Template

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


Early US Census Record Tracker with sample data
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.

Genealogy Spreadsheet featuring parish register information for Cornwall, England

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.


Genealogy Spreadsheet for US Tax Record Research about slave owners.
Some records document head and slave taxes.

Spreadsheets for tracking property taxes of your ancestors
Some records document real estate property taxes.

Genealogy Spreadsheet for Texas Tax Records
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.


Genealogy Spreadsheet example for tracking land record research
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.


Table for Genealogy Proof Statement ICAPGEN
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.


Genealogy Surname Table

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.

Video for Quickly Create a Small Family Tree Using Google Sheets

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.


Leeds Method Charting Using MyHeritage DNA

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.



MyHeritage Auto Cluster Enhanced with a spreadsheet for genealogy research
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.


DNA Segment Phasing Spreadsheet
Phasing requires a spreadsheet.

DNA Segment analysis using a spreadsheet
DNA segment analysis using a spreadsheet.


Wath the Spreadsheets for Genealogy Webinar



Spreadsheets and genealogy YouTube Thumbnail

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:


Spreadsheets for genealogy research text overlaid on research timeline
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.