Download Your Ancestry Matches To Excel Spreadsheet

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.

options for tree filters with public linked trees selected

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.

shared dna filter with custom centimorgan range set to twenty to four hundred cm

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

  1. Select the entire web page by clicking the “ctrl” and “a” keys on your keyboard.
  2. Copy the contents with “ctrl” and “c”
  3. 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.

  1. Right-click in the first cell of the worksheet to open the context menu.
  2. Choose “Paste special”
  3. 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.

  1. Copy the five filtered columns i.e. highlight the five columns and use ctrl-c.
  2. Switch to the third worksheet which has been set up with a header row.
  3. 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.

14 thoughts on “Download Your Ancestry Matches To Excel Spreadsheet”

  1. Hi, thank you for the Excel spreadsheet and the clear guidance. Seems to be a problem in the macro not picking up matches where the new ‘Do you recognise them’ feature has been used. All others were ok. Thanks.

    Reply
  2. Hi Margaret,

    Recent reader, first-time caller. Really appreciate the assistance on your site. It is helping me move forward with finding long-buried (pardon the expression) family lines. On my mother’s side I can go back two generations and at that point am not even sure of the family names.

    I love the semi-automated Excel immigration. Noticed that within the past few days Ancestry has put in ANOTHER set of format changes so expect you’ll have to go through changing and updating this again.

    In the meantime, as long as you are working in Microsoft Excel, you may as well use Word.

    Word has very powerful find-and-replace functions allowing finding and replacing several lines of data in a single search. What Word does not have is decent spreadsheet functions.

    The solution is to use Word’s tab or table features to create an intermediate, formatted page which is then copied into Excel. It is also possible to simply save the final, edited Word document into a txt file with delimiters, then open it natively in Excel.

    As a Jewish person, I have a million 4th cousins and a billion at 5-8th cousin on Ancestry. When I copy their DNA Matches page, I scroll down to the last relative I can identify (paternal grandmother’s brothers great-great-granddaughter, about 70 cM shared DNA) and hold CTRL+SHIFT while returning to the top of the page. Then I use CTRL+C to copy only that portion instead of allowing the entire page to resolve and copying everything everything. There are matches there in the Distant Cousin realm with so little data as to be worthless to me. That copied portion is then pasted into Word (or Excel for those playing by the rules).

    Hope these comments prove useful to some.

    Reply
  3. Thank you so much for taking the time to make this necessary task easy to do. Shame on Ancestry for not providing a way to do this.

    Reply
  4. Oh my goodness! I am absolutely obsessed with your brilliant spreadsheet! Thank you for coming up with such an easy-to-understand solution for this annoying problem. Ancestry seems to love introducing “fun tools” to draw in new customers or reel back in testers who don’t subscribe, but seldom offer much of real value to the hardcore genealogy researcher. I’ve already shared your spreadsheet with several DNA Facebook groups I’m in. Can I ask for one minor tweak to the macro? One of the things they do get right is that when a parent (or parents) test, it shows you which side your match is on. It would be fantastic if your macro could grab that info & add it to a column in the final spreadsheet. Again, thank you so much for this great tool!

    Reply
  5. I downloaded the new macro from a much older version. I suspect I am doing something wrong.

    i tried to paste some screen grabs here, but apparently that is not enabled.

    The download from Ancestry worked fine.

    After the macro is run, some of the wrong cells are red (not necessarily red).

    Some names, and some other rows got highlighted. (in column A but not a name)

    A single letter appears in the second column (B) of some rows. usually in the row of a wrong cell highlighted.

    Hello, what am I (or the macro) doing wrong?

    Steve

    Reply
  6. Hi – Thanks for this but a little confused, Some of my names are appearing twice in the list when pasted in, but only showing once on the Ancestry, these do not have managed names..
    Also why only some of the pasted info is put into columns, when especially in my case I need the Name & the Managed Name, as I have Smith & Jones as part of my Grandparents, and many of the matches using initials which are the same. Also notes, you refer to notes being useful above, but not on the extract macro?

    Reply
    • The double names used to be when match had a picture – I was filtering that out. Maybe Ancestry has changed something there, I’ll take another look.
      Notes are difficult to grab automatically so I leave them behind.

      Reply
      • I took another look to see if there’s a glitch in the final list with double names. But I don’t think so. The reason you see them in the inital copy is that they are “behind” the picture for anyone who has uploaded a photo or has linked their account with Facebook. But the final copy gets rid of the duplicates.

        Reply
  7. Re Running the Macro.
    “just click on the Developer Tab”
    You have a developer tab?
    I don’t.
    BTW I have Excel Version 2108 Build 12346.20238
    Is this something I can fix?

    Reply
    • Thanks for mentioning this, sometimes Excel doesn’t show the Developer tab. You can enable it with these steps (I’ve also updated the article):

      (1) Expand the File menu in Excel
      (2) Click on the “Options” link at the very bottom
      (3) Click on “Customize Ribbon” (near the middle of the left pane)
      (4) Ensure that “Main Tabs” is selected at the top of the right pane
      (should be the default)
      (5) Turn on the “Developer” checkbox (near the bottom of the list)

      Reply

Leave a Comment

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