Native American & Minority Ancestors Identified Using DNAPainter Plus Ethnicity Segments

Ethnicity is always a ticklish subject. On one hand we say to be leery of ethnicity estimates, but on the other hand, we all want to know who our ancestors were and where they came from. Many people hope to prove or disprove specific theories or stories about distant ancestors.

Reasons to be cautious about ethnicity estimates include:

  • Within continents, like Europe, it’s very difficult to discern ethnicity at the “country” level because of thousands of years of migration across regions where borders exist today. Ethnicity estimates within Europe can be significantly different than known and proven genealogy.
  • “Countries,” in Europe, political constructs, are the same size as many states in the US – and differentiation between those populations is almost impossible to accurately discern. Think of trying to figure out the difference between the populations of Indiana and Illinois, for example. Yet we want to be able to tell the difference between ancestors that came from France and Germany, for example.

Ethnicity states over Europe

  • All small amounts of ethnicity, even at the continental level, under 2-5%, can be noise and might be incorrect. That’s particularly true of trace amounts, 1% or less. However, that’s not always the case – which is why companies provide those small percentages. When hunting ancestors in the distant past, that small amount of ethnicity may be the only clue we have as to where they reside at detectable levels in our genome.

Noise in this case is defined as:

  • A statistical anomaly
  • A chance combination of your DNA from both parents that matches a reference population
  • Issues with the reference population itself, specifically admixture
  • Perhaps combinations of the above

You can read about the challenges with ethnicity here and here.

On the Other Hand

Having restated the appropriate caveats, on the other hand, we can utilize legitimate segments of our DNA to identify where our ancestors came from – at the continental level.

I’m actually specifically referring to Native American admixture which is the example I’ll be using, but this process applies equally as well to other minority or continental level admixture as well. Minority, in this sense means minority ethnicity to you.

Native American ethnicity shows distinctly differently from African and European. Sometimes some segments of DNA that we inherit from Native American ancestors are reported as Asian, specifically Siberian, Northern or Eastern Asian.

Remember that the Native American people arrived as a small group via Beringia, a now flooded land bridge that once connected Siberia with Alaska.

beringia map

By Erika Tamm et al – Tamm E, Kivisild T, Reidla M, Metspalu M, Smith DG, et al. (2007) Beringian Standstill and Spread of Native American Founders. PLoS ONE 2(9): e829. doi:10.1371/journal.pone.0000829. Also available from PubMed Central., CC BY 2.5, https://commons.wikimedia.org/w/index.php?curid=16975303

After that time, the Native American/First Nations peoples were isolated from Asia, for the most part, and entirely from Europe until European exploration resulted in the beginning of sustained European settlement, and admixture beginning in the late 1400s and 1500s in the Americas.

Family Inheritance

Testing multiple family members is extremely useful when working with your own personal minority heritage. This approach assumes that you’d like to identify your matches that share that genetic heritage because they share the same minority DNA that you do. Of course, that means you two share the same ancestor at some time in the past. Their genealogy, or your combined information, may hold the clue to identifying your ancestor.

In my family, my daughter has Native American segments that she inherited from me that I inherited from my mother.

Finding the same segment identified as Native American in several successive generations eliminates the possibility that the chance combination of DNA from your father and mother is “appearing” as Native, when it isn’t.

We can use segment information to our benefit, especially if we don’t know exactly who contributed that DNA – meaning which ancestor.

We need to find a way to utilize those Native or other minority segments genealogically.

23andMe

Today, the only DNA testing vendor that provides consumers with a segment identification of our ethnicity predictions is 23andMe.

If you have tested at 23andMe, sign in and click on Ancestry on the top tab, then select Ancestry Composition.

Minority ethnicity ancestry composition.png

Scroll down until you see your painted chromosomes.

Minority ethnicity chromosome painting.png

By clicking on the region at left that you want to see, the rest of the regions are greyed out and only that region is displayed on your chromosomes, at right.

Minority ethnicity Native.png

According to 23andMe, I have two Native segments, one each on chromosomes 1 and 2. They show these segments on opposite chromosomes, meaning one (the top for example) would be maternal or paternal, and the bottom one would be the opposite. But 23andMe apparently could not tell for sure because neither my mother nor father have tested there. This placement also turned out to be incorrect. The above image was my initial V3 test at 23andMe. My later V4 results were different.

