Site icon DNAeXplained – Genetic Genealogy

Concepts – Managing Autosomal DNA Matches – Step 2 – Updating Match Spreadsheets, Bucketed Family Finder Matches and Pileups

Roberta Estes

We’re going to do three things in this article.

  1. Updating Your DNA Master Spreadsheet With New Matches
  2. Labeling Known Pileup Areas
  3. Utilizing Phased Family Finder Matches

You must do item one above, before you can do item three…just in case you are thinking about taking a “shortcut” and jumping to three. Word to the wise. Don’t.

OK, let’s get started! I promise, after we get the housework done, you’ll have a LOT of fun! Well, fun for a genetic genealogist anyway!

Updating Your Chromosome Browser Spreadsheet

If you haven’t updated your chromosome browser spreadsheet at Family Tree DNA since you originally downloaded your matches, it’s time to do that. You need to do this update so that your DNA Master Spreadsheet is in sync with your current matches before you can add the Family Finder bucketed matches to your master spreadsheet. Just trust me on this and understand that I found out the hard way. You don’t have to traipse through that same mud puddle because I already did and I’m warning you not to.

Let’s get started updating our DNA Master spreadsheet with our latest matches.  It’s a multi-step process and you’re going to be working with three different files:

The steps you will take, are as follows:

  1. Download a new Chromosome Browser Spreadsheet, but DO NOT overwrite your existing DNA Master spreadsheet, or you’ll be swearing, guaranteed. This chromosome browser spreadsheet is downloaded from the Family Finder chromosome browser page. Label it with a date and save it as an Excel file.
  2. Download a new Matches spreadsheet. This spreadsheet is downloaded from the bottom of your matches page. Label it with the same date and save it as an Excel file too.
  3. Update your Master DNA Matches spreadsheet utilizing the instructions provided below.

If you need a refresher about downloading spreadsheet information from Family Tree DNA, click here.

Your Matches spreadsheet will include a column labeled “Match Date.”

On your Matches spreadsheet, sort the Match Date column in reverse order (sort Z to A) and print the list of matches that occurred since your last update date – meaning the date you last updated your DNA Master Spreadsheet.

If you need a refresher about how to sort spreadsheets, click here.

This list will be your “picklist” from the new chromosome browser match spreadsheet you downloaded. I removed the middle and last names the matches, above, to protect their privacy, but you’ll have their full name to work with.

After your spreadsheet is sorted by match date, with the most current date at the top, you’ll have a list of the most recent matches, meaning those that happened since your last file download/update. Remember, I told you to record on a secondary page in your DNA Master Spreadsheet the history of the file, including the date you do things? This is why.  You need to know when you last downloaded your matches so that you don’t duplicate existing matches in your spreadsheet.

Why don’t you just want to download a new spreadsheet and start over?

Remember the color coding and those pink columns we’ve been adding, at right, above, so you can indicate which side that match is from, if the segment is triangulated, how you are related, the most recent common ancestor, the ancestral line, and other notes? If you overwrite your current DNA Master spreadsheet, all of that research information will be gone and you’ll have to start over. So as inconvenient as it is, you’ll need to go to the trouble of adding only your new matches to your DNA Master spreadsheet and only add new matches.

Utilizing the new Chromosome Browser match spreadsheet, you are going to scroll down (or Ctl+F) and find the names of the people you want to add to your master spreadsheet. Those are the people on your Matches spreadsheet whose test date is since you last downloaded the chromosome browser information.

When you find the person’s name (Amy in this example) on the Chromosome Browser Match spreadsheet, highlight the cells and right click to copy the contents of those cells so that you can paste them at the bottom of your DNA Master Spreadsheet.

Next, open your Master DNA Spreadsheet, and right click to paste the cells at the very bottom of the spreadsheet, positioning the cursor in the first cell of the first row where you want to paste, shown below.

Then click on Paste to paste the cells.

Repeat this process for every new match, copy/pasting all of their information into your DNA Master Spreadsheet.  I try to remember to do this about once a month.

