Spreadsheet Magic - Importing Data From Ancestry.com

Did you know you can import results from an Ancestry.com search directly into an Excel spreadsheet?  Think of the many ways you could use this technique to create a data set and then manipulate it to see patterns in the data.  A spreadsheet could be a great tool to study and correlate you ancestor's FAN club - their Friends, Associates and Neighbors.

As an example, perhaps you only know only that you ancestors came from "Ireland."  Wouldn't it be nice to be able to narrow that down a bit?  Wherever they decided to settle, they didn't do it in a vacuum.  They settled there because they knew someone there.  If you can figure who the other Irish immigrants were and where they were from, you just might find some clues as to where you ancestor hailed from.  

Creating a database of people sharing a common place of origin with your ancestor, and then manipulating that database by adding other information to it might just help you to solve your location-of-origin problem.  But typing in the names, birth years and other information for every individual with this kind of a common characteristic can be a slow and tedious process.  Imagine if there were a quicker way...  

There is!  You can import data from a web search,  Here's a step-by-step example showing how to import from a Ancestry.com into an Excel spreadsheet.

Step 1 – Do a search on Ancestry.com, for example a search of all the people born in Ireland living in Hillsborough, Somerset County, New Jersey in the 1860 census. Copy the URL from your search.  (Note: it is best to get the maximum number of results per page, so set it to display 50)  Ctrl+C to copy. 

Step 2 – If it is a long URL, convert to tinyurl at http://tinyurl.com/ Click on “copy to clipboard” (just beneath the tiny URL that was created).  You need to do this step because the URLs for Ancestry searches are really long

Step 3 – In the spreadsheet where you want to place your data, go to the Data tab and click on the “From Web” icon.  A New Web Query window will open up.

Step 4  - Put your cursor in the Address box at the very top of this window and do Ctrl+V to paste the tinyURL in the box.  Click the “Go” button to the right of the Address bar.  The first page of your Ancestry results will appear. 

Note – if this doesn’t work, you may need to sign in again to Ancestry within the Ancestry window in Excel (confusing, I know, but I think you’ll get it when you’re working through doing this.  And if you're not signed in to Ancestry in Excel, sometimes all the date (like "city" and "gender" won't transfer)

Step 5 – At the top left of the search page image (just above and to the left of the i in the picture above), there is a yellow arrow.  Click that and it will turn to a green check.  Then click “Import” in the lower right corner of the window.

Step 6 – A pop-up will appear asking you where you want the data.  $A$1 is the default and usually that is what you want  Click OK.
 
It will paste a bunch of extra stuff on your worksheet, including rows that you will probably eventually want to delete, but your data will be in a columnar format that you can deal with.  To delete the rows above your data range, click on the row immediately above your data range, Shift+Ctrl+Home and it will highlight all the rows above your data range.  Then on the home tab, click the Delete triangle, then DeleteSheetRows and it will eliminate all those rows.
 
For subsequent pages of data from Ancestry, on your Ancestry query go to the subsequent page of results, create a new tinyURL for that page of results per Step 2 above, and follow the same process. (You might need to have your cursor in a column to the right of your data area to start the process, but you can specify that you want to paste the data into $A$55 (or whatever cell where it won’t paste over your last import).

This technique is great for using with Ancestry.com but will also work with other websites.  You might find a database somewhere of all the probates or marriages in a county.  Wouldn't it be great to be able to pull the data for the surnames you're studying?  The process outlined above might be just the way to do that.

FamilySearch.org will also allow you to import data directly into a spreadsheet.  I'll cover that process in my Tuesday Tips post next week.  See you then!