Versions May Differ

Please note that your ethnicity predictions may be different based on which test you took which is dictated by when you took the test. The image above is my V3 test that was in use at 23andMe between 2010 and November 2013, and the image below is my V4 test in use between November 2013 and August 2017.

23andMe apparently does not correct original errors involving what is known as “strand swap” where the maternal and paternal segments are inverted during analysis. My V4 test results are shown below, where the strands are correctly portrayed.

Minority ethnicity Native V4.png

Note that both Native segments are now on the lower chromosome “side” of the pair and the position on the chromosome 1 segment has shifted visually.

Minority ethnicity sides.png

I have not tested at 23andMe on the current V5 GSA chip, in use since August 9, 2017, but perhaps I should. The results might be different yet, with the concept being that each version offers an improvement over earlier versions as science advances.

If your parents have tested, 23andMe makes adjustments to your ethnicity estimates accordingly.

Although my mother can’t test at 23andMe, I happen to already know that these Native segments descend from my mother based on genealogical and genetic analysis, combined. I’m going to walk you through the process.

I can utilize my genealogy to confirm or refute information shown by 23andMe. For example, if one of those segments comes from known ancestors who were living in Germany, it’s clearly not Native, and it’s noise of some type.

We’re going to utilize DNAPainter to determine which ancestors contributed your minority segments, but first you’ll need to download your ethnicity segments from 23andMe.

Downloading Ethnicity Segment Data

Downloading your ethnicity segments is NOT THE SAME as downloading your raw DNA results to transfer to another vendor. Those are two entirely different files and different procedures.

To download the locations of your ethnicity segments at 23andMe, scroll down below your painted ethnicity segments in your Ancestry Composition section to “View Scientific Details.”

MInority ethnicity scientific details.png

Click on View Scientific Details and scroll down to near the bottom and then click on “Download Raw Data.” I leave mine at the 50% confidence level.

Minority ethnicity download raw data.png

Save this spreadsheet to your computer in a known location.

In the spreadsheet, you’ll see columns that provide the name of the segment, the chromosome copy number (1 or 2) and the chromosome number with start and end locations.

Minority ethnicity download.png

You really don’t care about this information directly, but DNAPainter does and you’ll care a lot about what DNAPainter does for you.

DNAPainter

I wrote introductory articles about DNAPainter:

If you’re not familiar with DNAPainter, you might want to read these articles first and then come back to this point in this article.

Go ahead – I’ll wait!

Getting Started

If you don’t have a DNAPainter account, you’ll need to create one for free. Some features, such as having multiple profiles are subscription based, but the functionality you’ll need for one profile is free.

I’ve named this example profile “Ethnicity Demo.” You’ll see your name where mine says “Ethnicity Demo.”

Minority ethnicity DNAPainter.png

Click on “Import 23andme ancestry composition.”

You will copy and paste all the spreadsheet rows in the entire downloaded 23andMe ethnicity spreadsheet into the DNAPainter text box and make your selection, below. The great news is that if you discover that your assumption about copy 1 being maternal or paternal is incorrect, it’s easy to delete the ethnicity segments entirely and simply repaint later. Ditto if 23andMe changes your estimate over time, like they have mine.

Minority ethnicity DNAPainter sides.png

I happen to know that “copy 2” is maternal, so I’ve made that selection.

You can then see your ethnicity chromosome segments painted, and you can expand each one to see the detail. Click on “Save Segments.”

MInority ethnicity DNAPainter Native painting

Click to enlarge

In this example, you can see my Native segments, called by various names at different confidence levels at 23andMe, on chromosome 1.

Depending on the confidence level, these segments are called some mixture of:

  • East Asian & Native American
  • North Asian & Native American
  • Native American
  • Broadly East Asian & Native American

It’s exactly the same segment, so you don’t really care what it’s called. DNAPainter paints all of the different descriptions provided by 23andMe, at all confidence levels as you can see above.

The DNAPainter colors are different from 23andMe colors and are system-selected. You can’t assign the colors for ethnicity segments.

