Tracking Ancestry DNA matches by Spreadsheet

INTRODUCTION

Do you feel a little overwhelmed when looking at your Ancestry DNA matches? I find that the best way to tackle thousands of matches is to download them into a spreadsheet and organize them in a way that every piece of information can be seen and filtered. The power of spreadsheets allows me to search, sort and filter on any and all pieces of information I choose to track in a single list. Here’s how it looks.

All columns of the Ancestry DNA Match spreadshet

THE BASICS

I start with the basics: the Match Name with the number of centimorgans and segments. I also put the Ancestry “cousin category” in with the basics e.g. “4th-6th” and 5th-8th” Cousin. This is useful when I’ve got the Ancestry DNA spreadsheet side-by-side with the Ancestry match list webpage.

THE SIZZLE: SHARED MATCHES

Counts of Shared Matches

Now for the powerful stuff. I want to see at a single glance whether I and a particular Match have Shared Matches. Simply by having a yes/no column, I can filter out matches that have no shareds. Sometimes I do this, and sometimes I don’t: the power is in my hands.

Even better, I show the count of Shared Matches that are displayed on the “shared” tab of the match page. The Ancestry website requires two clicks to get to the list of shared matches for every match. I think this is the biggest barrier to efficient research using Ancestry. I want this number of shared matches right in front of my face. Why do I call it “Visible Shared”? Because these are the shared matches that Ancestry shows you for that match. As opposed to the next column, which has what I call the “Hidden Shared” matches.

HIDDEN SHARED

Hidden Shared Column“Hidden Shared” is my own term for shared matches that Ancestry does not show you for a match, because they fall below the CM threshold it sets as its cut-off point. I’ll explain that in another blog post. I’m showing a real example from my own Ancestry results. When I look at the match page for Margaret, Ancestry shows me five matches I share with her. But across my 15 thousand odd matches, there are another 12 matches who show Margaret when I look at their match pages. These 12 are all below 20 cM with me, but many of them are 19 cM and I want to know about them.

Eagle-eyed people will notice that the Hidden Shared column is in italics. I italicize it to remind myself that the number is not absolute, instead it depends on how many matches I’m tracking with this spreadsheet. If it’s my highest Five Thousand matches, for me that includes all my matches above about 8.4 cM. Yes, my Ancestry DNA spreadsheet imposes my own cut-off threshold, but its a lot lot lower than the Ancestry website imposes.

The next column just adds the Visible and Hidden Shared matches together. I can sort the list quickly on the Total Shared Matches, and identify which of my matches have the most connections.

MORE SIZZLE: TREES

Tree Attributes Columns

The column with tree type lets me filter on Linked and Unlinked public trees, similar to the Ancestry filter. Ancestry also shows us the size of linked trees, but here is the value of a spreadsheet in which you can filter on any and all columns. By putting the tree size into a column here, I can choose to filter on a minimum number of persons in the tree. I usually set a filter of at least four persons, to avoid wasting time on trees that only have the match and two parents. I can also sort my matches on the tree size in descending order.

Tree Size Column

Look carefully at the second and third matches in the pic. Why are the numbers in red italics? That’s my way of indicating that this number should be treated with caution as it the size of an unlinked tree. Ancestry doesn’t show the size of unlinked trees on the main list webpage. I can understand why. A match may have more than one unlinked tree: one for themselves, one for their spouse, and one for their work colleague who they are helping with family research.

MULTIPLE UNLINKED TREES

But I prefer to give myself more information at a glance, and show an unlinked tree size. If the match only has one unlinked tree, then I’m prepared to assume that the tree represents their own heritage and they’ve just forgotten (or don’t know how) to link it to their DNA kit. The red italics tell me to be cautious.

Multiple Unlinked Trees

Wouldn’t it be useful to know which matches have only one unlinked tree, as opposed to several? Yes, that would be useful. So I have that piece of information in my spreadsheet. It’s a yes/no column called “Multiple Unlinked Trees”. I can choose to focus on matches with only one unlinked tree, as I don’t have to try and figure out which tree is relevant.

If you’re looking at the pic carefully, you’ll see I show a size when there are multiple unlinked trees, so how do I choose which tree to reference? When the match has two or three trees, I grab the highest size and show that one. When the match has more than three unlinked trees, Ancestry squirrel away their sizes into one more link-click. Life is too short, so I just stick with the highest size of the three trees that Ancestry will list on their Match page. That’s why the red italics: “treat with caution”.

COMMON TREE

Common Tree Column

Wait, what’s that column named “Common Tree”? You can share your tree within Ancestry, so if this “Yes/No” fields is filled, then it means this match shares a tree with at least one other match in the spreadsheet. Which match? I’ve got that info in a separate Ancestry DNA spreadsheet, I’ll blog about that another time.

TAKING A BREAK

Occasionally you just have to take a break from your Ancestry research. Christmas week. Summer vacation. Maybe that year when you were moving house, job, and country.

Created Date Column

The Created Date of matches is really useful when I’m coming back to Ancestry from an extended break. Simply by having the field in my spreadsheet, I can choose to show only matches created since a certain date. I also might want to filter matches that were created during a specific period, when my memory tells me I’d seen an interesting new match at the time but I forgot to make a special note against it.

WHO’S IN CHARGE?

Admin Column

Putting the name of the manager of a match into a separate column allows a search or filter on all matches who have a manager of a particular name. I’m phrasing that a little carefully, as it doesn’t mean that the matches have the same manager. If the manager is named “J.S.”, then you have to examine the matches a little carefully to be sure its the *same* manager and not two different ones. This is for really focused research on a particular match, when you’re looking for all possible clues.

The “Male/Female” attribute isn’t particularly useful until you’re composing a message to “C.W.” and have a preference for the “Dear Sir” or “Dear Madam” salutation.

ANCESTRY DNA SPREADSHEET LAYOUT

What’s with the color-coding? It’s purely to minimize information overload by marking the match info into discrete sections. Green for trees of course, and yellow for shared match information. The light blue is for what I think of as fixed information i.e. the centimorgans and created date etc aren’t going to change if I take another download of matches a month later. I split these admin columns left and right, with the Shared and Tree information slap bang in the middle of the spreadsheet. That is so that information all fits on one screen, so I can scan my eye quickly over many matches and see the attributes that are most likely to guide me to a decision that I want to research this match further. I’ve got the URL (web address) of the match over on the right, which lets me click and open the match page in Ancestry immediately.

HOW TO DOWNLOAD ANCESTRY DNA MATCHES

When I started using Ancestry, I was keeping track of this information manually. Yes, I was clicking endlessly through Ancestry and typing the data into a spreadsheet.
I went looking for some other solutions, and found a few out there. There are at least two free utilities that will dump out a data file that you can import into a spreadsheet. There is at least one commercial piece of software that will do similar.
I wrote my own software because I wanted the Ancestry DNA spreadsheet that I’ve described on this page. If you’d like the same data for your own matches, we offer a consultancy service that produces the spreadsheet described here, along with other spreadsheets such as the shared matches of your matches (name, centimorgans, tree size, URL link etc).

WANT TO GIVE IT A TRY?

Check out our free trial offer. There are a limited number of free trials each month, as it does take a bit of time to put together. 

3 thoughts on “Tracking Ancestry DNA matches by Spreadsheet”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.