23andMe Relatives Spreadsheet And Triangulation

23andMe lets you download a spreadsheet with details of your DNA relatives, including the chromosomes and positions where you share segments of DNA.

This is very helpful for several tasks, including triangulating your relatives to research common ancestors. If you haven’t tried triangulation, it’s probably a lot easier than you think.

I use the spreadsheet to pick and choose groups of DNA relatives to start the process. This article is a step-by-step walkthrough.

Download Your DNA Relatives Spreadsheet

23andMe provides a file with all your DNA relatives. The information includes

• which chromosome(s) have the shared DNA
• the size of each section of shared DNA
• the start and end positions of the shared segment on the chromosome

To download the file, open the DNA Relatives list and scroll to the bottom of the page.

You’ll see a button for requesting the file.

23andMe will send you an email link when your file is ready.

Their instructions say that there could be a wait of 24 hours. It took five minutes for me from my most recent request.

Convert The DNA Relatives File To A Spreadsheet

23andMe gives you a zip file that contains what’s known as a CSV file.  You should open this file with your preferred spreadsheet tool.

I like to use Microsoft Excel. You may want to open the file with Google Sheets. The process I’ll describe now is very similar, no matter what tool you use.

Using Excel, I save the file in .xlsx format to get access to the spreadsheet features.

If you upload the file into Google Sheets, the online software will convert the data into spreadsheet format.

Freeze The Top Row

The first thing I do is to freeze the header row.

This is because I will be changing the sort order of the data, and doing a bit of scrolling up and down.

Sort The Spreadsheet By Chromosome And Position

The spreadsheet is sorted in the same way as your DNA Relative List. Your relatives are listed in descending order of the closeness of relationship with you.

My top relative has two rows in the spreadsheet. This is because we share DNA on two different segments on the 7^th and 16^th chromosome pair.

One of my other relatives has five rows. Two shared segments are on the 2^nd chromosome pair, and the rest are on three different chromosomes.

But this isn’t the sort order that I want for how I will use the spreadsheet. So, my next step is to change how it’s sorted.

You probably know how to order a spreadsheet by a single column. In this case, we want to use three columns:

1. Chromosome #
2. Start position
3. End position

In Excel, you achieve this by choosing the “Custom Sort” option from the Sort & Filter menu. This lets you select the three columns in the order I list them above.

Hide Unwanted Columns

This step is optional. But I prefer an uncluttered display where I can see what I need without horizontal scrolling across the screen.

These are the columns that I hide:

• Surname
• # SNPs
• Full IBD
• Sex
• Set relationship
• Predicted relationship
• Relative range
• Maternal
• Paternal
• Maternal haplogroup
• Paternal haplogroup

This means that I can see all the information I want with one glance, including the family surnames.

Extra Formatting

This step is also optional, but I think it makes the spreadsheet much easier to deal with.

The chromosome start and end positions are lengthy numbers. I find them easier to read when they are in the “1000,000,000” format i.e. commas separate the thousands.

So, I change the formatting of these two columns to display as a Number with the 1000 separator. 

The Genetic Distance column shows the number of centimorgans with five decimal places. Again, this is awkward to read. I format the column to display as a round number.

Here’s an excerpt from my spreadsheet. My point is that I can see the name, chromosome details, relationship (cMs and percentage), and family surnames at a glance.

Find The Segment for Your Target Relative In The Spreadsheet

If you don’t already have a target to investigate, you may as well start with your closest relative with whom you have not yet identified a connection.

For me, that was my highest match on 23andMe. This relative, named Jennifer, is described as a 3^rd cousin.

My first step with unknown relatives is to open their profiles and look at our Relatives In Common.

In Jennifer’s case, we have plenty of fourth cousins in common and I don’t know where any of them fit in my family tree.

But if I can figure out my connection with Jennifer, a lot of other relatives could fall into place.

When I expand the DNA details for Jennifer, the chromosome browser on this page tells me we share segments on two different chromosomes.

I’ve got a separate article that runs through using the one-to-one chromosome browser on 23andMe

But I don’t know which segment is longer than the other yet. One drawback of this version of the browser on 23andMe is that it doesn’t show the different segment sizes.

So, my next port of call is to the spreadsheet. I just need to remember that Jennifer will appear several times (one row per shared segment).

