By Jim Bartlett
Impressed by Jim Bartlett’s prose on various message boards and mailing lists, I asked him to do a guest blog post on using spreadsheets with autosomal DNA results, here it is – Kitty
Using autosomal DNA testing can be a challenge – but it doesn’t have to be. It can be intimidating – but by taking it a step at a time, you can break it down into bite-sized pieces that are much easier. When you decide to use autosomal DNA (atDNA), and to get the most out of it, I recommend three broad areas of focus right from the start:
- Learn all you can about DNA testing for genealogy and particularly about autosomal DNA (atDNA). The ISOGG wiki is a good place to find good articles, tools, blogs (to keep you up to date), etc. Join email lists and read and ask questions. This is definitely a “continuing education” hobby. We are on a frontier with genetic genealogy – and we are pushing the boundaries every day!
- Create as robust a Tree as you can – stretch as much as you can to 12 generations, or more. This is the net you need to catch cousins and find your Common Ancestors. This is very important – if you don’t have the ancestors in your Tree, you cannot expect to find a Common Ancestor with a Match.
- Set up a process for your autosomal DNA project. To determine Common Ancestors you have to share ancestry info with your DNA Matches – you’ll be sending (and receiving) a lot of emails and messages. You’ll want to keep track of what you do; to find info on your Matches; to remember the Common Ancestors you determine; new names, new emails, new links to Trees, etc., etc. You may want to use a spiral notebook as a Diary or Journal of your notes. Some people keep a notecard for each Match, or a folder. I now have over 3,000 matches at FTDNA and 23andMe, so I need something that can handle that many (and more) Matches. Many of us use a spreadsheet – read more to see how to set up one.
So far I am finding that the common ancestors with Dad’s DNA matches at both 23andme and FamilyTreeDNA are much further back than predicted. We have found the MRCA for only those distant cousins with good paper trails and perhaps even a tree at GENI like we have.
Most of these matches are only one or two segments and the longer the segment the more likely it is to be a real match with a discoverable common ancestor. I actually found a new 5th cousin of mine through DNA, Dad’s 4th cousin once removed. She has a one segment match of 17.14 CMs and 2849 SNPs in common with Dad and our common ancestors are in the 1700s at farm Fatland in Etne, Hordaland, Norway (online resources for Etne research listed at familysearch.org)
23andme shows you all your 7cm and larger matches but many genetic genealogists think anything less than 10cm is suspect. My view is that if Dad’s match is also a match with either me or my brother (n.b. frequently the match is for fewer SNPs and CMs in the next generation) then it is real, even at 6cm. As you can see in the chart, we have found many common ancestors with smaller than 10cm matches. GEDmatch lets you look at even smaller segment matches with specific people as does Family Tree DNA.
Here is a summary of the most recent common ancestors in Norway that I found for Dad with some of his DNA matches:
It is very interesting to look at the overlapping DNA segments of one’s matches in order to figure out where they may have come from and how they might be related. It also helps with tracking these relationships and comparing the results from different sources. Personally I know when someone matches my 2nd cousin Dick that the relationship is on dad’s father’s line. My next task will be to start diagramming the possible relationships based on who matches whom among all our new distant cousins.*
To find the DNA segments where you match another person at 23andme that you are sharing with you put your cursor on “My Results” in the very top menu bar and then click on “Ancestry Tools” at the bottom of the second column of selections to get the page that lists Family Inheritence: Advanced. Within this function you select the person to compare to on the left and all those to do it with on the right. So typically I take a new person and compare them to me, my brother and my Dad; then various cousins. You need to select the table version to get the numbers shown in the spreadsheet below.
Here is how I track overlapping segments. I make one spreadsheet for each person I am looking at, sorted by chromosome, segment start, segment end and length. I use the same columns as the 23andme table view of my genome shares but add one column at the beginning marked with a P or M (paternal or maternal side match) whenever I know it. Then I add a column for the most recent common ancestor (MRCA), one for known relationship (when found), and another one for notes where I put comments like “same match with Kitty, no match to Dick, matches Jane elsewhere).