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