Now, I’m moving to my own profile that I paint with my ancestral segments. To date, I have 78% of my segments painted by identifying cousins with known common ancestors.

On chromosomes 1 and 2, copy 2, which I’ve determined to be my mother’s “side,” these segments track back to specific ancestors.

Minority ethnicity maternal side

Click to enlarge

Chromosome 1 segments, above, track back to the Lore family, descended from Antoine (Anthony) Lore (Lord) who married Rachel Hill. Antoine Lore was Acadian.

Minority ethnicity chromosome 1.png

Clicking on the green segment bar shows me the ancestors I assigned when I painted the match with my Lore family member whose name is blurred, but whose birth surname was Lore.

The Chromosome 2 segment, below, tracks back to the same family through a match to Fred.

Minority ethnicity chromosome 2.png

My common ancestors with Fred are Honore Lore and Marie Lafaille who are the parents of Antoine Lore.

Minority ethnicity common ancestor.png

There are additional matches on both chromosomes who also match on portions of the Native segments.

Now that I have a pointer in the ancestral direction that these Native American segments arrived from, what can traditional genealogy and other DNA information tell me?

Traditional Genealogy Research

The Acadian people were a mixture of English, French and Native American. The Acadians settled on the island of Nova Scotia in 1609 and lived there until being driven out by the English in 1755, roughly 6 or 7 generations later.

Minority ethnicity Acadian map.png

The Acadians intermarried with the Mi’kmaq people.

It had been reported by two very qualified genealogists that Philippe Mius, born in 1660, married two Native American women from the Mi’kmaq tribe given the name Marie.

The French were fond of giving the first name of Marie to Native women when they were baptized in the Catholic faith which was required before the French men were allowed to marry the Native women. There were many Native women named Marie who married European men.

Minority ethnicity Native mitochondrial tree

Click to enlarge

This Mius lineage is ancestral to Antoine Lore (Lord) as shown on my pedigree, above.

Mitochondrial DNA has revealed that descendants from one of Philippe Mius’s wives, Marie, carry haplogroup A2f1a.

However, mitochondrial tests of other descendants of “Marie,” his first wife, carry haplogroup X2a2, also Native American.

Confusion has historically existed over which Marie is the mother of my ancestor, Francoise.

Karen Theroit Reader, another professional genealogist, shows Francoise Mius as the last child born to the first Native wife before her death sometime after 1684 and before about 1687 when Philippe remarried.

However, relative to the source of Native American segments, whether Francoise descends from the first or second wife doesn’t matter in this instance because both are Native and are proven so by their mitochondrial DNA haplogroups.

Additionally, on Antoine’s mother’s side, we find a Doucet male, although there are two genetic male Doucet lines, one of European origin, haplogroup R-L21, and one, surprisingly, of Native origin, haplogroup C-P39. Both are proven by their respective haplogroups but confusion exists genealogically over who descends from which lineage.

On Antoine’s mother’s side, there are several unidentified lineages, any one or multiples of which could also be Native. As you can see, there are large gaps in my tree.

We do know that these Native segments arrived through Antoine Lore and his parents, Honore Lore and Marie LaFaille. We don’t know exactly who upstream contributed these segments – at least not yet. Painting additional matches attributable to specific ancestral couples will eventually narrow the candidates and allow me to walk these segments back in time to their rightful contributor.

Segments, Traditional Research and DNAPainter

These three tools together, when using continent-level segments in combination with painting the DNA segments of known cousins that match specific lineages create a triangulated ethnicity segment.

When that segment just happens to be genealogically important, this combination can point the researchers in the right direction knowing which lines to search for that minority ancestor.

If your cousins who match you on this segment have also tested with 23andMe, they should also be identified as Native on this same segment. This process does not apply to intracontinental segments, meaning within Europe, because the admixture is too great and the ethnicity predictions are much less reliable.

When identifying minority admixture at the continental level, adding Y and mitochondrial DNA testing to the mix in order to positively identify each individual ancestor’s Y and mitochondrial DNA is very important in both eliminating and confirming what autosomal DNA and genealogy records alone can’t do. The base haplogroup as assigned at 23andMe is a good start, but it’s not enough alone. Plus, we only carry one line of mitochondrial DNA and only males carry Y DNA, and only their direct paternal line.