Housekeeping note – If you’re wondering why some graphics in this article are the spreadsheet itself, and some are pictures of my screen (taken with my handy iPhone,) like the example above, it’s because when you do a screen capture, the screen capture action removes the drop down box that I want you to see in the pictures above. Yes, I know these pictures aren’t wonderful – but they are sufficient for you to see what I’m doing and that’s the goal.

Combined Spreadsheets

In my case, if you recall, I have a combined master spreadsheet with my matches and my mother’s matches in one spreadsheet. You may have this same situation with parents and grandparents or your full siblings if your parents are missing.

You will need to repeat this process for each family member whose entire match list resides in your DNA Master spreadsheet.

I know, I groaned too. And just in case you’re wondering, I’ve commenced begging at Family Tree DNA for a download by date function – but apparently I did not commence begging soon enough, because as of the date of this article, it hasn’t happened yet – although I’m hopeful, very hopeful.

After your spreadsheet is updated, we have a short one-time housekeeping assignment, then we’ll move on to something much more fun.

Known Pileup Regions

I want you to add the following segments into your DNA Master spreadsheet. These are known pileup regions in the human genome, also known as excess IBD (identical by descent) regions. This means that you may well phase against your parents, but the match is not necessarily genealogical in nature, because many individuals match in these areas, by virtue of being human. Having said that, close relationships may match you in these regions. Hopefully they will also match you in other regions as well, because it’s very difficult to tell if matches in these regions are by virtue of descent genealogically or because so many people match in these regions by virtue of being human.

You can color code these rows in your spreadsheet so you will notice them.  If you do, be sure to use a color that you’re not using for something else.

I have used several sources for this information, including the ISOGG wiki phasing page and Sue Griffith’s great Genealogy Junkie blog article titled Chromosome Maps Showing Centromeres, Excess IBD Regions and HLA Region. The HLA region on chromosome 6 is the most pronounced. Tim Janzen states that he has seen as many as 2000 SNP segments in this region that are identical by population, or at least they do not appear to be identical by descent, meaning he cannot find the common ancestor. His personal HLA region boundaries are a bit larger too, from 25,000,000 to 35,000,000. Regardless of the exact boundaries that you use, be aware of this very “matchy” region when you are evaluating your matches.  This is exactly why you’re entering these into your DNA Master spreadsheet – so you don’t have to “remember.”.

By the way, Family Tree DNA and GedMatch use Build 36, but eventually they will move to Build 37 of the human genome, so you might as well enter this information now so it will be there when you need it. If your next question is about how that transition will be handled, the answer is that I don’t know, and we will deal with it at that time.

I do not enter the SNP poor regions, because Family Tree DNA does not utilize those regions at all, and they are the greyed out regions of your chromosome map, shown below.

On my own spreadsheet, I have a few other things too.

I have indicated chromosomal regions where I carry minority ancestry.  For both my mother and me, chromosome 2 has significant Native admixture.  This Native heritage is also confirmed by mitochondrial and Y DNA tests on relevant family members.

If you carry any Native American or other minority admixture, where minority is defined as not your majority ethnicity, as determined by any of the testing companies, you can utilize GedMatch ethnicity tools to isolate the segments where your specific admixture occurs. I described how to do this here as part of The Autosomal Me series. I would suggest that you use multiple tools and look for areas that consistently show with that same minority admixture in all or at least most of the tools. Note that some tools are focused towards a specific ethnicity and omit others, so avoid those tools if the ethnicity you seek is not in line with the goals of that specific tool.

Ok, now that our housekeeping is done, we can have fun.

Adding Phased Family Finder Matches to your Spreadsheet

I love the new Family Tree DNA phased Family Finder matches that assign maternal or paternal “sides” to matches based on your matches to either a parent or close relative. If you would like a refresher on parental phasing, click here.

We’re going to utilize that Match spreadsheet you just downloaded once again.

In this case, we’re going to do something a bit different.

This time, we’re going to sort by the last column, “Matching Bucket.” (Please note you can enlarge any image by clicking or double clicking on it.)

When you’re done sorting the “Matching Bucket” column , you will have four groups of matches, as follows:

