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 our Excel spreadsheet template. There are quick videos for each step so you’ll see exactly what to do if you’re not familiar with Excel.
Before we get to the tutorial, I’ll show you exactly what you get in the spreadsheet. Then I’ll go through some benefits and ideas for using the spreadsheet in your family tree research.
What Does Our Ancestry Match Spreadsheet Give You?
Our spreadsheet template will format your Ancestry matches into a list with this information per DNA match:
- Match name
- Number of shared centimorgans (cM)
- The type of family tree:
- public linked tree (the most helpful to you)
- unlinked tree (still helpful)
- private linked tree (not helpful, unless they respond to your messages)
- no trees (I usually skip over these or filter them out)
- The number of people in a linked tree
- Whether Ancestry has identified a common ancestor with you
- Mother or father’s side (if this information is available)
Here’s an example from my latest download:
Benefits Of Working With Ancestry Matches In A Spreadsheet
The power of spreadsheets lets us search, sort, and filter the match information in a single list. Here’s some ways that I put this to use.
Filtering And Sorting On Tree Size
Our free spreadsheet template automatically puts the tree size for linked trees into your spreadsheet.
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!
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.
When you’ve used this article to download your matches, you may find these tutorials helpful:
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 spreadsheet for working through the Leeds method.
When you’ve gone through the process here, you can check out our article on using the Leeds Method with Ancestry.
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 the 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.
Step 0: Download Our Spreadsheet Template
To make this as easy as possible for you, I’ve put together a free Excel workbook that contains a powerful macro for formatting your data.
We will send a download link to your email. Fill in your email address below. It’s a two-step verification process, and you will get the download link in a few minutes.
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).
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.
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
The match list web page uses a design called “continuous scrolling”. As you scroll downward, the web page keeps loading more matches until there are none left.
In 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”
- Move to the next 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. If you don’t remove blank lines now, a later step won’t work properly later.
Thankfully, Excel has a built-in feature to do this. If you’re not sure how to do so,follow these simple instructions to remove blank lines in your Excel worksheet.
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:
Don’t See A Developer Tab In Excel?
A reader commented that they couldn’t see the Developer tab in Excel.
Don’t worry if it’s not showing for you. Follow these instructions to show the Developer tab in Excel.
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!
Alternative Methods And Other Programs
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.