We need Y and mitochondrial DNA matching at FamilyTreeDNA to verify the specific lineage. Additionally, we very well may need the Y and mitochondrial DNA information that we don’t directly carry – but other cousins do. You can read about Y and mitochondrial DNA testing, here.

I wrote about creating a personal DNA pedigree chart including your ancestors’ Y and mitochondrial DNA here. In order to find people descended from a specific ancestor who have DNA tested, I utilize:

  • WikiTree resources and trees
  • Geni trees
  • FamilySearch trees
  • FamilyTreeDNA autosomal matches with trees
  • AncestryDNA autosomal matches and their associated trees
  • Ancestry trees in general, meaning without knowing if they are related to a DNA match
  • MyHeritage autosomal matches and their trees
  • MyHeritage trees in general

At both MyHeritage and Ancestry, you can view the trees of your matches, but you can also search for ancestors in other people’s trees to see who might descend appropriately to provide a Y or mitochondrial DNA sample. You will probably need a subscription to maximize these efforts. My Heritage offers a free trial subscription here.

If you find people appropriately descended through WikiTree, Geni or FamilySearch, you’ll need to discuss DNA testing with them. They may have already tested someplace.

If you find people who have DNA tested through your DNA matches with trees at Ancestry and MyHeritage, you’ll need to offer a Y or mitochondrial DNA test to them if they haven’t already tested at FamilyTreeDNA.

FamilyTreeDNA is the only vendor who provides the Y DNA and mitochondrial DNA tests at the higher resolution level, beyond base haplogroups, required for matching and for a complete haplogroup designation.

If the person has taken the Family Finder autosomal test at FamilyTreeDNA, they may have already tested their Y DNA and mtDNA, or you can offer to upgrade their test.

Projects

Checking projects at FamilyTreeDNA can be particularly useful when trying to discover if anyone from a specific lineage has already tested. There are many, special interest projects such as the Acadian AmerIndian Ancestry project, the American Indian project, haplogroup projects, surname projects and more.

You can view projects alphabetically here or you can click here to scroll down to enter the surname or topic you are seeking.

Minority ethnicity project search.png

If the topic isn’t listed, check the alphabetic index under Geographical Projects.

23andMe Maternal and Paternal Sides

If possible, you’ll want to determine which “side” of your family your minority segments originate come from, unless they come from both. you’ll want to determine whether chromosome side one 1 or 2 is maternal, because the other one will be paternal.

23andMe doesn’t offer tree functionality in the same way as other vendors, so you won’t be able to identify people there descended from your ancestors without contacting each person or doing other sleuthing.

Recently, 23andMe added a link to FamilySearch that creates a list of your ancestors from their mega-shared tree for 7 generations, but there is no tree matching or search functionality. You can read about the FamilySearch connection functionality here.

So, how do you figure out which “side” is which?

Minority ethnicity minority segment.png

The chart above represents the portion of your chromosomes that contains your minority ancestry. Initially, you don’t know if the minority segment is your mother’s pink chromosome or your father’s blue chromosome. You have one chromosome from each parent with the exact same addresses or locations, so it’s impossible to tell which side is which without additional information. Either the pink or the blue segment is minority, but how can you tell?

In my case, the family oral history regarding Native American ancestry was from my father’s line, but the actual Native segments wound up being from my mother, not my father. Had I made an assumption, it would have been incorrect.

Fortunately, in our example, you have both a maternal and paternal aunt who have tested at 23andMe. You match both aunts on that exact same segment location – one from your father’s side, blue, and one from your mother’s side, pink.

You compare your match with your maternal aunt and verify that indeed, you do match her on that segment.

You’ll want to determine if 23andMe has flagged that segment as Native American for your maternal aunt too.

You can view your aunt’s Ancestry Composition by selecting your aunt from the “Your Connections” dropdown list above your own ethnicity chromosome painting.

Minority ethnicity relative connections.png

You can see on your aunt’s chromosomes that indeed, those locations on her chromosomes are Native as well.

Minority ethnicity relative minority segments.png

