{"id":4280,"date":"2022-12-23T13:28:20","date_gmt":"2022-12-23T13:28:20","guid":{"rendered":"https:\/\/www.genealogyexplained.com\/?page_id=4280"},"modified":"2024-04-02T19:31:57","modified_gmt":"2024-04-02T19:31:57","slug":"dna-match-spreadsheet","status":"publish","type":"page","link":"https:\/\/www.genealogyexplained.com\/dna-match-spreadsheet\/","title":{"rendered":"DNA Match Spreadsheet"},"content":{"rendered":"\n
\"DNA<\/a><\/figure>\n\n\n\n

Keeping track of your DNA matches can be a chore. With new matches popping up all the time, it\u2019s hard to remember who you\u2019ve already seen and what relationships you’ve already researched. This is further complicated when you\u2019re trying to manage multiple kits across multiple testing companies. This article will teach you how to make a spreadsheet for your DNA matches, and provide a template to get started.<\/p>\n\n\n\n

In a hurry? You can access my spreadsheet template here<\/a>.<\/strong><\/p>\n\n\n\n

To learn more about DNA testing and genetic genealogy, read this guide<\/a>.<\/p>\n\n\n\n

Before we dive into the fine details, let\u2019s talk about some reasons why you would want to track your matches.<\/p>\n\n\n\n

Pro tip:<\/strong> a great way to get your feet wet in this hobby is with a free trial at one of the big genealogy sites. MyHeritage has a free 14-day trial<\/a> which gets you instant access to 19.4 billion records and over 90 million family trees. Click here<\/a> to learn more about this offer.<\/div>\n\n\n\n

Stay organized<\/h2>\n\n\n\n

Because I manage multiple kits across multiple company databases, I need a system to keep track of my activities. But I don\u2019t just add my matches to a spreadsheet to have a nice clean list of people. I track which matches I\u2019ve determined the actual relationship for and which I have not. That way, when I have some free time and want to sink my teeth into a new DNA mystery, I open my list and filter by unidentified matches. <\/p>\n\n\n\n

I\u2019m also a big fan of \u201cpainting\u201d my matches on a Friday night. By that, I mean I\u2019m a big fan of DNA Painter\u2019s chromosome mapping tool<\/a>. I like to think of this tool as a single chromosome browser<\/a> for all my matches. Keeping track of who I\u2019ve added to this tool allows me to see who I still need to add. Unlike some other spreadsheet templates, I don\u2019t like to store chromosome data or start\/stop positions in my spreadsheet. DNA Painter is a far better tool for this, so there\u2019s no need for redundancy. I like to track who I\u2019ve already painted – even if I haven\u2019t determined the common ancestor(s). <\/p>\n\n\n\n

Some other essential things I like to track are contact information, strong shared matches, and notes. The notes field I leave open-ended, which allows me to make a note of any other pertinent information not covered by the other fields. This is where I can record things like a Gedmatch kit ID, links to research logs in google docs, or links to trees I created on Lucidchart. <\/p>\n\n\n\n

I also use my spreadsheet to quickly group my match list across all databases by things like MRCA (most recent common ancestors) and actual relationships. Grouping matches like this is one reason I use AirTable<\/a> instead of Microsoft Excel or Google Docs (more on that later).<\/p>\n\n\n\n

Log it before you lose it.<\/h2>\n\n\n\n

Another fundamental reason to track your DNA matches with a spreadsheet is that not everyone keeps their profiles active forever. When news broke of police using DNA databases to hunt down the Golden State Killer, many people removed their profiles due to privacy concerns. I lost a lot of valuable match information when that happened, especially on Gedmatch. In particular, one match was a heartbreaking loss because it was critical to the brick wall case I was working on. That loss is what prompted me to start keeping track of my matches.<\/p>\n\n\n\n

Companies like 23andMe, MyHeritage, and FamilyTreeDNA allow you to view chromosome detail for your matches. If the match removes their profile or makes it private before you log that information, you\u2019ve lost it. This is especially true on 23andMe. Many people opt for private profiles since their health information is linked to the same account.<\/p>\n\n\n\n

Using Airtable<\/h2>\n\n\n\n

My spreadsheet application of choice is Airtable<\/a>. If you haven\u2019t heard of this app before, it\u2019s a combination of Microsoft Excel and Microsoft Access. You\u2019re getting the same easy-to-use spreadsheet interface, but with relational database capabilities. This allows you to manipulate your data any which way. I love Airtable. <\/p>\n\n\n\n

Using the spreadsheet<\/h2>\n\n\n\n

Let\u2019s take a look at my DNA match spreadsheet template and how to use it. Feel free to customize to your specific needs.<\/p>\n\n\n

\n
\"\"
Matches anonymized for privacy<\/figcaption><\/figure><\/div>\n\n\n

The first column is the username of the match. I track by username instead of the real name to easily find them later in whichever company database they\u2019re in. <\/p>\n\n\n\n

Speaking of company database, the second column is just that. Not only is this a good thing to track so you can easily find the match later, but some people will be in multiple databases, and you\u2019ll want to track that. For example, suppose you have a match on Ancestry. In that case, you can\u2019t paint that match on DNAPainter because Ancestry doesn\u2019t show chromosome data. But if that match then joins a site that offers chromosome data, we need to record that and immediately log the data before it\u2019s lost. Currently, there are options for Ancestry.com, MyHeritage, Family Tree DNA, 23andMe, LivingDNA, and Gedmatch.<\/p>\n\n\n\n

The next three columns have to do with the amount of shared DNA<\/a> and segments. I look at:<\/p>\n\n\n\n