If your family tree is online on Ancestry.com or MyHeritage, there’s no simple way to export a list of surnames from the website.
This article shows what a simple way to extract a list of surnames from your tree into a spreadsheet.
Then I’ll show you some neat stuff you can do with that list.
Strategy To Get A List Of Unique Surnames From Your Tree
These are the overall steps to take:
- Export your family tree to a GEDCOM file
- Import the GEDcom file into a free desktop family tree application
- Use the family tree software to export a list of all people in the tree
- Import the list into spreadsheet software
- Use the spreadsheet software to generate a unique list of surnames
Export Your Family Tree To A GEDCOM file
If you already have a GEDCOM file with your family tree, you can skip this step.
If your family tree is on Ancestry.com or MyHeritage, here are step-by-step walkthroughs:
Which Desktop Software To Use?
I expect that all the major family tree applications can export a list of people in a format that can be opened in a spreadsheet.
You want the details to be in separate columns when you import them into a spreadsheet.
I’ve done this successfully with two free applications: RootsMagic Essentials and Family Tree Builder.
I found Family Tree Builder to be easier for this specific task. You can download the free software from the MyHeritage website.
Use Family Tree Builder To Export A List Of People
You should now import the GEDCOM file into Family Tree Builder. The import option is under the File menu.
The next step is to export a list of people that lets you extract surnames.
Family Tree Builder has the option to export lists into files already formatted for Excel.
- Expand the Edit drop-down menu in the top bar
- Choose “Export to Excel” from the list.
- Click on “Export list of people”.
As long as you have Microsoft Excel on your computer, the formatted list will open in Excel.
Use Your Spreadsheet To Create A Unique List Of Surnames
You now have a spreadsheet with multiple columns.
It’s still a text file, so you should save it as a .xlsx file.
The next step is to copy the surname column to a new tab or spreadsheet. Now, we can manipulate this single-column list.
Remove blank rows
If you have entries with a first name and no last name, you will have blank rows in the column. At this point, I like to remove them.
Here are the simple steps to remove blank lines in Excel.
Eliminate duplicates
This step gives you a list of unique surnames.
Follow these steps to eliminate duplicate rows in Excel.
Create a fixed header
Family Tree Builder has given you a first row with “Last name” in the field. Let’s turn this into a proper header.
Bold the text, and freeze the top row.
Order the list
Now, we can order the unique names alphabetically.
Check How The Surnames Rank In Popularity
Our website has a simple online tool that tells you how a surname ranks in the 2000 and 2010 U.S. census.
There are quick instructions and examples in our article on “how rare is my last name?“
It’s not a complicated tool, so you might just want to jump straight into it. Here’s the link to the surname ranking tool.
Rareness And Geographic Maps
I mentioned that we can do some neat stuff with this list.
One of the things I like to do is identify how rare some of these surnames are in the United States or Ireland. These tend to be easier to research.
I also like to see how some of the surnames are distributed across the United States and the rest of the world.
Forebears.ie
I find one website that is useful for both these details. You can read more about Forebears.io in our article on rare and uncommon last names.
The Forebears.io website has a simple search feature where you enter a surname.
To get to the right search box, expand the “Names” menu on the home page and choose “Surnames”.
You can copy and paste a surname from your unique list into the search box.
But spreadsheet functions give you a nice shortcut to work through the list.
Spreadsheet Short-cuts
I’m going to create hyperlinks that jump straight to the Forebears results page for each surname.
Forebears actually have two results pages. The first gives you a list of alternate spellings for the specific name.
You can choose any of these alternatives to see the detailed results with rarity calculations and the distribution heat map.
Let’s give ourselves the option of opening either.
Add two new header fields: “Alternate Surnames” and Direct Link.
It will look like this:
Alternate surnames
Copy this string in the field below “Alternate Surnames” (include the = and the final bracket).
- =Hyperlink(“https://forebears.io/surnames?q=” & A2)
This is a function that turns the cell into a hyperlink that launches the surname search on the Forebears.io website.
But you only have the function in one row. So copy the field, and paste it down the entire column.
You should see a different name in each row.
Direct link
The direct link is to run the search on the specific name in your family tree (not look for alternate surnames.
Copy this string in the field below “Direct Link” (include the = and the final bracket).
- =Hyperlink(“https://forebears.io/surnames/” & A2)
Once again, you should then copy the field and paste it down the entire third column.
Try the links
The cells are now hyperlinks that jump you straight to the results.
This will save you a bit of time in your research.
