Tag Archive | DNA spreadsheet

Using Spreadsheets

Dec8

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.

For DNA tracking purposes, the only other important function to understand is formatting cells so that the numbers don’t surprise you by turning into dates or fractions when you do not want them to. Click here for a recent article claiming that 20% of scientific papers on genes contain gene name conversion errors because of this type of reformatting!

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.

Suppose you want to keep your match list in a spreadsheet. There are many articles on this blog that explain how to do that. Use the tag DNA spreadsheets to find them by clicking here – http://blog.kittycooper.com/tag/dna-spreadsheet/
Continue reading →

Taking it to the Next Level – DNA Spreadsheets

Sep24

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, MyHeritage. 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 MasterSpreadsheet

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.

tier1smll 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 GEDmatch Matching 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.

Here is the step by step of what I did for my perfect cousin J.M. whose AncestryDNA results I blogged about in my previous post.

Continue reading →

Using your DNA test results: the Basics for Genealogists

Mar18

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.

KittyTopMatchesMap

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.
  • Secondly, make sure you know how to use a spreadsheet: sorting, deleting rows, inserting columns; you only need to know the easy stuff. [UPDATE: click here for my post with a youtube video teaching that easy spreadsheet stuff]
  • Third, check your understanding of how DNA works. Perhaps read my basics page – http://blog.kittycooper.com/2013/04/the-basics-at-23andme/ – and follow up with whichever lessons, books or videos appeal to you among those that I suggest or that you find listed in the ISOGG wiki – http://www.isogg.org/wiki/Autosomal_DNA
    Also lots of beginner questions are answered in the FAQ I keep for the DNA-NEWBIES mailing list on yahoo; a copy is on this blog – http://blog.kittycooper.com/dna-testing/newbie-faq/ – so check there when something is confusing.
  • 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.

Continue reading →

Organizing your Autosomal DNA Information with a Spreadsheet

Jan17

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

jvb-in-coat-and-tie-cropUsing 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:

  1. 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!
  2. 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.
  3. 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.

Continue reading →

Finding Distant Relatives with Autosomal DNA Testing

Jan14

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.

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

  1. Have an easily readable pedigree chart in both PDF and online format (GEDmatch can do the latter, more on GEDmatch later)
  2. Another good tool is a list of about 12 generations of ancestors by place name. Much easier for a possible relative to scan.
  3. Do some reading on the basics. UPDATE 9-sep-2018 See my article – http://blog.kittycooper.com/dna-basics/
  4. Try to talk some close relatives into testing so you have more data to work with
  5. 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.

Continue reading →