Inferential Genealogy Study Group in 2nd Life–EXCEL

October 13, 2011

I was reminded that I haven’t talked about one of the tools that I have used for this process. Dr. Jones talked about it, and the 2nd Life Study Group talked about spreadsheets.

I happen to use EXCEL after working with it for a number of years.

Not sure how clear this will be for the Blog, so I will try to explain it.

First, in my Genealogy Program, I have it automatically assign a number to each person (PersonID). The number is unique for each person in the file. If a person is deleted or merged with another, that number will not be reused. It was very helpful during my clean up because I knew and can see the Persons ID. That is the first column

The next three columns are Last Name, First Name, Middle Name, as it appeared in the Census Records. Census records are reflected in the 5th column lasted as the Residence Fact. That fact has the Census Year, the State, the County / City and the jurisdiction within that city. Here we are talking about Baltimore Maryland. So, the next to the last column will reflect what is on that Census Record. The last column is the House Number of the Family Number. Using filtering and sorting this really helps show households for a given Census Year.

In this example each of the people with red boxes around 1900 Maryland Baltimore Independent City / Ward 13 House # 939 were folks living at that location.

In the example, based on the dates, two brothers, and a wife are in that household. There are others in the spreadsheet, but just showing the example.


To keep track of the Females in the file, I used the Yellow Hi-lite to remind me that this name is a female. I wanted to stand out, when doing searching in the Census.

If I don’t know a Persons Name, First or Last, I use 5 Underscores. It’s a visual indication that “I don’t yet know the persons name”. You can see that I don’t know the female (Elizabeth W) birth surname.

But, I know that she was married in 1875 to Charles Howard, the gentlemen listed above her name (clearly by accident). When she got married, I then knew to look for Elizabeth Howard in the 1880 Census. BUT, I wanted to keep in mind that I still didn’t have her birth Surname, so when a Female gets married, I move her birth surname to the column after her middle name or initial.

The Birth, Marriage, and Death Dates are important. So they are tracked, but using their complete full name for the Birth and Death information. Visually, it shows the start and end of a persons information, along with the Person’s ID.

I know that I don’t have to look for Elizabeth in the 1910 Census, because she died in 1906. But, I can also tell that I need to look at the 1910 census as the three people listed here have blanks in those census years.

As this was developing, I had been tracking everyone in the file. But at this stage of this file, keeping my goal in mind, I only focused on what I now know to be two brothers and their households.

Looking at the Census Records are one thing, but to be able to step back, using a spreadsheet, I have been able to see how these households “stuck together” over time. Then to be able to realize that the households were in the Same Block or just down the street, as was seen in the Maps that have been posted here.

Working with this spreadsheet has been very helpful in resolving some of the conflicting information that I ran into and resolve in the past couple of days.

Learning: Learn to use some basic sorting and filtering that is available in a spreadsheet.

PS: I was able to generate data from my genealogy program when I wanted to start using a Spreadsheet. I had not started to us it, until I went through the video that Dr. Jones made available to us, and the 2nd Life Study Group talked about it.

But like setting a goal for this project, it took a bit to figure out how to use this tool to my advantage. Not to try to track everyone, but to be focused on what the tool was going to be used for.


Inferential Genealogy Study Group in 2nd Life – Family Tracking

August 3, 2011

My friend and Genea-Blogger Dear MYRTLE had an interesting Blog recently.

Using custom report features of your genealogy program

In that blog entry, she had this question:

We are new to Genealogy and have and Family Tree Maker.
This is a question that I am sure must have been answered a million times but we have not been able to find a solution doing Google searches. We would like to use Excel to help sort out what we know and don’t know from the tree that we have built so far.

Is there any software that will download an existing gedcom file onto an Excel spreadsheet?

I thought, that is exactly what I am doing for this project. So, why not share it here.

Since I know very little about David Ridgely Howard, I wanted to see if the Census Records could help me build what his family might look like, using Census Records, since other records were not helping me identify his parents. The census would help build households that my subject was in in various census years. Hopefully, one of them would give clues to his parents. As census years progress, relationships could be established within the Household. So, I wanted to build an EXCEL file, as was indicated in Dr. Tom Jones’ discussion on Inferential Genealogy.

