Do you feel a little overwhelmed when looking at your Ancestry DNA matches? I find that a great way to tackle thousands of matches is to download them into a spreadsheet.
This article has a step-by-step tutorial on downloading your Ancestry matches to an Excel spreadsheet. We’ve added a quick video for each step so you’ll see exactly what to do if you’re not familiar with Excel.
Our template spreadsheet will format your Ancestry matches into a list that looks like this:
Before we get to the tutorial, I’ll review some benefits and ideas for using a spreadsheet in your research.
Benefits Of Working With Ancestry Matches In A Spreadsheet
The power of spreadsheets allows me to search, sort, and filter the information I choose to track in a single list.
Filtering And Sorting On Tree Size
The Ancestry website lets us filter matches by the type of family tree (public linked, public unlinked, etc).
At the top of my Ancestry wishlist is the hope that they’ll add a filter on the number of people in the tree. That way, I can skip matches with teeny tiny trees.
We can usually predict that trees with three people have the match and their ma and pa, with all details anonymized. And often, public trees with six or seven entries look disappointingly like this :
In this tutorial, I’ll show you a download method that automatically puts the tree size for linked trees into your spreadsheet. Of course, this number is a snapshot from when you run the download.
If my research time is limited, I like to filter on a minimum of twenty people in a tree.
But maybe you don’t want to skip any match. Then sort the list in descending order of tree size!
Adding A Count Of Shared Matches
This may be less useful if you have a high number of second and third cousins. That usually means you’ll have plenty of shared matches with your DNA matches who are above 20 cM.
The rest of us will have many higher DNA matches with no shared matches at all. Some will have a paltry one or two entries on their Shared Match tab, where it’s obvious that the matches in common are their siblings.
I like to add a column to the spreadsheet with the number of shared matches. This lets me filter on my DNA matches with sizeable trees and several shared matches. Those offer great opportunities for research.
Our download method doesn’t grab this number, so it’s something that you’d need to add yourself as you review your match list.
Adding Notes That Can Be Filtered
Ancestry has a Notes field on the Match List page, but you can’t use it for filtering and searching.
I like to add columns into the spreadsheet with extra details that can be used for filtering. An example is to have a column with notable surnames that appear in the match tree.
Even if you stick a list of names into one field, you can use text filters to search and filter for a single name during your research session.
I’ve found that many people use Excel all the time, but aren’t familiar with the more advanced filtering. I say advanced, but text filters are very easy to use.
The trick is to format your notes in a way that makes them easy to filter and search.
Let’ say that you spotted something interesting in a family tree of a match, but you’re currently researching some other aspect. If you type in “check branch from Arizona another time”, you’ll forget all about it in your next research session.
Instead, you can set up a priority column and use a number to remind yourself that this match was important. As simple as 1, 2, or 3. Then you can filter on priority 1 matches a week or month later.
Finding Ancestry Matches On Other Sites
Ancestry is the odd one out when it comes to consumer DNA sites for genealogy. 23andMe, MyHeritage, and FamilyTreeDNA all allow you to download a list of your DNA matches.
It can be a very worthwhile exercise to discover which of your Ancestry matches are also on these other sites. In my experience, most people use one of the sites to have their most up-to-date family tree. That may not be Ancestry.
I’ve got a detailed tutorial article on how to find Ancestry matches on MyHeritage. It uses the same principles of downloading Ancestry matches as I go through in the article you’re reading now. So, you can follow this tutorial and then check out the other article later.
Using The Leeds Method
The Leeds Method is a way of organizing your DNA matches and shared matches to help getting past brick walls.
We have a separate article on using a spreadsheet to follow the Leeds Method. When you’ve gone through the process here, you can check out our article on using the Leeds Method with Ancestry. It has a separate spreadsheet that is designed for the specific process.
Why Isn’t There A Simple Button To Export Ancestry Matches?
Ancestry doesn’t provide a way to download your DNA matches from their website.
In 2020, Ancestry stopped the use of several independent applications that scrolled through the match list web page and copied the data into spreadsheets or files for you.
But you can also do this for yourself. Our tutorial is going to be a breeze to follow if you are familiar with using Excel macros.
And don’t worry if you don’t even know what that means. I’ve embedded video walkthroughs of my screen that takes you through five steps to get your matches nicely formatted in an Excel spreadsheet.
Getting Around Some Limitations Of Excel
The method that I describe in this tutorial uses filters in Excel to find the useful match details amidst all the other text that comes from the web page.
Unfortunately, Excel filters can get very slow (or not work at all) with large amounts of data. If I try to put a filter on a download of all my matches, I have to watch a little blue wheel spinning for what seems like hours. I’ve never been patient enough to wait to see how long it takes.
So, I recommend that you work with three to four thousand matches at a time. If you have a very powerful local computer, you can experiment with a higher number than that.
But how do you work with smaller sets of matches? Easy. Just use the Ancestry filters. That is step one of our walkthrough.
Using Our Ancestry Matches Excel Spreadsheet Template
To make this as easy as possible for you, I’ve put together a free Excel workbook that contains a macro that does some complex formatting.
There’s a link at the end of this article that will let you download a copy.
When you open the spreadsheet, Excel will warn you that it contains a macro. You may also need to “enable” the content to use the spreadsheet (just click yes if you’re asked).
Feel free to edit the macro for your own purposes.
Does the end result not look right?
I’ve had to change the macro twice in the past year to allow for changes in the Ancestry display. The most recent was the addition of the percentage of shared DNA.
A helpful reader alerted me that the macro wasn’t working. If you experience a problem, please drop a comment at the end of the article. I’ll check what’s changed on Ancestry again!
Step 1: Filter Your Match List On Ancestry
I like to start with my matches who have public linked trees. They are most likely to be useful to my research.
Later, I will run a new download on a filtered list of matches with public unlinked trees.
You can also use the centimorgan range filter to take sets of matches at a time. For example, take all your matches above 100 cm. Then drop down to a range of 40-99 cM etc.
If you aren’t familiar with the Ancestry website filters, then watch this quick video.
Step 2: Scroll Down To The End Of Your Matches
If you’re a long-time user of Ancestry, you’ll remember that they used to have “pages” of matches. Each page had fifty matches, and you clicked a link to keep moving to the next page.
The company switched the design to what’s called “continuous scrolling”. As you scroll downward, the web page keeps loading more matches until there are none left.
With this step, you need to scroll down to the end of your filtered matches. If you have a few thousand in your list, that can be very tedious.
My video shows you a very simple trick to avoid a pain in your finger from pressing the “page down” key. It’s no big secret: I simply balance a carefully positioned object on the keyboard and go have a coffee.
Extra tip: check that all info is shown on the last few matches
One more tip: make sure that the entire display has finished loading. I’ve scratched my head wondering why the last fifty matches in my spreadsheet didn’t have any tree information.
It was because the final match names had been displayed on the web page, but I’d copied the data before the tree information had appeared on the screen.
Step 3: Copy, Paste Special, And Remove Blank Lines
Now that you’re down at the end of the matches, we’re going to copy the web page text to a spreadsheet.
We make sure that it’s formatted properly for later steps by using some built-in Excel features.
There are two steps: copy the matches, and then remove blank lines from the spreadsheet. This two-minute video walks through the process. I also lay out the sequence below.
Copy Matches To Excel
- Select the entire web page by clicking the “ctrl” and “a” keys on your keyboard.
- Copy the contents with “ctrl” and “c”
- Open a new tab in your Excel spreadsheet
It’s important that you use Excel’s paste feature at this point. If you use your keyboard, you’ll get all the images e.g. the Facebook avatars, etc.
- Right-click in the first cell of the worksheet to open the context menu.
- Choose “Paste special”
- Choose “Text” as the formatting option
Remove blank lines
You’re going to see an unreadable splat of data in your spreadsheet now. Don’t worry, we’ll be cleaning it all up to be useful.
But the next thing to do is remove empty lines from the worksheet. This is because a later step will run an Excel macro which assumes that it has got to the end of the data when it hits a blank line.
If you don’t remove blank lines now, the macro won’t work properly later. Thankfully, Excel has a built-in feature to remove blank lines.
- Highlight the column
- Expand “Find & Select” in the Home menu
- Choose “Go to special”
- Choose “Blank” (and ok)
- Delete the selected blank lines by clicking the Delete button in the Cells section
By the way, it’s possible that you don’t have any blank lines in your display. For me, they appear with matches where I’ve added notes. And you may not use the Ancestry note feature.
But to be sure, run through this process anyway.
Step 4: Run The Macro To Highlight The Match Names
And this is where (some of) the magic happens. A macro is a piece of code that has been added to an Excel spreadsheet.
Our spreadsheet template contains one macro (there is a link at the bottom of this article for a free download).
Just make sure you’re on the page with the copied Ancestry data when you run the macro. If you’re not sure how to run a macro, watch this quick video:
What does the macro do?
The main point of the macro is to find and highlight all the match names in the big mass of data that you’ve copied.
It also copies the centimorgans and tree details into the same row as the match name, because I find those useful to have in the final spreadsheet.
The reason to make the match name line bright red will be clear in the next section! We’re nearly finished, I promise.
Step 5: Filter On Highlighted Match Rows
Now, we use Excel filtering to filter the first column on the color red.
You may not have been aware that Excel lets you filter by colors. But when you add a filter on the first column, you’ll see “Filter by color” as a choice in the drop-down menu. And red is available as a target.
This video shows you how to apply the Excel filter.
Step 6: Copy The Filtered Rows
Our spreadsheet template has a worksheet that’s been set up with a header row and formatted columns.
This is the final step to copy the filtered content into a nicely formatted list.
- Copy the five filtered columns i.e. highlight the five columns and use ctrl-c.
- Switch to the third worksheet which has been set up with a header row.
- Click into the second cell in the first column.
The trick is to use “Paste special” to paste the values.
When you right-click in a cell, you’ll see “Paste special” in the list of items. Choose “values” from the options presented. This stops the scary red color from being copied!
There will be one extra unwanted line in the second row. Delete that row, and you’re all done!
Don’t like Microsoft Excel and want to work with Google Sheets instead? You’d have to rewrite the Excel macro in my template to make it work with Sheets.
Alternatively, the crew over at Family History Fanatics have their own version of a spreadsheet template for Google Sheets. Check out their tutorial here.
If you don’t mind paying the price of a cup of coffee per month, then you can check out the DNAGedcom client tool. I recommend that you join their Facebook group, which will have answers to all your questions.
Free Ancestry Matches Excel Spreadsheet Template (With Macro)
We will send a download link to your email address. Enter the details below.