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:

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.

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.

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.

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:

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.

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.

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:

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.

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.

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.

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.

______________________________________________________________

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.

> 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?

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.

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?

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…

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.

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

Go back to the article titled “Step 1” and start there. If you haven’t read the Concepts series, that will help you understand what we’re doing and why. You can find them by typing Concepts into the search box.

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.

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.

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.

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.

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

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?

I’m pretty sure I just encountered the “pile-up” region on chromosome 1. The graphic for “Known Pileup Regions” reports a pile-up on chromosome 1 that is located from 118,236,043-151,667,721 in Build 36. Someone on my DNA Relatives list contacted me in regard to a shared segment on this chromosome, from 115,921,098-158,730,907.

This person almost certainly *is* a relative. He has both matching names and locations. These names include the names of a couple who are ancestral two me on two different paths. They are my 6th great grandparents through my father’s maternal grandfather, and my 4th great grandparents through my father’s maternal grandmother. His maternal grandparents were 2nd cousins twice removed.

I don’t know how the person who contacted me is connected to my 4th/6th great grandparents, but 23andMe’s prediction of “4th cousin” seems a bit closer than is likely, but their stated range of 3rd to distant cousin is certainly possible.

So case solved, right? Well, not quite. So far I’ve been talking about a relationship on my father’s side, but my father himself doesn’t actually *share* this segment. I do, and four of my five siblings do; and so do two of my nephews. But my nephews don’t share with their grandfather in the part of chromosome 1 in which the segment is located. Not only that, my maternal 2nd cousin at 23andMe *does* share this segment.

Looking at the other people who share this segment with me, it seems to be a “real” segment in at least some cases — as opposed to being a composite segment drawing on both chromosomes in the pair. However, I think the in many cases it’s a great deal older than 23andMe seems to believe.

23andMe reckons the length of the segment as 24.14 cM, which is significant. Yet on the chart of “Known Pileup Regions”, the region from 115,921,098-151,667,721 is a mere 7.01 cM. A different chart (https://isogg.org/w/images/1/13/Table_3_start_and_stop_positions.jpg) says 9.95 cM, but even using that number would require that the portion of the match from 115,921,098-118,236,043 be 14.19 cM. This seems counterintuitive, since it’s physically a much shorter portion of the segment. But the centimorgan is not a direct measure of the length of a segment. Rather, it is based on the probability of a crossover occurring within the segment.

The other possibility is that 23andMe is not taking into account the existence of a pile-up in this region. Part of the difficulty is that cM can only be based on a kind of “average probability”. In reality, cM should probably be reckoned differently depending on whether we’re talking about a male or female transmission of DNA, given the much higher average crossover rate for women than for men. But that’s surely a topic for another day.

I have a few dozen matches across the first half of chr 11, many of which are 30+ cM in size but don’t know how they connect to my tree. They match the other two dozen people whom I know exactly from where they trace.

What are the odds this is a pileup region? 30cM or greater seems to be huge.