07 March 2023

This Spreadsheet Sorts DNA Matches By Branch

If it's at all possible, get your close relatives to take a DNA test that you will manage. Choose someone from your maternal and paternal sides. If you can't test your parents, how about a half-sibling or a 1st cousin? Once they've tested, get ready to see all your DNA matches in a new light.

I'm so glad my parents agreed to take a DNA test. At first I asked them to test so I could more easily see which one of them was related to my DNA matches. Now AncestryDNA is doing that for us with their SideView™ feature. I figured I didn't need this feature. But I was wrong.

While watching videos from RootsTech 2023, one big thing dawned on me. You see, my parents are on each other's match lists. After so much digging, I'm sure they don't have a common ancestor who lived before the late 1700s. But I never thought to look at my parents' SideViews.

Looking at SideView from My Parents' Perspective

The result was pretty exciting. When I looked at Dad in Mom's match list, I saw:

  • their relationship falls in the 4th–6th cousin range
  • they share 37 cMs
  • their longest segment of shared DNA is 14 cMs
  • Dad is on Mom's paternal side of the family

That last item is great news because Mom's maternal side of the family dead ends too early. That ancestral hometown was a Papal State in Italy, so it didn't keep civil records before 1861.

When I looked at Mom in Dad's DNA match list, they still shared 37 cMs with the longest segment measuring 14 cMs. But, unfortunately, SideView shows Mom as Unassigned. Ancestry says this could mean there isn't enough information to assign Mom to one of Dad's parents. Your Unassigned matches may get updated in the future, so keep checking.

Analyzing DNA Matches in a New Light

It was Kelli Bergheimer's RootsTech presentation "DNA Misconceptions" that inspired today's project. I created a spreadsheet to compare my DNA matches' relationship to each of my parents and me.

I started by adding the names of my DNA matches in column A of a spreadsheet. I went from the top of the list down to my 44 cM matches. After 44 cMs my matches fell into the Distant Family category. I noted:

  • how many cMs we shared
  • whether a match is on my maternal side, paternal side, both sides, or unassigned

Then I went through each of my parents' match lists. I captured their matches down to a 44 cM match. If their match wasn't already in my list, I added a new line to the spreadsheet. I checked each of our lists for everyone in column A, noting shared cMs and which side of the family.

When all your maternal DNA matches are on Mom's list, and all your paternals are on Dad's list, you know you've found a cousin.
When all your maternal DNA matches are on Mom's list, and all your paternals are on Dad's list, you know you've found a cousin.

Next I used colors to show where Mom and I shared a match, where Dad and I shared a match, and where all 3 of us shared a match. In those cases, I added the length of our longest segment of shared DNA with the match in cMs. (If you use AncestryDNA, click your match's number of shared cMs to see what is the longest length.)

I found 10 matches that only Dad has, and 7 matches that only Mom has. All 17 had a strong number of shared cMs. It's possible I share an amount of DNA with them that's too small to make my list. There were no matches in my column that didn't match either Mom or Dad. That qualifies them as true matches and not false positives.

Are Your Matches Really in Your Family Tree?

Kelli Bergheimer speaks about IBD (Identical By Descent) vs. IBS (Identical By State) matches. IBS is also called IBC (Identical By Chance). An IBD match is definitely family—a true cousin. This is someone who fits in your family tree. An IBS/IBC match may or may not be a true cousin. They may share DNA with you because their ancestors' roots are planted so close to yours. And that's one way this spreadsheet helps you understand your matches.

If my parent(s) and I share a match with a good number of cMs, I can be confident that person is a true cousin. I did a bit of research on this concept, and the following numbers vary a bit from place to place. But here's the basic idea.

If the *longest segment* you share with a DNA match is:

  • 12 cM or more, you're almost surely true cousins
  • about 10 to 12 cM, there's a 90% chance you're true cousins
  • between 8 and 10 cM, there's a 50-50 chance you're true cousins
  • between 6 and 8 cM, there's less than a 50% chance you're true cousins
  • smaller than 6 cM, there's about a 10% chance you're true cousins

Even if you are true cousins, your most recent common ancestor (MRCA) may be too far up your tree to identify. Your odds of finding that MRCA get better and better when you and your match share a segment of 30 cMs or more. If you share a segment of about 16 to 30 cMs, you've got about a 50% chance of finding that MRCA. Any lower and the odds are slim to none. (My parents' longest segment is 14 cMs.)

The message is clear. Don't beat yourself up if you can't identify a match who shares a longest segment of less than 16 cMs with you.

Practical Ways to Use This Spreadsheet

I love this spreadsheet because it can tell me which of my 4 grandparents' lines connect me to a DNA match. I added a column next to the matches' names showing which grandparent(s) is the connection:

  • Adamo for Mom's paternal matches
  • Mary for Mom's maternal matches
  • Pietro for Dad's paternal matches
  • Lucy for Dad's maternal matches
  • various combinations to cover double matches and unassigned matches
Use your parents' AncestryDNA SideView™ to assign a match to one or more grandparents' branches of the family tree.
Use your parents' AncestryDNA SideView™ to assign a match to one or more grandparents' branches of the family tree.

This creates a sort of cluster when you sort your spreadsheet by the grandparent column. In my case, if Grandma Mary is their only connection to me, and they aren't a very strong match, I may not find our MRCA. (Again, that's because Mary's line dead ends too early.) If Grandpa Adamo is their only connection, I know they have roots in his hometown of Baselice, Italy. And sometimes a match's last name reassures me that my spreadsheet is correct.

What About Your Closest Tested Relatives?

Getting back to my parents matching each other—I went to DNA Painter to use the Shared cM Project 4.0 tool. I entered the total number of cMs my parents share: 37. The tool says that pedigree collapse or endogamy can affect the results. There's a good chance I have one or both of those things in my family tree. All my ancestors came from a small string of neighboring Italian towns.

Putting pedigree collapse or endogamy aside, my parents have:

  • a 50% chance of being 5th, 6th, or 7th cousins, along with several other relationships in that range
  • a 19% chance of being half 3rd cousins or 3rd cousins once removed
  • an 18% chance of being 4th cousins or half 3rd cousins once removed
  • a 10% chance of being half 2nd cousins or 2nd cousins once removed

Based on my extensive family tree, I'm confident my parents are 5th, 6th, or 7th cousins. All I can do hope that their towns' church records go public some day so I can dig into the early 1700s.

If you have very close relatives who've DNA tested, try this type of DNA analysis for yourself. I'm excited to be able to figure out my lower matches who have a stronger connection to one of my parents than to me.


  1. Help how doing get a copy of this

    1. You make your own. It's so simple it doesn't need a template.

  2. This is a great idea. I'm going to have to try to figure out how to make it work for me. My bio father and his brother tested on Ancestry, as did my mother's brother. I manage DNA for my father and his brother, but do not have access to my maternal uncle's DNA. My mother tested with MyHeritage. I kick myself every day for not having hers done with Ancestry, too. The swab was easier than spitting for her, though, that's why we went with MyHeritage. My father and his brother and my mother are all on GEDmatch. Maybe I can make this work.

    Thanks for a great project idea!

  3. PS I do manage the DNA of two maternal 1st cousins and a 2nd cousin who is on my maternal grandpa's side. Would it work to use them since they are on Ancestry and my mother isn't?

    1. It won't be as accurate with a cousin because they have other contributors to their DNA, but if you must use a cousin, make it a first cousin.