I use the “find” feature in the spreadsheet to search for Jennifer. She appears twice in the spreadsheet.

Now, I choose a segment (usually the largest) and take a look at the neighboring rows.

Identify Other Relatives Who Share This Segment

The reason why we ordered the spreadsheet this way is that it makes it easy to identify a group of relatives who share the same overlapping segments.

In the excerpt below, notice how Jennifer’s segment has the exact same end position (27,377,120) as Derek, the guy below. But they have different starting positions.

While Ryan and Joan (the first two rows) don’t match either the starting or end positions of Jennifer. Although they themselves have the same starting position as each other.

Look for overlap, not alignment

Don’t think that your group of DNA relatives need to be aligned on the same starting and end position. That will rarely happen.

But the overlapping sections need to be of significant length. Otherwise, you could be dealing with sections that are identical by chance.

Eliminating Close Relatives

I’ve obscured some details of these matches, but I can tell you that Jennifer and Derek have listed the same four names in their “Family Surnames” section.

That was a bit of a clue! Having the same endpoint made me also wonder about how closely these two people were related to each other.

However, when I looked at the Relatives In Common list for Jennifer, there was no sign of Derek on the first page of our shared relatives. I had to go to the fifth page to find Derek. Sure enough, he is labeled as her brother.

One of the drawbacks of the Relatives In Common list is that you can’t change the sort order. It is ordered by how close the relationship is with you, not the DNA match.

My shared percentage of DNA with Jennifer is 0.81% while Derek is a fraction of that at 0.24%

It’s down to the random nature of inheritance that I share over three times the amount of DNA with Jennifer as with her sibling!

However, I’ve now identified that two of the people in my group are siblings. I’m going to remove Derek, who shares less DNA with me. But why?

Because for the purpose of triangulation, having Derek doesn’t give extra value over Jennifer. This is because they have an identical pedigree (parents, grandparents, etc).

So, it’s worth eliminating one sibling and bringing in another row from the spreadsheet that overlaps with this area.

Close relatives are useful in other ways

That doesn’t mean you should now ignore the interesting fact that these two people are close relatives.

One may be much more open to communication than the other. One may have a tiny family tree on another genealogy website, while the sibling has a much bigger version.

I add notes to 23andMe and to the spreadsheet to remind myself of this.

Repeating the process of elimination

When I looked for another row to add to the Chromosome Browser, I skipped two who had the same surname as Ryan.

But before I skipped them, I looked at my Relatives In Common with Ryan. I had to go as far as page four to find that these two were Ryan’s sister and mother.

More useful information from close relatives

I also noticed that Ryan was 1^st cousin to Kathleen and also to another row in the spreadsheet in the Relatives In Common list.

So, I worked through my Relatives in Common with Kathleen to find that Kathleen was a sister to this lady.

Kathleen hadn’t added any family surnames, but her sister had.

I didn’t swap Kathleen out of the browser for her sister. But I added the sister’s family surname details into Kathleen’s row in the spreadsheet (with an explanatory note to remind myself why).

Swapping in more relatives into the group

My goal is to get five people in the group who are distant cousins to each other, and overlap on the same chromosome.

The reason for the magic number five is that I will be using the Advanced Chromosome Browser in a later step. This browser allows a maximum of five DNA relatives to compare to your own kit.

Next Steps – Triangulation

This article was about forming a group of your DNA relatives that have overlapping DNA on the same chromosome.

Identifying common ancestors

The end goal is to ensure that every member of the group inherits this piece of DNA from a common ancestor.

This lets you switch to standard genealogy techniques to look for intersections of surnames and locations in their family trees.

You may be able to gather this information through communicating with your DNA relatives. Alternatively, you may be able to find their public trees on other platforms. And you may have to roll up your sleeves and build back their family trees for them!

Triangulation

The challenge is that at this point of your preparation, you cannot be sure that everybody in the group shares the same piece of DNA with each other.

All you know now is that they share the section with you.

Triangulation is the process that takes you to the end goal. On 23andMe, you can use a combination of the Relatives In Common display and the advanced chromosome browser.

I’ve got a separate article that walks through the process of this technique. Consider it as part two to the article you’re reading now.

So, your next step is to work through the steps in our article on triangulation with the 23andMe chromosome browsers.