Page Text: Lets start with the basic concept:
Each row on your spreadsheet represents one DNA match AND the “most-likely” shared ancestors(s).
Your “Most-likely” Shared Ancestors
I will be using the term “most-likely” quite a lot in this article. DNA helps significantly in genealogical research, but commonly doesn’t totally prove something, especially when you are dealing with distant relatives. There are two important factors here:
There may be dead-ends in your family tree that hide other relationships. As an example my gg-grandmother, Ann Atkinson was illegitimate. There are no records identifying her father. Through her husband I have a number of DNA matches with his ancestors. Since both Ann and her husband came from the same small community (Hawnby in North Yorkshire) then it is possible that either there is another inheritance path that connects my matches with our “most-likely” shared ancestors OR I share other unknown ancestors of my gg-grandmother with my matches.
There are circumstances that may well be hidden from any documentation that you have used in your research. Small DNA matches wouldn’t tell you that, say, one of your ancestors was having an affair with the brother of her husband, or was raped by her father-in-law.
What to record ?
My spreadsheet is split into three “logical” parts representing different things I want to store about my DNA matches. Each row of the spreadsheet represents one connection between myself and my DNA match.
Part 1 – Details about the Match
This is everything you want to know about the match themselves. Obviously you can record what information you want. I record the following
Match Name (or their Username, or their Initials)
Relationship. Although I enter this manually it’s based on calculated information in the second part of the spreadsheet. More later.
DNA testing company used (ancestry, ftDNA, MyHeritage , GEDmatch etc.). I colour-code these details. It wsometimes makes it easier to find matches.
Amount of shared DNA (both the centiMorgan total and the number of matches segments). Whilst the number of segments isn’t so interesting the amount shared is.
Whether they match on my Mum’s or Dad’s side of the family
Part 2 – Relationship details
Here I record their degree of Cousinship, normally in a shorthand form eg. 3C1R for a third cousin once removed. I also have a number of other fields I use to calculate and validate this information. As ever, a picture paints a thousand words. below is a skeleton spreadsheet that I’ve prepared.
Sample Excel spreadsheet – this shows the details I record for each match and the calculations used to validate Cousinship
Part 3 – the Names of our ancestors up to a common match
It’s probably easiest if I describe this part of the spreadsheet as a Christmas Tree. At the core I have a “central” column where I record the most-likely common ancestor and then progress with the children of both my match and myself until they are all recorded. I use one column for each generation. Again an illustration is better:
The ancestor “Christmas Tree”. My ancestors, Common Ancestry, DNA Matches ancestors.
Some points to note:
To add new DNA matches to this, right-click on the row number below the point where you want to insert a new. match.
Our most-likely shared ancestor(s) are recorded in black. I’ve use blue and pink to identify male and female ancestors. The colours help you spot direct paternal/maternal lines that may be interesting for additional yDNA/mitochondrial DNA testing.
For all ancestors I record their year of birth and a location. The year of birth helps me spot any errors in the paths to a shared ancestor. The locations really helps me remember where my relatives migrated to.
Since I’ve already recorded whether a match is on my paternal or maternal side (in the “Details of the Match” section) I don’t bother to re-record my Mum or Dad in this part of the spreadsheet.
There is a visual trade off between how many columns of data you allocate to record the descendants of your shared ancestors. The more columns, the greater the amount of time you spend scrolling. I’ve allocated 6 columns for “my side” ancestors. This allows me to record up to 7th Cousins. (My most extreme match is currently a 7th cousin twice removed).
I order matches based on which side of the family they come. Matches on my Mum’s side go at the top, matches on my Dad’s side are at the both.
Since both my parents were “only-childs” my nearest relatives are going to be second cousins, sharing one of 4 sets of great grandparents which I use for clustering matches.
Sometimes, thanks to cousin marriages, you may be related to someone in more than one way. To deal with this I record both paths of inheritance, but skip repeating some of the “Details of the Match” data to avoid screwing up some of my stats.
For ease of use I’ve split and frozen the spreadsheet after column B. This mans I always see the match name and their relationship to myself.
I often end up having to put in male or female “unknowns” in my DNA matches list. These are normally living parents and grandparents of my DNA match, which Ancestry hides for living people.
This is where the difficult bits come on. These are the five columns G to K in my spreadsheet. Each column is explained separately.
Count of Generations to Myself (column G).
I can count the number of direct ancestors I have recorded between myself and my common shared ancestor. The Excel function COUNTA does this by counting the non-blank cells in a range. Since I don’t record myself or my parents in the ancestors I add 2 to the COUNTA value to give me the total number of generations.
Count of Generations to my DNA match (column H)
Again the COUNTA function counts the number of generations between the shared ancestor and the DNA match. Since I have recorded all the generations to the match I dont need to add additional generations like I did with my own calculations.
Generations Difference (column I)
This is the result of subtracting column G (Generations to Myself) from column H (Generations to my DNA match). A positive number means that they have more generations to our shared ancestor. I use Excel’s ability to do Conditional Formatting to help highlight this information. As an aside I’ve found that in most cases where there is a difference it is my DNA Match that has more generations. My ancestors were slow to procreate!
Cousinship (column J)
I normally calculated Cousinship based on the minimum number of generations from either my DNA match or myself. The Excel function MIN does this. Given that first cousins are two generations back to a common ancestor I need to deduct one from this figure.
Number of Generations Removed (column K)
This is basically a count of the number of generations difference between myself and my DNA match. I’ve already, in column I calculated this, but since it’s always a positive number I use the Excel ABS function to calculate this.
Once I have all these fields calculated my relationship with my DNA match is column I and column J. I’m sure I could use Excel to enter this information directly in column B (the Relationship) field, however I’ve yet to work on this.
To summarise, below is a table of the formulas I use on in the spreadsheet.