Making a spreadsheet of autosomal 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).

Here is a sample from my spreadsheet entries on chromosome one for my Dad, with the names blacked out. I typically only add people I have shared genomes with at 23andme or have had contact with through other sites. The template for this spreadsheet is in my Downloads area. Since this post was written I have started using a second spreadsheet to manage the details of my correspondance with matches, that template is also in the Downloads area.

 

 

Another spreadsheet that is very useful is the master sheet of all your matches that you can download from 23andme‘s Countries of Ancestry (COA). To do that go to ancestry labs and click on  “My Results” in the very top menu bar and then “Countries of Ancestry”.Wait for the page to load and then scroll down until you see a big button that says:

Download yourname‘s Ancestry Finder Matches (CSV)

Click on that to download the file. Open that file in excel and sort it by chromosome and segment start in order to look at the overlaps.

I like to look at this sheet to check on “pile ups” – spots where there are many matches. However you need to be sharing to compare people you match to each other. For example, one distant cousin overlaps with Dick on the master chart but when I compare him and Dick to each other there is no match there. Thus I know that person’s segment is from the other side, maternal not paternal. Every chromosome is paired and so every segment is really there twice. This even if the numbers match up you need to compare them to each other to be sure there is a match.

Then of course there is the fun of looking at each other’s pedigrees for the paper trail to a common ancestor. It is a lot easier when they are on Geni.com as well. Sometimes the relationship is quite far back. Here is a list of the MRCAs I have found so far with some of  our new distant cousins:

  • Gunnar Olafsen Gangså 1570-1639
  • Ingeborg Djupesland (Bårdsdatter)  b 1650
  • Knut Pedersen Åmot 1786-1851 (a fourth cousin)
  • Ola Narvesen Glaim 1621-1714
  • Bjorn Ve (1725-1792)  (a fifth cousin)

Part of the problem for us is that when you are born of the youngest son of the youngest son in these large Norwegian families your generations get longer than every one else’s so often we are once or twice removed in generations from our new cousins of our own age.

*Since this post was written I have created several tools to make a picture of segments. One to show what DNA you have inherited from which ancestor, see the ancestor chromosome mapping tool page. Another tool to look at multiple shared segments from many possible relatives, the overlapping segment mapper. Additionally a tool for a detailed view of a single chromosome. the one chromosome mapper.

print

25 thoughts on “Making a spreadsheet of autosomal DNA matches

Click here to add your thoughts at the end of the comments
  1. Since I wrote this post I have added two more columns at the beginning of the sheet, between side and the person’s name: one for the GEDmatch kit number and one for the email address.

    I have found that when there is a particularly interesting overlapping segment area that looking at it over at GEDmatch will often turn up some more matches with that information suitable for cutting and pasting into the master spreadsheet (just swap the email and name columns)

  2. I followed the directions and got this message. I am using a PC.
    Column heading Start is required.
    –> Column heading End is required.
    –> Column heading side is required.
    –> Column heading MRCA is required.
    –> Column heading centiMorgans (cM) is required.
    Your file is probably not a CSV file please fix it and try again
    It is easy to save your spreadsheet as a CSV from Google docs

  3. An Israeli researcher tells me he ignores Ashkenazi matches less the 23cM. I will retry working with my AJ matches on that basis

  4. Hi, Kitty. I’m having a very hard time understand this concept (from your article above) “For example, one distant cousin overlaps with Dick on the master chart but when I compare him and Dick to each other there is no match there. Thus I know that person’s segment is from the other side, maternal not paternal. Every chromosome is paired and so every segment is really there twice. This even if the numbers match up you need to compare them to each other to be sure there is a match.” Do you have any examples, visuals, etc. that can help me understand this? My questions start with !) How can they overlap on the master chart but when you compare them they do not match? 2) How does that make it a maternal match not a paternal match? 3) I understand the every chromosome is paired but you follow it with ‘so every segment is really there twice’ (I think I get that, it makes sense) but then you say even if the numbers match you need to compare them to each other to be sure there is a match… you lose me here! What are we supposed to compare? Examples and visuals would be so helpful to try to sort this out. Thanks!

  5. Dick is my second cousin on dad’s father’s side so if he and person A overlap with dad at the same place but not with each other then person A is matching the segment dad has from his mother since Dick is know to be matching the one from dad’s father.

    I mainly offline this week but I will look around for a graphic to,illustrate this later

  6. Hi Kitty I have a very nice spreadsheet I’ve been working on with like 600+ rows on it my question is the MRCA column im unsure of what exactly to put there? Do I put the names of the couple that is most recent ancestor? Thanks for any insight!

    • Danette – The MRCA (Most Recent Common Ancestor[s]) column is for the common ancestral couple when you find them. I do have cases where I am descended from a different wife or husband so only one MRCA. So leave it blank until you know the relationship

  7. Hi Kitty,
    I enjoy many articles and information I find periodically which you have written. Thank you. I have attempted to use your on-line dna mapping, but when I enter the csv (downloaded from FTDNA) the file with the name appears, after clicking the >choose filemake the chromosome map< button the name of the file disappears and nothing appears to have happened.

  8. Thanks
    Send me a copy of your file to kitty at this web site name without the blog and I will have a look

  9. Hi Kitty, I am newer than a newbie. I am trying very hard to follow chromosome mapping, but not with very much success. Do you know of a genetic genealogy group in Pittsburgh, PA that I can join to get hands-on, one-to-one technical assistance? Reading these articles is just not cutting it for me. HELP!

  10. Ann,
    The CSV files that work with my tool are lists of matches and the segments where they match you. Ancestry does not provide this information.

    To make such a spreadsheet you would need to upload your DNA resutls to GEDmatch
    http://blog.kittycooper.com/2015/09/please-upload-your-ancestry-raw-data-to-a-site-with-a-chromosome-browser/

    and then get the numbers like this:
    http://blog.kittycooper.com/2016/09/taking-it-to-the-next-level-dna-spreadsheets/

    • Thanks for the help but, I did all of that & the spreadsheet has everything under the ch1. Nothing in in other ch’s

      how to get the other ch’s 2-22 ?

      • Carl,
        You did exactly what? At Gedmatch there is a selector where you can get all chromosomes or only one, perhaps you had that set to chr 1?

  11. My mother was 42 years old when I was born and my father was 46-so I understand the pain of “Long Generations”-though I call it something less flattering!

Leave a Reply to Kitty Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.