I delete the N/A rows, which means “not applicable” – in other words, the match did not meet the criterial to be assigned to a “side.” You can read about the criteria for phased Family Finder matches here and here. If you don’t want to delete these rows, you can just ignore them.

The next thing I do is to add a column before the first column on the spreadsheet, so before “Full Name.”

In this case, you can highlight either the entire column or just the column heading, and right click to insert an entire column to the left.

If these are your matches, add your name in the “Who” column. If these are your parents’ or full siblings’ matches, add their names in this column. When you have a combined spreadsheet, it’s critical to know whose matches are whose.

Then select colors for the maternal, paternal and both buckets, and color the rows on your spreadsheet accordingly.

I use pink and blue, appropriately, but not exactly the same pink and blue I use for the mother and father spreadsheet rows in my DNA Master spreadsheet. I used a slightly darker pink and slightly darker blue so I can see the difference at a glance. The yellow, or gold in this case, indicates a match to both sides.

You’re only going to actually utilize the first two columns of information.

Highlight and copy the first two columns, without the header, as shown below.

Then open your master spreadsheet and paste this information at the very bottom of your spreadsheet in the first two columns.

After the paste, your spreadsheet will look like this.

Next, sort your spreadsheet by match name, this case, RVH is the match (white row).

Be still my heart. Look what happens. By color, you can see who matches you on which sides, for those who are assigned to parental buckets.  Now my white RVH match row is accompanied by a gold row as well telling me that RVH matches me on both my maternal and paternal sides.

Let’s look at another example. In the case of Cheryl, she is my mother’s first cousin. Since I have combined both my mother’s and my spreadsheets, you can see that Cheryl matches both me and my mother on chromosome 19 and 20 below. Mother’s match rows are pink and my rows are white.

In this example, you can see that indeed, Cheryl is assigned on my maternal side by Family Tree DNA, based on the dark pink match row that we just added. Indeed, by looking at the spreadsheet itself, you can confirm that Cheryl is a match on my mother’s side. I am only showing chromosome 19 and 20 as examples, but we match on several different locations.

I don’t have as many paternal side matches, because my father is not in the system, but I do have several cousins to phase against.

Here’s my cousin, Buster, assigned paternally, which is accurate. In Buster’s case, I already have him assigned on my Dad’s side, but if I hadn’t already made this assignment, I could make that with confidence now, based on Family Tree DNA’s assignment.  The blessing here is that the usefulness of Buster’s assignment paternally doesn’t end there, but his results, and mine, together will be used to assign other matches to buckets as well.  Cousin matching is the gift that keeps on giving.

Because my DNA Master spreadsheet includes my mother’s information as well, we need to add her phased Family Finder matches too.

Mother’s Family Finder Matches

Because I have my mother’s and my results combined into one DNA Master spreadsheet, I repeat the same process for my mother, except I type her name in the first column I added with the title of “Who.”

Continue with the same “Adding Phased Family Finder Matches” instructions above, and when you are finished, you will have a Master DNA Spreadsheet that includes your information, your parent’s information, and anyone who is phased for either of you maternally, paternally or to both sides will be noted in your spreadsheet by match and color coded as well.

Let’s take a look at cousin Cheryl’s matches to both mother and I on our spreadsheet now with our maternal and paternal buckets assigned.

As you can see, my results are the white row, and my Family Finder phased matches indicate that Cheryl is a match on my mother’s side, which is accurate.

Looking at my mother, Barbara’s matches, the pink rows, and then at Barbara’s Family Finder phased match information, it shows us that Cheryl matches mother on the blue, or paternal side, which is also accurate, per the pedigree chart below.

You can see that Barbara and Cheryl are in the same generation, first cousins, and Barbara matches Cheryl on her paternal line which is reflected in the Family Finder bucketing.

I have updated the “Side” column to reflect the Family Finder bucketing information, although in this case, I already had the sides assigned based on previous family knowledge.

In this example of viewing my mother and my combined spreadsheet matches, you are seeing the following information:

I find this combined spreadsheet with the color coding very visual and easy to follow.  Better yet, when other people match mother, Cheryl and I on this same segment, they fall right into this grouping on my DNA Master spreadsheet, so the relationship is impossible to miss.  That’s the beauty of a combined spreadsheet.