Now you’ve identified your minority segment as originating on your maternal side.

Minority ethnicity Native side.png

Let’s say you have another match, Match 1, on that same segment. You can easily tell which “side” Match 1 is from. Since you know that you match your maternal aunt on that minority segment, if Match 1 matches both you and your maternal aunt, then you know that’s the side the match is from – AND that person also shares that minority segment.

You can also view that person’s Ancestry Composition as well, but shared matching is more reliable,especially when dealing with small amounts of minority admixture.

Another person, Match 2, matches you on that same segment, but this time, the person matches you and your paternal aunt, so they don’t share your minority segment.

Minority ethnicity match side.png

Even if your paternal aunt had not tested, because Match 2 does not match you AND your maternal aunt, you know Match 2 doesn’t share your minority segment which you can confirm by checking their Ancestry Composition.

Download All of Your Matches

Rather than go through your matches one by one, it’s easiest to download your entire match list so you can see which people match you on those chromosome locations.

Minority ethnicity download aggregate data.png

You can click on “Download Aggregate Data” at 23andMe, at the bottom of your DNA Relatives match list to obtain all of your matches who are sharing with you. 23andMe limits your matches to 2000 or less, the actual number being your highest 2000 matches minus the people who aren’t sharing. I have 1465 matches showing and that number decreases regularly as new testers at 23andMe are focused on health and not genealogy, meaning lower matches get pushed off the list of 2000 match candidates.

You can quickly sort the spreadsheet to see who matches you on specific segments. Then, you can check each match in the system to see if that person matches you and another known relative on the minority segments or you can check their Ancestry Composition, or both.

If they share your minority segment, then you can check their tree link if they have one, included in the download, their Family Search information if included on their account, or reach out to them to see if you might share a known ancestor.

The key to making your ethnicity segment work for you is to identify ancestors and paint known matches.

Paint Those Matches

When searching for matches whose DNA you can attribute to specific ancestors, be sure to check at all 4 places that provide segment information that you can paint:

At GedMatch, you’ll find some people who have tested at the other various vendors, including Ancestry, but unfortunately not everyone uploads. Ancestry doesn’t provide segment information, so you won’t be able to paint those matches directly from Ancestry.

If your Ancestry matches transfer to GedMatch, FamilyTreeDNA or MyHeritage you can view your match and paint your common segments. At GedMatch, Ancestry kit numbers begin with an A. I use my Ancestry kit matches at GedMatch to attempt to figure out who that match is at Ancestry in order to attempt to figure out the common ancestor.

To Paint, You Must Test

Of course, in order to paint your matches that you find in various databases, you need to be in those data bases, meaning you either need to test there or transfer your DNA file.

Transfers

If you’d like to test your DNA at one vendor and download the file to transfer to another vendor, or GedMatch, that’s possible with both FamilyTreeDNA and MyHeritage who both accept uploads.

You can transfer kits from Ancestry and 23andMe to both FamilyTreeDNA and MyHeritage for free, although the chromosome browsers, advanced tools and ethnicity require an unlock fee (or alternatively a subscription at MyHeritage). Still, the free transfer and unlock for $19 at FamilyTreeDNA or $29 at MyHeritage is less than the cost of testing.

Here’s a quick cheat sheet.

DNA vendor transfer cheat sheet 2019

From time to time, as vendor file formats change, the ability to transfer is temporarily interrupted, but it costs nothing to try a transfer to either MyHeritage or FamilyTreeDNA, or better yet, both.

In each of these articles, I wrote about how to download your data from a specific vendor and how to upload from other vendors if they accept uploads.

Summary Steps

In order to use your minority ethnicity segments in your genealogy, you need to:

  1. Test at 23andMe
  2. Identify which parental side your minority ethnicity segments are from, if possible
  3. Download your ethnicity segments
  4. Establish a DNAPainter account
  5. Upload your ethnicity segments to DNAPainter
  6. Paint matches of people with whom you share known common ancestors utilizing segment information from 23andMe, FamilyTreeDNA, MyHeritage and AncestryDNA matches who have uploaded to GedMatch
  7. If you have not tested at either MyHeritage or FamilyTreeDNA, upload your 23andMe file to either vendor for matching, along with GedMatch
  8. Focus on those minority segments to determine which ancestral line they descend through in order to identify the ancestor(s) who provided your minority admixture.

