Using Spreadsheets

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/

Extract from my Dad's Match List SPreadsheet

Extract from my Dad’s Match List Spreadsheet

Start with the match list of DNA segments from the company you tested at. If you have only tested at Ancestry, then upload your results to GEDmatch.com and read my article about how to make a master spreadsheet using the matching segment tool at GEDmatch. You may wish to add columns like MRCA (most recent common ancestor), relationship, and notes like I have done in my Dad’s shown above.

Both 23andme and Family Tree DNA have a way you can download all your matches with their matching segment data or more easily you can use the tools at DNAgedcom.com to do that.

Another spreadsheet you might want to keep would be one to keep track of your DNA correspondence, perhaps with columns for their name (keeping the surname separate so you can sort by it), where they tested, how much DNA they share, their email address, gedmatch number, and occasionally even a phone number.

There are sample spreadsheets for both of these in my downloads area. Also have a look at the pictures you can make from these spreadsheets using my tools.

KittyTopMatchesMap

Segment Mapper Tool generated Chromosome Map of Kitty’s Closest Matches

8 thoughts on “Using Spreadsheets

Click here to add your thoughts at the end of the comments
  1. If they do not have a spreadsheet program, I suggest using Google Sheets, and Google Docs. It has many advantages including being able to share with others.

  2. I try to keep almost everything I want to have handy in one spreadsheet. So I have columns for email; cousinship; Sent and Reply (to keep track of emails or messages I’ve sent (by date, so I can find them easily if necessary) and the ones received; Ahnentafel number of MRCA; Tree URL (pasting a link to their Tree in this cell lets me easily refer back to their Tree); Date (I try to enter a date for each Match as I add them (or use FTDNA’s date), so I can sort and easily figure out the most recent add); Kin (in this column I put the initials of my closest known kin who also Matches on this segment – color-coded this highlights the “side” this segment is on); side (M or P – this is necessary to use Kitty’s chromosome tool; it also lets you sort your spreadsheet to separate the Maternal and Paternal chromosomes; Donor (if you keep a spreadsheet for more than one person); GEDmatch number; GEDmatch generation estimate; 23andMe link (this hyperlink (from a download of all shared segments) takes you directly to the Match with an InCommonWith list – very handy); Y-DNA and mtDNA haplogroup (if you track or use them, I don’t find them very useful); Notes or Remarks (to remember anything else that’s important to you). If I don’t know an MRCA, I use the MRCA column to note anything interesting about the Match (Adopted, Irish, Jewish, all-North Carolina, UK, No Tree, etc. – it helps me)

  3. I use Genomate Pro. Everything in one place. It’s no more complicated to learn how to import data than it is to manipulate spreadsheets.

    • I manage kits for other relatives, including my mother, my brother and my aunt. Should I keep a full separate spreadsheet for each person?

      • Hi Linda – I like to keep a spreadsheet for each person but Jim likes to keep them all together. This is really a matter of personal preference.

    • Bonnie, Genomate Pro sounds like an incredibly worthwhile package. Personally I like to have the flexibility of spreadsheeting my way but then that is how i started. If anyone out there wants to do a guest post on Genomate and Genomate Pro I would be happy to publish it.

  4. Pingback: Favorite Reads of the Week: 10 December 2016 – Christmas Letters, Oliebolen, WWII Research Guide – Family Locket

Leave a Reply to Jim Bartlett Cancel reply

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