You can do a combined spreadsheet with individuals whose DNA is “yours” and they don’t share DNA with anyone that you don’t. Those individuals would be:

Why not half siblings or half aunts-uncles? Those people have DNA from someone who is not your ancestor. In other words, your half siblings have the DNA from only one of your parents, and you don’t want their matches from their other parent in your spreadsheet. You only want matches that positively descend from your ancestors.

While your grandparents, great-aunts, great-uncles, parents, aunts and uncles will have matches that you don’t, those matches may be critically important to you, because they have DNA from your ancestors that you didn’t inherit. So your combined DNA Master spreadsheet represents your DNA and the DNA of your ancestors found in your relatives who descend directly ONLY from your ancestors. Those relatives have DNA from your ancestors that has washed out by the time it gets to you.

Why can’t your cousins be included in your DNA Master spreadsheet?

I want you to take a minute and think about the answer to this question.

Thinking…..thinking….thinking…. (can you hear the Jeopardy music?)

And the answer is….

If you answered, “Because my aunt or uncle married someone with whom they had children, so my cousins have DNA that is not from an ancestor of mine,” you would be exactly right!!!

The great news is that between a combined spreadsheet and the new Family Finder bucketed matches, you can determine a huge amount about your matches.

After discovering which matches are bucketed, you can then use the other tools at Family Tree DNA, like “in common with” to see who else matches you and your match. The difference between bucketing and ICW is that bucketing means that you match that person (and one of your proven relatives who has DNA tested) on the same segment(s) above the 9cM bucketing threshold.  You can still match on the same segments, but not be reported as a bucketed match because the segments fall below the threshold.  “In common with” means that you both match someone else, but not necessarily on the same segments.

Here’s a nice article about utilizing the 9 tools provided by Family Tree DNA for autosomal matching.

The Beauty of the Beast

The absolutely wonderful aspect of phased Family Finder Matching is that while you do need to know some third cousins or closer, and the more the better, who have DNA tested, you do NOT need access to their family information, their tree or the DNA of your matches. If your matches provide that information, that’s wonderful, but your DNA plus that of your known relatives linked to your tree is doing the heavy lifting for you.

How well does this really work? Let’s take a look and see.

On the chart below, I’ve “bucketed” my information (pardon the pun.) Keep in mind that I do have my mother’s autosomal DNA, but not my fathers. His side is represented by 8 more distant relatives, the closest of which are my half-sister’s granddaughter and my father’s brother’s granddaughter – both of which are the genetic equivalent of 1st cousins once removed. My mother’s side is represented by mother and two first cousins.

Total Matches Maternal Side Bucket Paternal Side Bucket Both Sides Bucket Percent Assigned
Mother 865 13 106 2 14
Me 1585 356 361 3 23

Mother has the above 106 paternal bucketed matches without me doing anything at all except linking the DNA tests of mother to her two first cousins in her tree.  In my case, the combination of mother’s DNA and her two first cousins generated 356 maternal side bucketed matches, just by linking mother and her two first cousins to my tree.

Mother does have one third cousin on her mother’s side who generated 13 maternal bucketed matches.  So, while third cousins are distant, they can be very useful in terms of bucketed “sides” to matches.

It’s ironic that even though I have my mother’s DNA tested, I have slightly more paternal matches, without my father, than maternal matches, with my mother. Of course, in my case, that is at least partly a result of the fact that my mother has so many fewer matches herself due to her very recent old world heritage on several lines. Don’t think though, for one minute, that you have to have parents or siblings tested for Family Finder bucketed matching to be useful. You don’t. Even second and third cousins are useful and generate bucketed maternal and paternal matches. My 361 paternal matches, all generated from 8 cousins, are testimony to that fact.

The very best thing you can do for yourself is to test the following relatives that will be used to assign your resulting matches with other people to maternal and paternal sides.

The new permanent price of $79 for the Family Finder test will hopefully encourage people to test as many family members as they can find! For autosomal genetic genealogy, it’s absolutely the best gift you can give yourself – after testing yourself of course.

______________________________________________________________

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

Exit mobile version