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.
Table of Contents
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:
- how to find Ancestry matches on MyHeritage.
- how to find 23andMe relatives on Ancestry
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.
Subscribe to get our free Ancestry template!
We also send a weekly newsletter with tips on making the most of your family research.
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!
This works best with the Chrome browser
A reader commented that this process works best when using the Chrome browser.
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.
If you find this spreadsheet useful, consider donating the price of a cappuccino ($5) with the PayPal button below:
FAQs
How do I download an Ancestry tree to excel? ›
From any page on Ancestry, click the Trees tab and select a tree. From the tree, click the tree name menu in the top-left corner and select Tree Settings. On the right side of the Tree Settings page, click Export tree. The button will begin spinning and say Generating a GEDCOM file.
How do I download my ancestry DNA data? ›- From your DNA Settings, select the test you'd like to download.
- Scroll down to the Download or Delete section and select Download DNA data.
- Select the box to confirm that you understand, and click Continue.
- Verify your account by choosing 1 of 2 methods: ...
- In the email, select Download DNA Data.
- Select the printer. at the top of the page.
- To print a one-page summary, choose Your DNA Results. To print a detailed report, choose Your DNA Story. Select Next. ...
- On the preview page that appears, select Print or OK to print your results.
To download your raw data, click on the Download tab from within the top navigation bar within the Browse Raw Data feature and scroll down until you see the "Submit request" button. You will receive an email to the email address associated with your 23andMe account when your raw data download file is ready.
Can I download a list of DNA matches from Ancestry? ›Can You Download Ancestry DNA Matches? While you can download your raw DNA data results and transfer the information to GEDmatch, MyHeritage, and FamilyTree DNA, you can't directly download your shared match list from AncestryDNA.
Can I convert a GEDCOM file to Excel? ›Overview. GedScape is software that lets you view, manipulate, convert and extract text data from GEDCOM files (CSV, for import to Excel), for Windows-based computers.
Can police use DNA from ancestry com? ›Law Enforcement Requests in the United States:
Contents of communications and any data relating to the DNA of an Ancestry user will be released only pursuant to a valid search warrant from a government agency with proper jurisdiction.
Companies like AncestryDNA and 23andMe provide your DNA raw data in a text file format, which in some cases may be compressed as zip files when downloaded. Other companies like MyHeritage and FTDNA provide your DNA raw data in the CSV file format.
Should I download my DNA data? ›One of the first things you should do after receiving your DNA test results from AncestryDNA is to download your raw DNA file. This is both for safekeeping and because you may want eventually want to transfer your DNA to another website to learn more from it.
How do I organize my DNA matches? ›- Download your raw DNA data. The very first step is to download your raw data from your testing company. ...
- Identify and label your DNA matches. Once that is complete, we can get to the match list. ...
- Map DNA matches for shared locations.
Why can't I print from Ancestry? ›
Dec 20, 2022•Knowledge
Printing facts is available from a browser and is not currently available in the Ancestry® app. From the profile page of a person in your tree, you can print out pages containing the facts of one person at a time. It's not currently possible to print out facts for everyone in your tree at once.
When you take an AncestryDNA test, your test results will include an ethnicity estimate. Part of this is an estimate—reported as a percentage—of where your ancestors lived hundreds of years ago, as far back as around 1,000 years.
Can you upload ancestry DNA results to 23andMe? ›Unfortunately, If you want to be in 23andMe's matching database, you'll have to purchase a test through them. They do not accept transfers from other companies to find DNA relatives. AncestryDNA isn't the only source of data the smaller companies will accept; they will also take data from one another.
Where can I upload my raw DNA data for free? ›- Family Tree DNA — Free DNA upload site.
- MyHeritage — Free DNA upload site.
- LivingDNA / Find My Past — Free DNA upload site.
- GEDmatch — Free DNA upload site.
- GPS Origins (Home DNA)
- Vitagene.
At GEDmatch, you can see up to 2000 matches for free, but you must be a Tier 1 subscription member to download your matches – and the download is restricted to your top 1000 matches.
Can I upload my AncestryDNA to other sites? ›If you've undergone DNA testing, you can download your raw DNA data and upload it to other DNA sites for additional analysis. Have you recently had DNA testing done through DNA sites like AncestryDNA, 23andMe, or MyHeritage?
How do I upload my AncestryDNA to living DNA? ›How do I upload my data? Create a Living DNA account, upload your DNA data through your portal and hit the upload file button at the bottom of your screen.
How do I read raw data on Ancestry? ›Sign in to your Ancestry account. From any page on Ancestry, click the DNA tab and select Your DNA Results Summary. On your DNA homepage, click Settings on the right side of the page. In the panel on the right side, click Download Raw DNA Data.
What software can open a GEDCOM file? ›You can open GEDCOM files in a variety of genealogy applications, including RootsMagic (Windows, Mac), Ancestral Author (Windows, Mac), MyHeritage Family Tree Builder (Windows, Mac), and GeneWeb (cross-platform). You can also upload GEDCOM files to Ancestry.com and FamilySearch.org (Web).
How do I convert a GEDCOM file to PDF? ›...
To convert into PDF.
- Open GEDCOM file in notepad.
- Press Control A and select all,copy it.
- Open MS Word-2010.
- Paste inside.
- Save it giving a name and chose file type as :. PDF.
Can you do conversion in Excel? ›
The Excel CONVERT function converts a number in one measurement system to another. For example, you can use CONVERT to convert feet into meters, pounds into kilograms, Fahrenheit to Celsius, gallons into liters, and for many other unit conversions.
Can Ancestry sell my DNA? ›Please note: Ancestry does not share your Genetic Information with third-party marketers, insurance companies, or employers, and we will not use your Genetic Information for marketing or personalized advertising without getting your explicit consent.
Does the government know your DNA? ›No, your DNA is not stored by the federal government as a part of the security clearance process, but yes, some biometric data (in this case, fingerprints) are currently required.
Who owns the data on Ancestry? ›...
Ancestry.com.
| Type | Private |
|---|---|
| Owner | GIC Private Limited The Blackstone Group (as of 2021) |
| Website | ancestry.com |
It is worth noting that previously, Ancestry.com suffered a data breach in which login credentials of 300,000 accounts were leaked in plain-text.
Does Ancestry delete your DNA? ›Ancestry will delete all your personal data from our systems within 30 days. Even though it takes some time for Ancestry systems to delete your personal data, effective immediately after completing the process, you will no longer have access to your Ancestry account, nor to any family tree or DNA Data.
What can I do with my AncestryDNA results? ›Shared surnames and birth locations
AncestryDNA identifies any surnames and birth locations that are in your tree and your match's tree. Exploring these could give you insights about where your shared ancestor might have lived or the lineage where you might want to look for your shared ancestor.
At the moment, he said, only two commercial DNA databases can be accessed by law enforcement: GEDmatch and FamilyTreeDNA. And their privacy policies require users to opt out if they're uncomfortable with their information being used. “It's a 100% voluntary system,” Morrissey said.
Is 3% DNA match a lot? ›You share around 50% of your DNA with your parents and children, 25% with your grandparents and grandchildren, and 12.5% with your cousins, uncles, aunts, nephews, and nieces. A match of 3% or more can be helpful for your genealogical research — but sometimes even less.
How do you triangulate an AncestryDNA match? ›- Point A. The first point is you (you're already a third of the way there!). ...
- Point B. The second point on your triangle is any other person on your match list. ...
- Point C. ...
- Find the total number of shared cMs for you and your match. ...
- Look up that number in a table. ...
- Check the genealogy of the match.
Are DNA matches 100% accurate? ›
Short answer: Not really 100%. The longer answer: While DNA is extremely good at identifying individuals, mistakes have happened.
Why you shouldn't take an Ancestry test? ›Your genetic information is not anonymous.
Testing companies anonymize genetic data before sharing it with outside companies or researchers. But that process can be reversed. It's possible to identify unique mutations in an anonymous sample of DNA and compare those quirks to public databases to identify the donor.
...
Printing
- On the left side of your tree, select pedigree view or family view .
- Go to the part of your tree you want to print. ...
- Select Print > Print > OK or Print.
Ancestry and other major genealogical companies give you the option to print family tree posters directly from your account. Your can print your tree directly from Ancestry.com by clicking the “Print” button near the top right of the screen (it's recommended that you print the pedigree view to save paper).
What is the oldest bloodline in the world? ›The longest family tree in the world is that of the Chinese philosopher and educator Confucius (551–479 BC), who is descended from King Tang (1675–1646 BC). The tree spans more than 80 generations from him and includes more than 2 million members.
How much DNA do you inherit from your great grandparents? ›| Relationship | Average % DNA Shared |
|---|---|
| Parent / Child | 50% (but 47.5% for father-son relationships) |
| Full Sibling | 50% |
| Grandparent / Grandchild Aunt / Uncle Niece / Nephew Half Sibling | 25% |
| 1st Cousin Great-grandparent Great-grandchild Great-Uncle / Aunt Great Nephew / Niece | 12.5% |
For instance, an inheritance between 3 and 7% could represent your 3rd, 4th, 5th, or 6th great-grandparents. That means instead of look at your 64 4th great-grandparents to find out who has a particular heritage, you now need to add: 32 3rd great-grandparents.
Can you copy an entire tree in Ancestry? ›Duplicating your tree
To create a duplicate tree on Ancestry, download the tree you want to duplicate, then upload it again as a separate tree. After creating a duplicate tree, you can delete the people that you don't want to include in the new tree.
- Select a family view, chart or report, then go to File> Export Family Page (or chart, report)> To Acrobat (PDF).
- Save the file. FTM automatically attaches a . ...
- Now anyone can open the PDF with the free Adobe Reader software. Even text in the charts is searchable.
The family tree template is not pre-installed, so you will need to be connected to the internet to find it. Search for "family tree" to find two options available for free download. If you don't see a search bar, look under "Microsoft Office Online," "Office.com," or "Online templates," depending on your Excel version.
How do I print an entire tree on Ancestry? ›
...
Printing
- On the left side of your tree, select pedigree view or family view .
- Go to the part of your tree you want to print. The part you see when you select Print is the part that will be printed. ...
- Select Print > Print > OK or Print.
Family trees
Unless you delete them, any trees you've created in your account will remain on the site whether or not you have a membership. As a registered guest, you'll be able to do the following with your tree: Adding and removing people and photos. Adding, deleting, and editing names, dates, and other facts.
You may request that Ancestry destroy your Biological Samples by contacting Member Services. Note that we may destroy Biological Samples that do not meet our quality assurance requirements at our discretion.
Can I make Ancestry destroy my DNA? ›You can delete your own AncestryDNA® results at any time from your DNA Settings page. Deleting your DNA results is permanent and cannot be undone.
Can you download Family Tree DNA? ›Family Tree DNA makes the raw results file from your Family Finder test available for you to download. To download your raw results: Sign in to your myFTDNA account. On the menu bar, click myFTDNA > MyDNA > Family Finder > Download Raw Data.
Can you convert a GEDCOM file to PDF? ›Here's how to convert GED to PDF and modify it quickly and easily: Import a file you need to convert with one of the available options. Drag and drop it to the highlighted area, upload it from your device storage or the cloud. Pause while it's processing and see the tool convert GED to PDF automatically.
What is the best way to document family tree? ›- Research logs—Fill in the purpose of each search, and source data on logs before looking at the source. ...
- Family group records—Keep up-to-date with source footnotes for every event. ...
- Photocopies of most sources—If the repository will allow it, ALWAYS make a photocopy.
Go to the Insert tab > Insert Hierarchy Chart > Treemap. You can also use Recommended Charts to create a treemap chart by going to Insert > Recommended Charts > All Charts.
Which Microsoft program is best for family tree? ›My Family Tree™ is a genealogy application for Windows®. You can use it to build interactive family charts in no time at all. For those just starting out investigating their family history, it provides an easy way to visualize and share your family tree with photos, stories and basic facts about each person.
Can you print a GEDCOM file? ›When your GEDCOM is finished compiling, click the green “Download Your GEDCOM File” button. This will open a window allowing you to select a place for the downloaded file. You are now ready to print your family tree in the program of your choice! Explore our wide variety of genealogy books for more information.
What is GEDCOM software? ›
GEDCOM (/ˈdʒɛdkɒm/ JED-kom), complete name FamilySearch GEDCOM, is a de facto open file format specification to store genealogical data, and import or export it between compatible genealogy software. GEDCOM is an acronym standing for Genealogical Data Communication.