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.
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.
Step 3 Add your family information
Enter your family group names or individuals, their unique identifier numbers and their 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.
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.