Have fun!

______________________________________________________________

Disclosure

I receive a small contribution when you click on some of the links to vendors in my articles. This does NOT increase the price you pay but helps me to keep the lights on and this informational blog free for everyone. Please click on the links in the articles or to the vendors below if you are purchasing products or DNA testing.

Thank you so much.

DNA Purchases and Free Transfers

Genealogy Services

Genealogy Research

Concepts – Sorting Spreadsheets for Autosomal DNA

This article covers both sorting in Excel and how to identify an overlapping segment, and what that means to you as a genetic genealogist.

I swore I wasn’t going to teach Excel, but there have been so many questions about sorting Excel spreadsheets that I am going to a very basic “how to sort and not hurt yourself” article. This does NOT replace actually understanding how to use Excel, but it will at least get you through the knothole of sorting for genetic genealogy.

I wrote more about sorting and filtering in the concepts article about assigning parental sides.

There are some advanced ways to accomplish the same thing, and I’m not discussing those. If you already know how to use Excel those are fine, but this article provides the basics for those who don’t.

Sorting

I am going to use, as an example, my matches to only a few people which gives us enough information to sort, but isn’t overwhelming.

When you download your results from Family Tree DNA, your spreadsheet will be in match name order, like the spreadsheet below.

SS Raw

I want you to notice that while the primary order is by match, there is a secondary order too (chromosome), and a third (start location) and fourth (end location) as well.

Within each match, the order is by chromosome, and then by start and end location.

What this means that you can look at Alice and see that chromosome 1 is first, and that the lowest value start location is shown first within chromosome order.

That’s not the order you’ll likely be working with all the time, so let’s take a look at how to sort the spreadsheet in a different way.

The row highlighted in red contain column headers.

SS column headers

When you sort an individual column you will select the header for that column, shown below, if you’re going to sort the Matching SNPs column.

SS Column select

The cell on your spreadsheet won’t be red, but I’ve colored it red here so you can see that I’m selecting this column header and only this column header.

When you select a column header, you put the cursor on that cell and click once.

SS column select 2

The cell you’ve selected will be bordered in black.  A screen shot of my spreadsheet is shown above.

I want you to watch what happens to these two rows colored green when I sort in Matching SNP order.

SS rows green

At this point, you will click on the sort and filter button on the upper right hand side of the toolbar.

SS sort dropdown

Here’s a closeup.

SS sort dropdown closeup

Selecting the “Sort A to Z” option sorts the contents of the entire spreadsheet in Matching SNP order, smallest to largest, because that’s the column header and sort option combination you selected. I use lowest to highest (A-Z) but you can also sort in reverse order, highest to lowest (Z-A) but that isn’t terribly useful for what we will be doing.

SS SNP column sorted

Notice that all of the rows are sorted into smallest to larger order by the Matching SNP column. So while the two green rows were originally together, now the rows all appear in order by the Matching SNPs column values.

The first green row match to Alice on chromosome 3 with 1300 cMs falls between the SNP value of 850 and 1458.  The second green row with a value of 2000 falls between 1638 and 2355.  This is exactly as it should be.  The contents of the entire spreadsheet are sorted by the values in the Matching SNPs column.

The statement “sorts the contents of the entire spreadsheet” is very important, because if you perform this task incorrectly, you will bollux up your entire spreadsheet, as in irrecoverably and forever.  What follows is an example of what NOT TO DO.

DO NOT DO THIS

DO NOT, and I repeat, DO NOT select the entire column to sort.

SS - Do Not Sort

This is an example of WHAT NOT TO DO.

If you select the entire column, as shown above, then sort, here’s what happens.

SS example bad sort

Notice that the green rows are now split apart – in other words they no longer form a row from left to right. That means that ONLY the data in the Matching cM column was sorted, but not rest of the data which is still in the same location on the spreadsheet as it was before the sort. Therefore, Alice’s green row Matching cM value of 1300 is no longer with Alice, since only the data in the Matching SNPs column was sorted. Now Alice’s 1300 cMs connected to Stacy’s red row on chromosome 4. Alice now has 500 SNPs instead, which as you can see, clearly isn’t accurate.

