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 (names changed for privacy):

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: Get 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.

It’s available in our online store: follow this link, enter a zero price (or give us a small tip), and click “I want this”. When you provide your customer email, you’ll be taken to a page to download the template.

A zip file will arrive in your downloads folder. It contains the spreadsheet.

You may need to take several steps to ensure that you can run the macro in this spreadsheet. Watch this video for each step:

YouTube player

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!

Works best with the Chrome browser

I advise that you log into Ancestry with the Chrome browser to copy your DNA matches.

Firefox, in particular, doesn’t seem to copy content with links. It leaves the centimorgan details behind and the macro won’t work properly.

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.

YouTube player

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.

YouTube player

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.

YouTube player

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:

YouTube player

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.

YouTube player

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!

YouTube player

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.

If you find this spreadsheet useful, consider donating the price of a cappuccino ($5) with the PayPal button below:

Margaret created a family tree on a genealogy website in 2012. She purchased her first DNA kit in 2017. She created this website to share insights and how-to guides on DNA, genealogy, and family research.

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

  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.

  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.

  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!

  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?


  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?

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

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

  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?

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

      • Thanks for this great overview of web scraping on Ancestry. I did something similar, using multiple tabs and formulas instead of macros. This allowed me to create the standard cluster chart from the otherwise inaccessible Ancestry data. I pasted data from my DNA match page into a single column, then pasted data from 100 (or so) different shared match pages in the subsequent 100 (or so) columns. Using control-A to select the entire page, then copy, then switch to Excel, then paste into the next column required quite a lot of time, but otherwise worked well and ultimately delivered the very helpful DNA cluster chart that groups matches by common ancestors.

        IW, you are so right: Firefox would not allow copy/paste of the cM data that Ancestry encapsulates within a button, but Chrome would. We Firefox users banged our head against a wall for quite a while before discovering this very important piece of information! Thanks so much for sharing!

  8. Instead of “Paste”>”Special”>”Text” when loading the Matches, I just simply “pasted” into Excel. It can take a LONG time for the machines to chug and the output looks messy, full of pictures, etc. BUT when done, if you “Find&Select”>”go to Special…” > select “Objects” > then push “Delete” key, all the pictures disappear. Then follow instructions to remove blank rows and run the Macro … Why this way ?? It preserves a HYPERLINK. For me, I ended with the same table look but I was able to click on any Match Name and it took me straight to the appropriate Ancestry MATCH page in a browser for easy access to more details

  9. Thanks so much for this great workbook!

    I’m having an issue where the macro runs fine for my lower matches, but it’s skipping over my closest matches and either not highlighting them, or it will highlight the name, but not pull up the other info to the same line.

    I copied the info from Chrome and ensured the page was fully loaded. I’m working with the desktop version of Office365 Excel.

    • My guess is that you have added great notes to your higher matches that describes their relationship to you. Unfortunately, the macro can get confused by content in the notes vs content in the normal display.
      If you wanted to email the spreadsheet to our support address (see the contact page), I’d be happy to take a look. You wouldn’t need to send the full data, just the top matches that aren’t working for you.

  10. I just wanted to say thanks for making all of this info available – it is greatly appreciated! As far as I am aware no one in my family has ever attempted a family tree, so I have ordered a kit from Ancestry and am excited to get started. The info you have provided here has already been super helpful, so thanks again!

  11. HI,
    Great tool, clear instructions. However, it failed for me when I hit Run the macro – nothing happened. I tried it with the column selected, with the entire Splat Sheet selected, and with nothing selected. Saved and re-opened the spreadsheet.
    I am running Window 10 and Excel that is part of MS Office Pro Plus 2016.
    Please advise.

  12. HI,
    Please ignore my previous post – I had not removed blank lines.
    It would help to not have to follow so many side links and just put all the instructions in a compact form.
    Again, thanks for developing this.

  13. I was just about to try this when I realized I was going to be doing a lot of scrolling. Then I remembered that someone had written some code that you could run that would auto mark matches on ancestry so they weren’t removed from your list when ancestry (in their infinite wisdom) decided to remove lower cm matches. Part of the code was an auto-scroll feature which I edited a bit to help with another project. Here is what you need to do:

    1. Open your ancestry match list in Chrome.
    2. Filter as you see fit.
    3. Open the Developer Tools, found under more tools.
    4. Switch to the console tab.
    5. If you know how many matches you have, or have filtered it down to, take that number and divide by 50. Add a 5 to that for good measure.
    6. Edit the following code, to replace the # with the number you got above If you did not do that math, replace the # with any number, If you use a bigger number the more times it will scroll so even if it finds the bottom you may still have to wait for it to stop trying. If you use a smaller number, you may need to run it multiple times to actually find the end.

    for (let l = 0; l setTimeout(r, 15000));
    console.warn(“Number of times scrolled ” + l);

    7. Copy and paste your edited code at the > in the console pane.
    8. Press enter and wait. You will know when it is done, when it has stopped the count.
    9. Once it is done, you may want to manly scroll a time or two to be sure you actually found the end. If necessary, consider rerunning the code, but pasting it again.

  14. Hello,
    I use LibreOffice Calc instead of Excel and when i try to paste in to the SPLAT tab nothing seems to be pasting. Is this because i am using LibreOffcie?

  15. I manage 3 DNA profiles and downloaded the macro, applied to each one separately. I loaded the matches into 3 excel worksheets. Is there a way I can search for and save same name matches using 2 at a time? Would I need additional code to create a macro to do that or is it available? I have about 660 4th cousins or closer in each one. The macro is used for deleting same name entries but I want to save a file with just those names that appear in each profile.
    I hope you understand what I am asking.

    • I think I understand. You want to identify matches that are common across the three DNA profiles?
      I think that the Excel has a feature that can help you with this – conditional formatting.

      Basically, if you get three columns of DNA matches into one worksheet, you can get Excel to color-code names that appear in all three columns. The trick is to use Conditional Formatting (in the styles section), choose “Highlight Cells Rule”, and choose “Duplicate Values”.

      I describe doing something similar in a different tutorial where I use the technique to highlight the same match names in spreadsheet data from Ancestry and MyHeritage. The relevant section is at this link.

      Hope that would work for you? Let me know if I’ve missed what you meant!

  16. I copied the matches from Ancestry but when I pasted them in Excel it didn’t include shared DNA information. Then when I ran the macro nothing happened – no names in red.

    • The spreadsheet doesn’t download shared matches, it only takes the information from the main list of matches.
      Sorry to hear you’re having problems, sometimes Ancestry tweak the web page and the macro stops working. But I ran it today and it worked find.
      Be sure that when you’re pasting the values, that you use “Paste Special” etc

      • I meant the relationship e.g. 2nd – 3rd cousin and DNA cMs shared with each match doesn’t show.
        Note – when I click on ctrl a it doesn’t highlight those items.This is a sample of what I get –

        ……………. Weatherall
        Father’s side
        Private unlinked Tree
        Do you recognize them?

        • Hi, Can somebody share the answer to this question? I just downloaded the spreadsheet today and seem to have the same problem with the CM and relationship data is not selecting when I use CTRL + A on the Ancestry match list.

          I assume I am doing something wrong but no idea what.

          So excited by the potential of this spreadsheet!

          • Please ignore my query. After sleeping on it I realized it was probably because of my browser. Switched from Firefox to Chrome and it worked smooth as silk.

            Apparently Firefox doesn’t like to copy all the columns.

  17. Thank you so much for your work. This is very helpful. One tweak I’d like to add is showing note comments, if possible. I’ve listed all my MRCA’s there and would love to have the included. Thought I might be able to modify your macro to include this, but it appears beyond my capabilities at the present.

  18. Dear Margaret,

    You are my hero! Thank you for this clear and concise how-to tutorial. Of course, double thanks for the macro! I have a suggestion to make this page even more awesome… if its possible… have the comments scroll down from newest to oldest, rather than oldest to newest. You are appreciated!

  19. This looks excellent. I spent 2 hours this afternoon and created a just about adequate macro to do this. Yours looks so much better. Here I am, in bed, just after midnight, like a kid on Xmas eve. Shall I get up and try it now…?

  20. It worked like a dream. I didn’t understand the line, “There will be one extra unwanted line in the second row. Delete that row, and you’re all done!”

    Thank you.

  21. I have requested the spreadsheet twice and still have not received it. I have also checked my spam/junk/trash folders and it did not come through them either. I was really interested in trying this and I am so disappointed.

  22. Margaret, Thank you so much for the spreadsheet. I was finally able to download it and it worked like a dream. I have been playing with it for a couple of hours. This will make it so much easier to figure out my dna matches from Ancestry.

  23. Margaret, thank you for this. I did it once manually, got very useful results, but vowed never to do it again. Now, with your macro and procedure, I can do it easily whenever I need it. Wonderful.

    A couple of things to let you know. First, I’m sure you’re aware but for readers who aren’t, your procedure is for recent versions of Excel. I’m on Excel 2003 which doesn’t have (to my knowledge) blank line removal nor color filtering. However, ASAP Utilities has both so I got’r done.

    Second, Ancestry has some new relationship names that aren’t in your vCousinList array. Perhaps these new names came with the new Edit Relationship feature? In any case, some of my matches were missed by the macro because Ancestry labeled them sister, nephew, niece, aunt, and uncle. There’s probably also a brother and maybe some others. I added the new ones I needed to your macro code and it worked beautifully.

    Regarding sides, there’s also a “Both Sides”.

    Thanks again.

  24. Hey Margaret,
    Drifting off to sleep and I had an epiphany: what if I used your spreadsheet for a specific proxy match and our shared matches? Jumped out of bed to try it and it worked!!
    This will be great for working on a specific line/cluster. So, I’m tweaking my Grid/Matrix spreadsheet to accompany this new revelatory tool you’ve created. Will share as soon as it’s done (and have slept). 😉

  25. Margaret,
    Please delete my reply above.
    Here is the improved Grid/Matrix sheet:!AnY4cGihA9l3keJDgMAVdyfJ8RRWGw?e=tladas
    (I expanded the matrix to accommodate 100 people instead of just 50.)

    Use this is to create clusters from a shared/PROXY match – i.e. someone who represents a specific line. Entering a 1 in the appropriate grid space colors that square and it’s corresponding twin on the other side of the diagonal.
    By going through a couple of shared match lists in a particular group, we can create our own clusters that could likely indicate triangulated matches to a shared ancestor.

    It’s not without some effort, but I think could be extremely helpful for brick walls.

  26. Hello Margaret
    I’m not sure why but when I run the macro only the cm data lines up with the name. The other information stays in the original column. I am using Excel 365.

    • Thank you for alerting me. Ancestry changed the display recently so I’ve had to make a corresponding change to fix the macro.
      I’ve uploaded a changed file. So you don’t have to resubscribe, I will send a link to your email.

  27. Hi Margaret, I am having the same problem as Nicola. Should I re-subscribe, or can you send a link to my email as well to get the updated macro?

    • Thank you Margaret, I have the new spreadsheet. Is there an easy way to copy the macro from the new spreadsheet into existing spreadsheets?

      • I guess “easy” depends on how familiar you are with the code interface in Excel. You could open the module and copy the content.
        But you can’t use the new code on an old copy of your Ancestry data.
        The change I had to make was in response to the display changing what used to say “mother’s side” to “maternal” and father to paternal.
        So, you should take a fresh copy of the display into the new spreadsheet.

        • Thanks, Margaret. I had already loaded thousands of matches into the Splat Sheet, and wanted to avoid having to do that over again. I was able to open both the worksheet I had started and the new worksheet, then go to the Developer menu, open the VB Editor, and drag and drop the new macro from the new worksheet into the worksheet I had started. Fairly painless …

  28. Margaret,

    Would you also send me the newest update that you sent Daniel and Nicola? I’ve been banging my head against my computer wondering where I went wrong and why it left behind so much information that I got successfully when I did this a year ago. LOL Thanks!

  29. I have spent 3 days trying to get your procedure for loading my matched DNAs to an Excel SS. The fault is with Ancestry not your procedure. When I go into Ancestry, select my cM range and hit Cntl A, the cM range is not selected. The line Parent’s 1st side is selected. So when I copy using Cntl C, the cM line is not copied, the “Parent’s 1st side” is copied. And that is what is pasted in the Splatch table. I think your procedure would be excellent to use. What can I do to fix it?

  30. Hi,

    Spreadsheet & macro work fine using Excel on a Mac but the Paste onto the Splat sheet also brings with it the match image. This hides a blank cell which in turn stops the macro.

    Is there an easy way to delete all the images to expose the blank cells?


  31. Hi,
    I’ve just downloaded the file but as it is an “xlsb” file, I don’t seem to be able to enable the macro. I have changed the settings within the trust centre in excel 2019 and also looked online for a possible solution: the only one was “change blocked to unblocked” on the general tab within properties in windows explorer but the file does not have a “general” tab. Any ideas?

  32. Hi,
    I downloaded your Excel file last week (thanks for that) and followed the step-by-step procedure, but when I launched the macro, it scrolled down and nothing happened. I have a few duplicate names and I work with the French Canadian site. Could it cause any problems? What did I do wrong? Need help.

    • Hi Marise,
      The macro depends on looking for words like “2nd Cousin” to split up the details on the page. So, it won’t work with the non-English site.
      I logged into the Canadian website and set the language to French, and it was the same as you described.
      When I switched the language to English (the drop-down at the top right of the page), and copied the page to the spreadsheet, it worked.
      I hope that’s a workaround for you.
      Best regards,

  33. Hi Margaret, Great spread sheet! Loved your logic to parse the file. If you’re interested, I revised the macro a bit to (1) read the whole ancestry text file without having to delete blank lines, (2) write the results to your Matches sheet as part of the macro, (3) speed up the macro, just email me and I’ll forward.

  34. I wrote an MS Power Automate script which scrolls to the bottom of the Ancestry page of matches, then reads all the data, parsing out the data from the html.. finally puts it all into an excel spreadsheet. It probably needs to be updated for recent changes.

    The script can be copied from the application, sent via email and pasted into the application elsewhere and should work as planned.

  35. Hi Margaret

    I love your macro!

    I’m wondering if you could make an addition of the notes?

    I have place my common ancestors and other sorting details there so I can easily see how my matches are related so the addition of those notes are very valuable to me.


  36. Nice project! I managed to do some extra manual work on MacOS platform and xlsm file. Will donate another 5 bucks. Thanks.

  37. I’ve used this for years. Definitely a time saver. Unfortunately for some reason when I go to filter by color it only colors the names. The other information such as cM, number of people in the tree, Ancestor, are not being included.

    In the part where cM should be, instead it’s putting I assume the side “P” “M”. Not sure why but I had to put those other fields in individually.

  38. Just a question, is there a way to include a column for Longest Segment lengths and number of total segments? Just curious.

  39. Margaret, with the recent changes from Ancestry to their matches pages, the macros don’t seem to be working again. I’ve tried both this one and the one specifically for doing the Leeds Method on my matches. It doesn’t take away any information, but no matter what I copy or don’t, or what browser I’m using, it doesn’t highlight the match names in red. It just “runs” the macro (or appears to) and then the page looks exactly as it did before. I’m using Excel 365 and Windows 10 and I’ve tried both Chrome and Edge.


Leave a Reply to Meredith Cancel reply

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