When you’re building your family tree, the rarer surnames are often the easiest to research.
My grandmother was a Smith which makes that ancestral line a challenge to explore. My early research focused on marriages in the Smith line to people with less common last names.
This tutorial shows you how to use a spreadsheet to rank everyone in your tree based on how common their surnames were in the 1990 U.S. census.
It’s true that rankings change over decades and centuries, but this gives you a good starting point for research targets.
Microsoft Excel or Google Sheets?
I’ve tested this tutorial with both Excel and Sheets. The same formulas work in both applications.
There are a few differences in the steps that I’ll call out as we go.
Need Some Help?
If you’d like a shortcut to downloading the U.S. census, formatting your spreadsheet, and copying the formulas – we’ve got you covered.
You can download our template for free. You just need to type or paste your list of surnames and everything else is set up for you. We’ll add you to our newsletter but you can unsubscribe any time.
Want to do it all yourself? Keep going!
Step 1: Make A Surname List
You need a list of target surnames that you want to check.
If you have a small family tree or just want to check your direct ancestors, you could simply type a list of last names into a document.
But what if you want to check every unique name in a large family tree? You’ll probably want a way to copy those names easily.
Here is a tutorial that covers several methods to get a surname list from your tree.
Step 2: Get The Census File From The U.S. Census Bureau
Download a file of surname rankings from the 2000 U.S. Census with these steps:
- Go to this U.S. Census page.
- Click the file named “File B: Surnames Occurring 100 or more times”.
This puts a zip file called “names.zip” into your downloads folder.
Right-click the file to extract the contents.
Two files will be extracted – one with the CSV extension and one with the XLSX extension.
Both have the same data in different formats. They contain the ranking and total number of all surnames in the 2000 census that had at least one hundred respondents.
Step 3: Set Up Your Spreadsheet
Option 1: using Google Sheets
Go to Google Sheets and start a blank spreadsheet.
- Expand the file menu
- Click on Import
- Switch to the Upload tab
- Upload the app_c.csv file
It may take a few minutes for the file to upload. When it’s finished, you will have one tab in the online file called “app_c”.
Option 2: using Microsoft Excel
Open the XLSX file that you have just unzipped.
There will be a warning message at the top saying that it is in protected view. Click the “Enable Editing” button to continue.
Step 4: Create A New Tab
Add a new tab to the spreadsheet and call it “Tree Surnames”.
Enter a header row with these columns:
- Column A: Surname
- Column B: Census Rank
- Column C: Census Total
Step 5: Copy Your Surname List
You now need to copy your list of surnames into column A of this new tab.
Step 6: Formula To Get Surname Rankings For Your List
The reference data has the rank for each surname. Note that the rank is from low to high. Smith is the #1 ranking. So, the higher the number, the rarer the name.
For each surname in your list, you want a formula that finds the entry in the reference data tab (app_c) and copy the rank details.
Our formula is based around the VLOOKUP function to find the entry, but we’ve added some complexity to make your data clearer.
Enter this formula into cell B2:
=IFNA(VLOOKUP(A2, ‘app_c’!A:B, 2, FALSE), 160000)
Then, copy the formula down for every surname.
Formula breakdown
The way to read formulas is from in to out.
The piece “app_c!A:B, 2” is the range of cells on the “app_c” tab where column A contains the lookup values and column B contains the data you want to grab (the rank here).
The VLOOKUP formula takes the value in cell A2 and searches the target range for it.
The IFNA() function kicks in when the lookup can’t find a surname. This means that there weren’t even a hundred people in the census with that name.
Why do we provide a default of 160,000?
Every surname with just a hundred people shares a ranking of 150,436. When the lookup can’t find a name at all, we’ve set the rank to be 160,000.
Step 6: Formula To Get Surname Totals For Your List
The reference data also has the total number of people in the census for each surname. I find that a little easier to visualize and work with than the rankings.
For each surname in your list, you want a formula to find the entry in the reference data tab (app_c) and copy the total details.
Enter this formula into cell C2:
=IFNA(=VLOOKUP(A2, app_c!A:C, 3, FALSE)), 99)
Then, copy this formula down for every surname.
What happens if the surname isn’t found?
Like with the ranking, we use the IFNA function.
If the surname isn’t found, we put in the value of 99. Of course, we don’t know if its 1 person, 50 people, or 99. You may choose to put in a zero or some other indication.