This is what I meant by selecting the entire column instead of just the header will forever ruin your data. If you do this, there is no recovery, unless you JUST did it, SS undo
realize the error, and can selecte the blue backarrow on the top of the toolbar on the left to “undo” your action. If you’re beyond that, the only recovery is to download your data again, or move to a backup if you have one.

What’s even worse if you do this and don’t realize it, so you’re working with incorrect data trying to find overlapping segments.  Of course, everything will be wrong.  I periodically do a sanity check and look at a couple people in the chromosome browser just to make sure that everything is as it should be on my spreadsheet and I haven’t done something like this.

To Sort Correctly – DO This

To use this spreadsheet effectively for genetic genealogy, we need the spreadsheet to be sorted in this viewing order:

  • Chromosome number
  • Start location
  • End location

In other words, we need the spreadsheet to look like this with all of the green cells remaining in their row with their match:

SS example good sort

You’ll notice that all matches on each chromosome are grouped together, with the smallest start location first, as illustrated by the red groupings of chromsomes 1 and 6. I do realize these are small segments, but the process is the same for large or small segments, so for our sorting example, just ignore any genealogical relevance associated with segment size.

You will be looking for overlapping segments. Notice that you have to be cognizatnt of the end location. In the case of chromsome 1, above, there are no overlapping segments for the two chromsome one matches, so they can’t match each other on this segment.

However, on chromsome 6, we have a different situation. Stacy’s segment match with me is quite long, 104cM. Stacy’s segment overlaps with everyone else’s on chromsone 6 that matches to me, either fully or part way. She matches Alice on all of the segments fully except for the last one. Stacy’s match to me ends at 108,000,000. Alice’s last segment matches to me from 107,779,220 which is included in Stacy’s match, but Alice’s match extends beyond Stacys, to 110,175,307.

Keep in mind that we don’t know at this point whether or not Stacy and Alice are from my mother or father’s side, based on matching. In other words, to draw any conclusions, we also have to know if Stacy and Alice match each other on this segment which we can’t tell from this spreadsheet.

Because I have access to Stacy’s account, I can indeed tell you that Stacy and Alice do not match each other on this segment, so they would be from different sides of my family tree. Stacy is a known relative from my father’s side and Alice does match my mother as well, so we now know that Stacy and Alice don’t match each other.

If you don’t have access to the accounts to see if your matches match each other, two tools at Family Tree DNA are partial substitutes.

  • The ICW tool tells you if two of your matches match each other, just not on which segments.
  • The maternal/paternal Family Matching tool, if you have connected the DNA of relatives who have tested, tell you which side your matches are from, maternal or paternal.

You can read about how to use those tools here.

If there are multiple matches with the smallest start location then they will be in order by the smallest end location first, shown in the yellow cells.

Sort Order

The sort order is exactly the opposite of the viewing order. If you want to SEE the data in this order:

  • Chromosome
  • Start
  • End

Then you must sort in this order:

  • End
  • Start
  • Chromosome

The last column you sort will be the primary viewing order.

Let’s look at our spreadsheet utilizing these three steps, in order.

Step 1 – First Sort

Selecting End Location to sort:

SS sort end location

After sorting by end location, below.

SS end location sorted

You will notice that all of the data is now in order by the values in the End Location column – smallest at the top, largest at the bottom.

The data in the other columns is not in any particular order at all.

Step 2 – Second Sort

Now selecting Start Location to sort that column in order, shown below.

SS sort by start location

Having sorted by Start Location, below:

SS sorted by start location

You will notice that now all of the data is sorted by start location. In the case where there is a common start location between two rows, highlighted in red, the end row with the lower end location will show first, noted in yellow, because you sorted first by end location in smallest to largest order.

Step 3 – Third Sort

Last, you’ll select the Chromosome column header to sort in chromosome order.

Sort by chromosome

Below, the result of sorting the third time in chromsome order.  After sorting, I bordered all segments on the same chromosome.

Sorted by chromosome