Since I use Family Tree Maker, Version 2011, and the question was the same, here is how I created my EXCEL file. Please keep in mind that I didn’t start out with this file, but I had data in my genealogy program file.

The key for me was to use a feature of Family Tree Maker and that is for the program to assign, with some user control, a PersonID. I did that, and each PersonID will begin with DRH (David Ridgely Howard), there is a reason for me to do that, which is not important (yet). So that I can see the PersonID in my file AND in EXCEL.

In Family Tree Maker I went to the Publish Workspace, Person Collection, and selected Custom Report.



This will allow me to select the information that I want in EXCEL.

When I brought up the Custom Report the first ICON when that screen opens is Items to Include. Clicking on that ICON will bring up this screen.


I included the Name, PersonID, Birth, Marriage, and Death FACTS. I wanted the EXCEL file to have the Name format to be Last Name, First Name, Middle Name. As that will be the first column in EXCEL.

Selecting name, in the above screen, then Name Options, I select Last, First, Middle to get the output of the Name in the format I want.

Here is what that report looks like in Family Tree Maker. My subject is in the red box. All I have at this point is his Name, the PersonID that Family Tree Maker generated for me, Birth and Death Dates.



That’s a good start and will help generate an EXCEL file so that I can track the Census Records. I select Include All in the Right Hand Panel in the Publish Workspace for the Custom Report. (won’t show all of the Family Tree Maker screen, as that can be a topic of my other blog)

With that report on my display, I click on Share, and select Export to CSV, a format that EXCEL can read and understand.


I want the data to appear in Columns, That is one row per person. So the first button, is selected.



Once exported, you are given the option to Open the file. Here it is in EXCEL. You can see David listed. So, my data is now in EXCEL to do what I want. Maybe not in the format that I want, but the main pieces of information is there.


I made some changes in EXCEL, like moving the PersonID column from the 2nd column to the first column. Normal Cut / Paste features in EXCEL were used. I did, but really didn’t have to, split up the Name from one column to three. Over time, I knew that the exact wording of the names would change over time.

For David, he is DRH001, I changed DRH1 to DRH001, because of the way EXCEL sorts. I wanted the FACTS to be in a column followed by the Date of that Event (or Fact), followed by the State, Place that it occurred and a description as provided by Family Tree Maker.

All of this was a little extra work, but the Sorting in EXCEL and Filtering that you can do with EXCEL it was worth the extra time to reformat what Family Tree Maker had provided.


I knew the Birth and Death Dates for David, so I entered those Facts with Dates. In Family Tree Maker when a Census Record is used, the Residence FACT is used to record where the person was in the Census. Above you can see that I have gathered the 1850, 1860, 1870, 1880, 1900, and 1910 Census, but haven’t found (yet) the 1920 Census. He died in 1927.

I added the Residence FACT dates If I hadn’t found them yet, so in looking at this spreadsheet I can tell I need to look for that.

Using the Sorting and Filtering capability of EXCEL I can see households over time. Who is in the household, who is not in the household, then later the Relationships.

I won’t post more about that, as what is really an EXCEL lesson or two, and that isn’t the purpose of this Blog.

I wanted to track females, over time, in Census Records. The first thing I did was to put a background color for them.


In this example, I do not know her birth Surname. For unknown names, I enter 5 underscores “_____”. I know her birth and death year AND when she got married. So, for the Census years before she was married, her surname has those 5 underscores. There are ways to find that information, but for this spreadsheet I wanted to be reminded visually that I need to look. Since she married a Howard, and I found her in the 1880 Census, just after her marriage, I changed her unknown surname to Howard. I know here death date, so I also have a visible reminder that I need to find her in those census records.

I thank Dear MYRLE for posting the question and I hope I have provided an answer to the question asked. I know that this spreadsheet, generated by Family Tree Maker has really helped me with this project.

%d bloggers like this: