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

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:

  • File 1 – Your DNA Master Matches spreadsheet that you have created. This is the file you will be updating with information from the other two files, below.
  • File 2 – A current download of all of your chromosome browser file matches.
  • File 3 – A current download of a list of your matches.

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.”

concepts2-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.

concepts2-match-list

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?

concepts2-headers

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.

concepts2-pick-list

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.

concepts2-paste

Then click on Paste to paste the cells.

concepts2-paste-position

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.

concepts2-known-pileup-regions

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.

concepts2-snp-poor-regions

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.

concepts2-native-segments

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.)

concepts2-match-bucket

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

  • Both
  • Maternal
  • N/A
  • Paternal

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.

concepts2-insert-column

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.

concepts2-bucket-colors

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.

concepts2-bucket-columns

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

concepts2-bucket-paste

After the paste, your spreadsheet will look like this.

concepts2-bucket-rows

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

concepts2-bucket-match-sort

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.

concepts2-bucket-match-maternal

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.

concepts2-bucket-match-paternal

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.”

concepts2-mother

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.

concepts2-cheryl-to-mother

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.

Margaret Lentz chart

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.

concepts2-bucket-matching-blended

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

  • Cheryl matches me – white rows
  • Cheryl matches me on my maternal side – dark pink row imported from Match spreadsheet
  • Cheryl matches mother (Barbara) – light pink rows
  • Cheryl matches mother on her father’s side – blue row imported from Match spreadsheet

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:

  • Either or both parents
  • Grandparents
  • Aunts and Uncles
  • Full siblings
  • Great-aunts and great-uncles

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.

