Using Excel to improve the accuracy of your family tree

Improving the accuracy of your family history requires a systematic approach and Excel is particularly well suited to this.

In an earlier blog post, Are your roots strong enough, I demonstrated the basic methodology for a Tree Health Assessment (THA) using tables and a family tree chart. Excel can be used to take this further to conduct a more in depth analysis.

Step 1 Units of analysis

First you need to decide which units you want to use for analysis. These become the columns of your Excel table.

I use family groups as my first units, with one row per family group. I have a column for the male surname and another for the female surname. You could instead have one row per individual, but that would only be practical if you were focusing on just a portion of your tree.

The remaining units should be key events in a person’s life which are:

  • events that fairly common in your family, and
  • events about which it is important to get the details accurate.

The actual events will vary depending on your family history.

Create your table, with a column for each unit of analysis.

Twelve suggested columns in Excel for a table to assess the accuracy of your family tree
Example analysis units as columns

Step 2 Additional columns

To ensure that you do not mix up the family groups, add in extra columns with the unique identifier numbers.

To help analyse your tree systematically, you also need to add in a column for generations.


Each person in your family tree should have a unique identifier number to help your distinguish between people of the same name. If you use family history software, get the numbers from there.

Generations – You are 1, your parents 2, grandparents 3 and so on. The benefit of including this is demonstrated below.

RIN = unique identifier numbers in Legacy Family Tree software, which is what I use. Gen = Generation

Step 3 Add your family information

Enter your family group names or individuals, their unique identifier numbers and their generations.

The first in an Excel table assessing the accuracy of your family tree list the surnames and unique identifier numbers
Adding family groups, all generations.

Step 4 Conduct your analysis

Add filter buttons to the columns: Click in Cell A1, click on the Data Tab, then click on the Filter button. This adds a little drop-down arrow to each column.

You can use the drop-down arrows to filter the data using any column. Filter to show only generation 2.

For each column of analysis, examine your family tree and determine if:

  • you have sufficient information
  • you have sufficient source citations for the information
  • the information provides sufficient evidence

Colour the cell based on your analysis:

  • Green means you have sufficient information, citations and evidence
  • Yellow means something is insufficient – perhaps you are missing citations or you have only one source
  • Red means something is wrong – you doubt the information
  • White (no fill) means you have no information for that unit. If the unit is not applicable for a particular family, add N/A.

If you have DNA evidence to support your conclusion, you might like to make that cell dark green instead of light green.

Once all of generation 2 is green then you can change the filter and work on generation 3. When that is finished you move on to generation 4, and so on. This systematic approach helps to ensure that your roots are strong before you move on.

Example analysis of a family tree using Excel, with ten columns colour-shaded based on the outcome of the analysis
Example analysis, generations 3 and 4

Step 5 Update your research plan

Use this analysis to prioritise which parts of your family history need work. Focus on one line at a time and try getting all the cells to go green.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s