You can see that the entire spreadsheet is grouped by chromsome, and within chromsome number, the Start Location is grouped smallest to largest. If there are multiple people with the same start location, then the End Location comes into play, with the smallest end location listed first, as shown in the red and yellow rows.

If you want to sort your spreadsheet in another order for some reason, you can do so using the same methodology. Once you understand about sorting spreadsheets, you understand about sorting all spreadsheets.

Now, you’re ready to look for your overlapping segments.

What is an Overlap?

An overlap is two segments of your matches that are partially or completely overlapping each other.  When you have overlapping segments, assuming they are of decent size, that indicates that the two people who match you on your spreadsheet potentially match each other too.  Remember, there are three matching possibilities:

  • Your matches will either match each other, in addition to you, because you and both of them share a common ancestor or…
  • They both match you, but they won’t match each other because one is from your mother’s side and one is from your father’s side or…
  • One or both are identical by chance.  In you need a refresher on what identical by chance, descent and population mean, click here.

Ss no overlap

In this first example, above, there is no overlap between these two people on chromosome 17.  One begins at 31,000,000 and ends at 36,000,000 while the second person’s match with you doesn’t begin until 40,000,000, which is clearly beyond the end of 36,000,000, so there is no possibility of overlaps between these two individuals.  In other words, they cannot match each other on these segments.  However, clearly they both match you because they are both on your matching spreadsheet.

SS overlap 1

In the example above, the overlapping portion of the segment is from 38,000,000 – 40,000,000.  The second person’s match with you extends to 53,000,000, but the area between 40,000,000 and 53,000,000 does not overlap.

SS overlap 2

In the example above, the start number is lower for the top row than the second row, so the overlapping area is still from 38,000,000 – 40,000,000, because the matches don’t match from 36,000,000 to 38,000,000.

SS overlap 3

Occasionally, you have an overlap that is fairly miniscule, which I generally ignore unless they are in a group that has a larger overlap that overlaps or covers both smaller matches, as in the example above. You can see that our red and yellow rows have a very small overlap from 39,500,000 – 40,000,000. However, the top row includes the entire areas of both red and yellow rows, reaching from 33,000,000 to 55,000,000 which begins before either red/yellow row and ends after both red/yellow rows.  So either all 3 individuals will match each other, indicating a common ancestor, or the top row will match one of the red/yellow rows and not the other.

Combining Spreadsheets From Different Sources

The good news is that you can download your matches into a spreadsheet format from  23andMe, Family Tree DNA and GedMatch, but you do need to understand something about the basics of sorting and how to stay out of spreadsheet trouble. I am careful about combining spreadsheets sources for a couple of reasons.

  • First, the formatting is not exactly the same, so you may need to move columns to be in the correct order for your spreadsheet before actually combining them.
  • Second, there may be overlapping people between 23andMe, Family Tree DNA and GedMatch. You’ll need to figure out how you want to deal with that, especially on an ongoing basis when you need to add to or update your spreadsheet without overwriting or eliminating your matching work and notes relative to common ancestors and ancestral lines in the columns you’ll be adding.

I always make a backup file with a date name in the file name before doing combinations, and sometimes before sorting as well.

Learning Excel

If you want to learn more about how to use Excel, here are some additional resources to utilize.

I found some training videos for Excel including “Twenty with Tessa, Tips and Suggestions for Spreadsheets” which is focused on using spreadsheets with one name studies and genetic genealogy, but the principles are the same.  https://www.youtube.com/watch?v=Ll_cfhOZTl0&feature=youtu.be

When discussing this online, one person mentioned that they joined www.lynda.com and took the basic Excel class which she found very useful.

Kitty Cooper has instructions on her blog for how to make a matches spreadsheet as well.

www.DNAadoption.com has some good courses.  Their DNA for beginners covers using spreadsheets and is not just for adoptees!

______________________________________________________________

Disclosure

I receive a small contribution when you click on some of the links to vendors in my articles. This does NOT increase the price you pay but helps me to keep the lights on and this informational blog free for everyone. Please click on the links in the articles or to the vendors below if you are purchasing products or DNA testing.

Thank you so much.

DNA Purchases and Free Transfers

Genealogy Services

Genealogy Research