In recent discussions with a few of my genetic genealogy students, I discovered that many need some help with understanding how to use spreadsheets. So I went looking and found a series of excellent youtube videos that even taught me a few things. Here is the first one in the series.
He uses OpenOffice Calc which is free and happens to be the spreadsheet that I use.
The basic idea of a spreadsheet is to make a list of things that you want to keep track of, with the information about each of them listed next to them in columns. As you use it, you may decide to insert more columns, the things you are tracking for each, or more rows, the items you are interested in. You can also delete any of these and best of all, sort them.
Personally I reformat the start and stop points to have commas so I can read the numbers more easily and make the centimorgans column (genetic distance) default to two decimal places so that they line up well. I also change the font to Arial.
Perhaps this post needs the subtitle , “My Perfect Cousin Goes to GEDmatch.”
Most of us can keep track of information in spreadsheets. So how to do that with DNA? Well, the idea is to keep a list of matching DNA segments so that a new match can be compared to your known family members. That way you may be able to see where they fit in.
If you have tested at 23andme or Family Tree DNA, you can download your list of matches with their matching DNA segments either directly from your testing company or by using the tools at DNAgedcom. However AncestryDNA does not provide a list of matching segments.
Extract from my Dad’s Master DNA Segment Spreadsheet (click for a larger version)
Why would you want those? The short answer is to figure out which line a new DNA cousin belongs to. For the long answer, read on. For more posts about DNA spreadsheets click here or in the tag cloud, lower right hand column.
AncestryDNA testers can make a DNA segment spreadsheet by using any of a number of utilities at the GEDmatch web site. Start by uploading your raw DNA data (click here for that “how to” post). Your results will usually be ready for full comparisons the next day. Then buy the tier 1 utilities for at least one month ($10).
My preference for making a first spreadsheet is to use the Tier 1 GEDmatchMatching Segment Search. Then I go through the top matches from the ‘One-to-many’ matches report with that spreadsheet as a reference. I add notes on what I discover to my new spreadsheet.
How do you use autosomal DNA testing to enhance your genealogical research without having to take a PhD level course? This is a question several of my cousins have asked me, so here is my attempt to answer.
Chromosome Map of Kitty’s Closest Matches using her segment mapper tool
First of all, get as many relatives to test as you can. The more data you have, the easier it is to make useful comparisons and sort new DNA relatives into their related family lines. The closer family members shown in my chromosome map above are all cousins I convinced to test.
Fourth, bookmark a page with an explanation of all the acronyms – this is a good one: http://www.isogg.org/wiki/Abbreviations – a key term is cM. You do not need to understand the definition of a centimorgan (cM), hardly anyone does; just accept that it is the best measure of the importance of a DNA match, the larger the better.
Now to the practical application of all this, using shared DNA segment data to find relatives, preferably those 3rd and 4th cousins that your family no longer knows of. Click on success stories on this blog to read about some of the cousins I have found with DNA.
When people are shown as matches to you or your relatives, the testing company will make a guess as to how closely related they are. Beyond 3rd cousins, it is not possible for them to make an accurate estimate because of the random nature of DNA inheritance.
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.
Maybe you tested your DNA to prove or disprove a genealogical theory. Or maybe you tested to check on your health risks or carrier status. But now you see all these possible 3rd to 5th cousins in your family finder or relative finder lists and you wonder if you are related and if you can find that relationship. Perhaps you contacted a few and had almost no responses.
Yes you are probably related, but without both of you having a good paper trail you would be most lucky to actually find that relationship. It is likely to be further back than suggested if your ancestors were at all endogamous. Just living in the same location for a few hundred years can lead to much inadvertent intermarrying and more common DNA than degree of relationship would expect. So autosomal DNA testing is no genealogical shortcut. Some of the people you contacted already know this, so if they saw no common surnames or places on your profile they may have lost interest.
So be prepared before contacting those likely 3rd to 5th cousins.
Have an easily readable pedigree chart in both PDF and online format (GEDmatch can do the latter, more on GEDmatch later)
Another good tool is a list of about 12 generations of ancestors by place name. Much easier for a possible relative to scan.
Try to talk some close relatives into testing so you have more data to work with
Last but not least, make a decision on how much time you want to devote to this project … warning it can be addictive
If you want to do the minimum, then scan the localities and surnames of these possible cousins and contact the ones with surnames or place names in common with you. Indicate in your message that this is the reason for contact. Include the URL for your pedigree or family site and offer to send the PDF files. Include your email address if you are using the 23andme messaging system. The more directed the contact, the more likely you will hear back. I recently went through and cancelled some of my early invitations and send new improved ones on the lines above and got four new shares.