Segmentology Common Ancestor Spreadsheet

I’m going to try a format here that will make it easier for me to explain some of my spreadsheet tools, and give you an easy way to copy the header (you can adjust the column widths to suit your self). Please let me know if this works for you, and I’ll try some more of them.

Copy the above column titled “Header Row” and paste it into your spreadsheet using the Transpose option. It should create the Header Row for the Common Ancestor Spreadsheet.  [Edit: it appears this doesn’t work from the image above – so just type them in a row across a spreadsheet.]

There are several types of rows for you to input:

1. Include one row for each of your Ancestor Couples – I highlight these rows      

2. There is one row for each Match with each known Common Ancestor (MRCA);              

3. I add a row for my MRCA Child & birth year with a NOTE to refer to appropriate Ahnentafel for more  

4. I add a row for Ancestor multiple marriages, and put marriage year in born column      

                This separates full cousins and half cousins.

5. If something looks fishy, or needs more investigation, I highlight it in orange/mud color.             

6. If an Ancestor/Ahnentafel number and a TG are in conflict, I highlight it in red. The genealogy may be correct but the shared DNA segment did not come from the MRCA         

Other NOTES:   

1. The main sort for this spreadsheet is Ahnen + born+ born +born columns         

                NB: Highlight all columns before sorting.

2. Another sort is on Match Name to analyze multiple MRCAs – only one TG per MRCA    

3. If you want to compare spreadsheets for different Test Takers, be sure to fill in the TT column first. Combine spreadsheets, sort, analyze, then sort on TT and separate the spreadsheets.       

4. Sidebar: I have an Ancestor Spreadsheet – one row for each Ancestor info, including the Ahnentafel number!  

5. I have typed all the data into my Common Ancestor spreadsheet – a lot of work             

                Idea: If you have a download of AncestryDNA Matches, start with that data for ThruLines Matches

6. If you want to be able to sort this by side (your paternal and maternal sides); add a column for P or M (or 2 or 3)               

7. Do not hesitate to add any other columns (or rows) that may be useful to you. I made up this spreadsheet, feel free to change it as you like.


1. It captures all of your Matches with Common Ancestors [some may be gone tomorrow…]        

2. It arranges the Matches’ descendants from the MRCA like a Family Group Sheet           

                Easy to compare with your own research

                Helpful in spotting many errors

                Easy to see Matches who are relatively close cousins to each other – good conversation starter

                Easy to highlight real and/or potential errors

                Easy to spot a Match at two companies with different names

3. Shows TG threads in a family [maybe Clusters too, haven’t tried them yet)       

                Makes it easy to spot TG threads through a family (closer Ancestors will have more TG threads)

Here is an example from my CA Spreadsheet:

[35BC] Segmentology Common Ancestor Spreadsheet by Jim Bartlett 20211219  

1 thought on “Segmentology Common Ancestor Spreadsheet

  1. Pingback: How Many NPEs Do You Have? | segment-ology

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.