Using Excel to plan family history helps you take a more systematic approach, conduct more comprehensive research and produce a more accurate family tree.
In another blog post, Are your tree roots strong enough?, I demonstrated the basic methodology for a Tree Health Assessment (THA) using tables and a family tree chart. The Tree Health Assessment Tool helps you analyse the amount of evidence that you have for each identity and relationship on your direct line in your family tree. This gives you some indication of the likely accuracy of the core of your family tree.
Using Excel to plan family history helps you take this analysis further. The table format allows you to conduct a more in-depth analysis of the evidence you have gathered and examine more than just your direct line.
Using Excel in this way helps you identify gaps in your research and develop research leads to include in your research plan.
What columns do you need?
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.
Add additional columns for unique identifier numbers
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.
Explanation:
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.
Add your family information to the table
Enter your family group names or individuals, their unique identifier numbers and their generations.
Analyse the data in your table
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.
Use the analysis to update your family history research plan
The final step in using Excel in planning family history is to use the analysis to identify:
- new research aims and objectives
- new research questions and hypotheses
- new research tasks.
You should also use it to prioritise further research. I would suggest focusing on one line at a time and try getting all the cells to go green. However, you can adjust this approach depending on your own research aims.
More information
For more of my articles about using Excel for family history, go to the Using Excel page.
For more articles and information about this tool, head to the Tree Health Assessment Tool page. You can also download your free copy of the THA Tool guide on my Free Stuff page.
For more of my articles about analysing sources, go to the Analyse page or Chapter 4 of my book, The Good Genealogist.
Post last updated 15 May 2025