concepts2-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.

  • Your parents
  • If your parents are not both available, all of your full and half siblings
  • Your grandparents
  • Your aunts and uncles
  • Your great-aunts and great-uncles
  • All first, second and third cousins unless they are children of aunts and uncles who have already tested

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.

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

  1. Can this be TRANSLATED into plain English please!!!!

    Margie Hinton – a long time researcher who still needs help understanding what this says and means.

    On Thu, Sep 8, 2016 at 9:03 AM, DNAeXplained – Genetic Genealogy wrote:

    > robertajestes posted: “We’re going to do three things in this article. > Updating Your DNA Master Spreadsheet With New Matches Labeling Known Pileup > Areas Utilizing Phased Family Finder Matches You must do item one above, > before you can do item three…just in case yo” >

    • You need to add current matches to your Master Spreadsheet. You can add the Family Finder Match information there as well. And the pileups are for your convenience. Have you been following the Concepts articles and did you do Step 1 in the Managing Autosomal DNA series?

  2. Difficult to understand why all those analysis are not part of the deal when you purchase Familiy Finder from FTDNA. Incomplete product at best!

      • Given that downloading your matches and keeping combined spreadsheets, with notes and such about your ancestors is something that no company could do, I’m just grateful for the bucketed matching and the various tools. I am hopeful and have asked for a more comprehensive download, by date, in in the chromosome browser file, but beyond that, I don’t know how they could do these things. For example, a person is bucketed, but not all segments will be phased and matching, and if you have only one parent, they never can be, so FTDNA cannot put the bucketing information in the chromosome browser file because it would be on every single segment of that matching person, which may not be accurate. So what I’m saying is that aside from the download by date function, they can’t do the rest.

  3. I’m afraid of the answer to this question? Do it over! But just in case I missed something….
    the last time I updated my master list was April 21, 2014. (I don’t like to update) When I download a new match spreadsheet the earliest match date is July 17, 2015. This must have to do with a FTDNA update. Do I start over?

  4. Hi Roberta — thanks so much for this update — I was waiting earerly for it. However, at the risk of looking like a dummy, I have to admit, I ran into trouble right away with Excel. I simply could not get it to display the dates properly. Yes, I clicked only on the heading as instructed. It displayed all the first digit — ie 9/ — all together, then the second /2/, etc in order but mixed up all the years 2015 and 2016. I then spent about two hours with help and online with the only explanation that the date was incorrectly formatted to display properly. After much frustration trying solutions with Excel, I finally gave up and stripped out the dates I wanted manually, one by one.
    I’m just blowing off steam here — I know I can’t expect you to teach everything about Excel, but if you did have a simple explanation, I would sure appreciate knowing it for next time.

      • Perhaps if she formatted the date column as TEXT & then entered the dates that way, they might read properly. Years ago, it used to take a comma or a space before the date string to indicate to the Excel program that TEXT was needed, not calculations or formulas…

      • I did that, too, but sometimes things have a way of resolving themselves in time. Thanks anyway.

      • Tried the convert to text idea as well. I’m now thinking it may have something to do with the Excel version I chose when saving the file. I had about a dozen choices. Nothing to do but keep on trying, I guess. Thanks for ideas.

  5. Hi,
    Thanks for your great blog, really appreciated as a newby to all this. I have no parents or siblings but have an Aunt tested. I only have downloaded matches once, so all the match dates are the same. is it worth me doing this? ie no colour coding?
    thanks, Sue

  6. Good concepts. I stretched it a bit and imported the predicted relationship (into the “Relationship column), matching bucket(into the Side column) and “match last name” (new column on end) from my matching spreadsheet to the master spreadsheet. I also left in the “N/As” just to highlight those in my master spreadsheet as ones that couldn’t be determined by FtDNA criteria.

  7. Thank you, Roberta, I do appreciate your explaining in detail how to do these steps, and I was hoping to follow them, but with the extent of busy-work introduced here (I have over 400 new matches for 3 tests since your article of June 8), and with no clear value here for me (I have exactly one person, a 5th Cousin, on my FTDNA match list of 1300 people, who I know how I connect to, I don’t know if I will proceed with your guides any further. Yes, I may end up with many hundreds of Triangulated Groups (TG), but with no idea of how we connect, they all would seem to lead nowhere. I have spent many hours examining all of my best FTDNA TGs using several other techniques and come up empty. Is there any hope for all of the people in this sort of situation, short of waiting a few years and hoping our group of matches progress to make this a little easier? Tree analysis with a few of my Ancestry.com matches seems to be the only thing I have to work with of any consequence.

      • I have checked trees, I have built trees, I have contacted my most significant matches. Of the few that responded, none of us, all FTDNA estimated 2nd-4th Cousins (my top match of which is a full 5th Cousin, the only one I can place, so all the rest are apparently an equal or more distant relation), has a clue. I do have US colonial roots, both Northern and Southern, a few of which, so far, disappear into the mist of the 1700s.

    • Have you filled out your trees on Ancestry to the 6th or 7th generation back from you? In particular, have you built out the collateral lines? I made a version of my tree just for finding Ancestry matches. I’m also finding Roberta’s exercise as a way of understanding the concept of chromosome matching even if I don’t identfy the MRCAs.

      • Mostly yes, Bonnie, but still not yet possible on all lines, as I alluded to. Yes, I have been working on my tree in every direction for 40 years. Granted, a further learning opportunity.

  8. All of my aunts and uncles are half aunts and uncles. As a result, so are all of my cousins. That means that I can not use any of them for this, correct? Thankfully, my mom recently let me test her and a brother. I really enjoy your blog.

  9. FYI, there is an app called Greenshot that will allow you to do screen captures with the drop down menu open. Once installed, you open the dropdown menu, press the print screen key, and select the desired area to capture. You can also easily obfuscate an area of the capture for privacy reasons. I’ve used the app for years and won’t want to work without it.

  10. Roberta, thank you so much. This has been a huge project for me, and I deleted all matches less than 3 cM, just to get the spreadsheet down to about 16,000 rows. I am tempted to delete those under 4cM, to make it easier to work with. I have many questions, but am willing to wait for Step 3 for most of them! I have my mother’s test and mine in my master spreadsheet, and I also have a few 2nd and 3rd cousins identified in the system so I have some “bucketed” matches. I do wonder though – on matches that are in my Maternal Bucket, should I disregard segments that do not match my mother? I do have some matches that match both my mother and father (who is deceased, but a 2nd and 3rd cousin have given me some matches).

  11. I have some questions about the pile up regions.

    Most of the matches of my mother at Family Tree DNA seem to have a Jewish ancestry and match on chromosome 6 or 11. My mother does not have a known Jewish ancestry yet.
    Am I right that the matches in the pile up region 6 mentioned in the table are IBD and should be ignored?
    At http://isogg.org/wiki/Identical_by_descent there is also a reference to pile up regions at chromosome 11 by research by Albechtson (2011) and Gusev (2011).
    Should those pile up regions be added in your table?

    My other question is what it means that my father who got an Indonesian mother and a European father with some Indonesian ancestors does not have those matches at chromosome 6 and 11? Actually he has 43 matches against my mother 1438. Could this beside a lack of Indonesian participants, be caused by the pile up regions for my mother?

    Would it be a suggestion for Family Tree DNA to be either find matches on a certain chromosome, or who do not match at a certain chromosome or to chose to filter our the pile up regions?

  12. Pingback: 2016 Genetic Genealogy Retrospective | DNAeXplained – Genetic Genealogy

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s