Methodology

How should you document a broader search?

The FFANs principle neatly categorises the people in a broader search as family, friends, associates or neighbours. Family can be included in your family tree, but how should you document research into friends, associates and neighbours?

What information needs to be documented?

First we must consider the type of information that needs to be documented. We are researching FFANs because that research may provide information which contributes to the history of someone in our family.

The key information, therefore, is:

  • name of the FFAN
  • vital dates and locations (birth, marriage death)
  • name of the person in your family that they are connected to
  • nature of the connection
  • information obtained from researching the FFAN that is relevant to the person in your family
  • source citations for that information.

Research plans

Including FFANs in research plans helps you focus your research on specific research questions that are necessary to progress your research and identify which FFAN is most likely to provide the best information.

You could include FFANs in the research plan of the ancestor that they are connected to. For example, I create research plans in Excel and add extra worksheets for FFANs. One worksheet lists all potentially relevant FFANs, then worksheets are added for more detailed research about those which have the potential to yield useful information.

Alternatively, you could create separate research plans for each FFAN or group of FFANs.

Family trees and associated documentation

When you research family beyond your direct line, you should add them to your family tree as this identifies their relationship to you. Friends, associates and neighbours can also be included in your family tree as unlinked individuals. You can then add their families, if that information is relevant to your research.

Another option is to create separate family trees for different family lines and for friends, associates and neighbours. I personally do not like having multiple trees. Family history software and online family trees have tools to help you with your research and I find that these tools work better if the people are all in the same tree. Having them all in the one tree also keeps all the information together, which minimises the risk that you will lose information or forget about some of the people that you were planning to research.

You should also extend your organising system for associated documentation to include FFANs. For example, I organise all my files by paternal and maternal sides, then by surname. I store documentation about FFANs under the surname that they are connected to.

How do you link FFANs to your ancestors?

Regardless of which approach you take to research planning and family trees, you need a system to document who each FFAN belongs to.

Using unique identifier numbers greatly assists this process. Family history software automatically adds a unique identifier number to each person. If you do not use software, you can set up your own manual system. The numbers are used to distinguish between people of the same name and as a short reference to a person.

You could use your master list of research plans to provide cross-referencing between ancestors and their FFANs, as demonstrated below.

Example master list of research plans using unique identifier numbers to cross reference to FFANs.

Another option is to add information about the FFANs and their connections on the profile of the relevant ancestor. Ideally they should appear with the information about the shared connection. For example, I added each of the men who were executors of John Townson’s will to my family tree as unlinked individuals, and then I added their names and unique identifier numbers to the fact about the will on John’s profile. I can use this information to search my tree by their names or their identifier numbers. Adding information about their connection to John on the profiles of each FFAN also allows me to trace that the link back to John.

DNA matches

DNA matches can be treated as FFANs, because the DNA results suggest that they are probably part of your extended family. DNA matches should be added to your family tree, if you have sufficient information and they are relevant to your research.

If you know, or think you know, the most recent common ancestor between yourself and your DNA match, you could include that DNA match in the research plan of that common ancestor. You could also list the DNA matches on the profile of that ancestor.

If you have not yet identified the most recent common ancestor, you might find it useful to have a research plan for a group of DNA matches who appear to be related to you in the same way, or have a DNA research plan for each of your four grandparent lines. A single DNA research plan would also be feasible.

You might like to read a post I wrote a while back, Research Planning with DNA.

Case studies, Methodology

Finding answers in a broader search

Researching someone’s siblings and the witnesses on their birth, death or marriage records is often sufficient. However, in some cases you need to conduct a broader search.

I suggested in my previous post that you need a broader search if:

  • the sources reveal gaps or inconsistencies
  • the sources do not provide the necessary information to answer your research questions, or
  • you have tested your DNA.

One of the best examples I have of the need for a broader search is my five times great grandfather, Captain John Townson.

John Townson was a member of the NSW Corps and arrived in Australia on the Second Fleet in 1790. He had two daughters. I am descended from the second daughter, Sarah Griggs. He was first stationed at Rose Hill (near Parramatta), then on Norfolk Island. He lived for a while on land granted to him in Sydney, in the area now known as Tom Ugly’s Point, and later moved to Tasmania where he received further land grants.

The standard birth, death and marriage records yield little of any use, which is not unusual for such records at that time. Plus, he never married and so far his baptism record has not yet been found. Despite his participation in the early history of Sydney and Norfolk Island – he was Lieutenant Governor there for a few years – there are few sources that refer directly to him. And despite claims of some researchers that he was baptised in Yorkshire or Shropshire, the evidence does not support either.

John’s life story is full of gaps and inconsistencies. To conduct a reasonably exhaustive search and gather sufficient evidence to substantiate the details of events in his life, a broader search is required. Fortunately, John has a ton of family, friends, associates and neighbours.

Research goals, questions and hypotheses are important for all family history research, but they are particularly essential when you research beyond the direct line because you need to place some limit on the research and give yourself something to focus upon, or else your search becomes endless. In this case, my research goals for John are (i) to find evidence of the date and location of his birth, and (ii) to better understand his role in the early history of Sydney and Norfolk Island, and his potential involvement in the event that is usually referred to as the Rum Rebellion.

Missing birth information

Missing information about the birth of an individual is a good example of when you need to extend your search beyond your direct line. It is not enough to search for their birth and baptism, nor to research their parents. You need to research the entire family group. Researching siblings provides more information to help confirm whether or not you are searching in the right place and time, and whether you have correctly identified the mother.

A broader search of John’s family has revealed records such as wills, divorce records for his mother from her first husband, a baptism record for his older sister and business records for his father. Collectively these sources provide circumstantial evidence that John was born before May 1760, probably in London or Richmond (in Surrey). I live in hope that one day I will find his baptism record.

Historical events

My research into the historical context of John’s life is ongoing. I have a timeline of his life in an Excel spreadsheet, with columns for his friends, associates and neighbours. This helps me identify shared events or experiences and target sources about those who may provide a useful insight into his life.

Extract from my spreadsheet about John Townson’s FFANs in the NSW Corps

For example, John came to Australia on the Scarborough with John Macarthur, about whom much has been written. They both had strong connections with Parramatta and had many shared friends and associates. One shared associate was Captain John Piper, the man after whom Point Piper in Sydney is named. Townson and Piper were stationed at Norfolk Island at the same time, and Piper was the executor of Townson’s will. The NSW State Library has papers about Macarthur and Piper, waiting to be explored for references to Townson and insights into his life.

Another example is John’s participation in the event known as the Rum Rebellion. In 1808, a group of men, mostly members of the NSW Corps, mutinied and overthrew the Governor of NSW, William Bligh. After the rebellion, Bligh named John Townson as one of the conspirators, but his brother, Robert, was also involved and signed the petition against Bligh. Sources about this event are providing an insight into their motivation, which appears to have been about Bligh failing to honour land grants to them, and also other details of their lives.

My next post about researching beyond your direct line will discuss options for documenting the research.

A few sources:

NSW State Library, ‘From Terra Australis to Australia. The 1808 ‘Rum’ Rebellion’, (https://www.sl.nsw.gov.au/stories/terra-australis-australia/1808-rum-rebellion), accessed 28 May 2022.

Findmypast & British Library, British Newspaper Archive (www.britishnewspaperarchive.co.uk), Proclamation by William Bligh, March 1809; Cheltenham Chronicle, Thursday 11 January 1810.

Frederick Watson, Historical Records of Australia, Series 1 – Governors’ despatches to and from England  (N.p.: Library Committee of the Commonwealth Parliament., 1914), Governor Phillip to the Right Hon. W. W. Grenville. (Despatch No. 9, per store-ship Justinian, via China; acknowledged by Rt. Hon. Henry Dundas, 10th January, 1792.) p.193.

Featured photo: Searle, E. W & Beatties Studio, 1848, Norfolk Island convict settlement at Kingston in 1848, retrieved May 28, 2022, from http://nla.gov.au/nla.obj-142181355

Methodology

Using Excel with DNA test results

To get the most out of DNA testing in family history you need to test multiple people and upload your test results to multiple websites. This creates a lot of data and Excel can be used to store and manage this data in a single location.

Why I use Excel for DNA

Gather

Excel allows you to aggregate data from multiple DNA kits in one place, in one file. You can then compare the matches between kits and get a broader range of data – for example, my sister might have a DNA match that I do not have, but I can still use that information if I store it in Excel.

Patterns

The spreadsheet format of Excel makes it easier to see patterns in the data. These patterns provide clues to how your matches may be related to you and to each other. Before DNA clustering tools became available, I used to use Excel to discern clusters.

Customise

Third party tools are great for DNA analysis, but you are bound by the design and limitations of that tool. With Excel you can customise and design tables to meet your own specific needs.

Adaptable

You only have to enter the data into Excel once, then you can manipulate it in different ways depending on the question that you want to answer on any particular day.


The downside of using Excel for DNA is that most of the data has to be entered manually, which can be time consuming.

How I use Excel for DNA

My Excel spreadsheet serves three purposes. I use it to:

  • record information about DNA matches for all of the test kits that I manage in a single spreadsheet
  • analyse how the matches may be related to me and to other matches, and
  • plan my research to test hypotheses based on the DNA data.

Step 1 Worksheet 1 – the main worksheet

I began my worksheet by copying and pasting my test results from GEDmatch. If you are a Tier 1 member you can avoid the copy and paste by downloading your top matches as a CSV file. Alternatively, you could download your test results from your testing company as a CSV file, open it in Excel and then save it as an Excel file.

Extract of results copied from GEDmatch, privatised

Step 2 Finalise your columns

If you read my previous posts about using Excel you will know that columns are the units of analysis when using Excel for family history, so you should keep any columns that are relevant to your analysis and delete the rest. You may also need to split some columns into two – for example, I separate the surname of the DNA match into another column so that I can sort or filter using the surname.

Add new columns if needed and rename columns if necessary.

Here are the columns that I use:

Record:

  • Kit (GEDmatch kit no. or A for Ancestry, F for FTDNA etc.)
  • User name/Alias
  • Surname
  • Which kit (which of the kits I manage has this person as a DNA match)
  • Autosomal total cM
  • Longest segment
  • Estimated generations
  • Chromosome
  • Start point
  • End point
  • cM on Chromosome
  • SNPs
  • X DNA total cM
  • X DNA longest segment

Analysis and Plan:

  • Relationship (to the tester)
  • Analysis group
  • Side (Maternal or Paternal)
  • GP group (based on the Leeds Method analysis)
  • Confirmed common ancestor (Y or N)
  • MRCA (most recent common ancestor(s))
  • Location
  • Notes
  • Actions required
  • Contact email
Columns for the record function
Columns for the analysis and plan functions (adjust column sizes as needed)

Step 3 Enter data

Enter data in the columns about matches from other sites or other test kits. This will have to be done manually, so focus on matches greater than 40cM first, then add those greater than 20cM. Smaller matches can be added later if they are relevant to your analysis.

In most cases, each DNA match will have a single row. However, close matches will have multiple rows as they match you on more than one chromosome.

If you do add data from other test kits, a DNA may have multiple rows because they appear in different kits. You can sort the data by match name to identify these.

Ancestry does not provide chromosome data, so Ancestry kits will have some empty columns.


Step 4 Add filter buttons to each column

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.


Step 5 Fill in the Analysis columns

Analysis group – Create groups and assign matches to the groups based on your analysis of relationships between matches. This is just like the coloured dots in Ancestry but in Excel you are not limited to 24 groups. The groups you use are entirely up to you. 

Side (Maternal or Paternal) – If you have tested someone on your mother’s side or your father’s side, use this to designate a match as Maternal or Paternal.

GP group (based on the Leeds Method analysis) – I create a version of my family tree and assign letters and numbers to them, based on my four grandparents. See Worksheet 3 below. For an explanation of the Leeds Method, see below.

Confirmed common ancestor (Y or N) – Have you identified the ancestor that you and that DNA match have in common?

MRCA (most recent common ancestor(s)) – Name the ancestor or ancestral couple that you and that DNA match have in common


Step 6 Worksheet 2 – Group index

Prepare an index to your Analysis groups. I use seven columns as shown below.

Group index

Step 7 Worksheet 3 – Leeds Method Index

Prepare an index to your four grandparents and their direct ancestors, which is then used in the Leeds Method Analysis.

Four grandparents, their parents and their grandparents

Step 8 Worksheet 4 – Leeds Method Analysis

Record your Leeds Method analysis. You will need six columns as shown.

Example Leeds Method Analysis (privatised)

Using Excel for this analysis means that you can use the sort and filter functions to help you analyse the results.

Leeds Method Analysis, filtered for mother’s father’s line

Step 9 Transfer

After conducting your Leeds Method Analysis, transfer the results to your main worksheet, using the keys from the Leeds Method Index.

Add the group to column R, GP group

Step 10 Use your table

Other analyses that you can conduct on the main worksheet include:

  • who are my closest matches – sort by the column labelled Autosomal total cM, largest to smallest; or sort by the Estimated Generations column, smallest to largest
  • who matches me on a particular chromosome – filter by the column Chromosome, then sort by Start Point smallest to largest and look for overlapping segments
  • which kits are for people who may be related – sort by Contact Email to identify kits that are being managed by the same person (NB that does not necessarily mean that they are related, but it is possible)
  • Sort or filter by Location to get clues that may help determine how the match is related to you

Step 11

You can also include hyperlinks in the Notes column to websites such as the match page on the testing site, family trees or DNA painter; or to other files stored on your computer. On the Insert Table, click the Hyperlink Icon, add a link in the Address field or browse for a file on your computer.

Adding a hyperlink in the Notes column to an analysis document on my PC

Further information

Is DNA essential evidence for family historians? 

Leeds Method of DNA analysis

Download my previous articles on using Excel for DNA

Methodology

How to create a research plan in Excel

Research plans help you organise your research and focus on priorities, so that you can achieve more with your research. You could create a simple research plan in Word, but Excel offers more features to deal with the complexity of family history research and the huge volumes of data.

My first three posts about using Excel for family history contained simple examples to demonstrate the basic concepts and techniques. Now we move onto a more complex example, which you will be able to customise based on your own circumstances and needs.

Step 1 Choose your planning unit

To create a research plan you first need to choose the research planning unit. By that I mean – Are you going to produce a plan for:

  • each research question
  • each person
  • each family group
  • each surname, or
  • a project?

The example I have used here is for a family group, which is the unit that I find most useful in my own research.

Step 2 Set up your worksheets

You can have multiple worksheets in a spreadsheet and this is very useful for a research plan, because each worksheet can have a different function and format, but they are still kept within a single file.

For this demonstration we will use three worksheets:

  • a summary of information about the family
  • a plan and log, and
  • a search plan.

At the bottom of each worksheet there is a tab. Double click on Tab 1 and rename it Family Group. Use this sheet to summarise key information about the family, such as their names, name variants, birth date and location, marriage date and location, death date and location, and their literacy levels.

First page of a research plan created in Excel for genealogy, which summarises information about the family
Example of a Family Group worksheet

Double click on Tab 2 and rename it Research Plan. This is your main worksheet.

On worksheet no. 3, create a Search Plan for this family group (follow the instructions in my blog post Using Excel to Manage Searches) and rename the Tab.

Step 3 Set up your columns

A key benefit of creating a research plan in Excel is that you can include a lot of information in a single plan, then manipulate the information into manageable subsets using filters.

Where possible, set up columns on worksheet 2 (Research Plan) that can utilise the filter feature. To do this, you need to standardise how you enter data in the column – for example, Column I below has just two answers, Open or Closed.

Here are the columns that I use:

Column A – Research question or hypothesis. List all of your research questions and hypotheses for this family group.

Column B – Information needed. List the information needed to answer each question or hypothesis. You will need more than one row per question/hypothesis.

Column C – Source. List the sources you intend to search for the information. You should have more than one source for each piece of information.

Column D – Repository. List the repositories you intend to search for the sources. You may have more than one repository for each source.

Column E – Source citation. When you find the source, add the source citation here.

Column F – Analysis. Analyse the source and the information and summarise key points here.

Column G – Notes. Has the question has been satisfactorily answered, has the hypothesis been supported or refuted? Is more research required?

Column H – Tree updated. Update your family tree and mark it off here.

Column I – Status. This allows you to track whether the research task has been completed or whether it still needs work. Open = still being researched, Closed = research on this question or hypothesis is complete

Nine suggested columns for a research plan created in Excel for genealogy, including research question, source, citation, notes and status
Columns for the Research Plan worksheet. Adjust column widths as required.

Step 4 Add your data and filters

Start filling in the columns, then add filters.

To add filter buttons to each column: 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.

Example research plan created in Excel for genealogy, with data showing how to use the filters on columns
Data entered and filters added to each column. Columns E-G hidden in this example.

You can use the drop-down arrows to filter the data using any column. This is particularly useful if you end up with a large plan and you want to focus in on just a subset of the data for planning or printing purposes.

Here are just a couple of filtering examples:

Use a filter on Column A to show all tasks for a particular research question.
Columns E-G hidden in this example.
Use a filter on Column I to show just the tasks that are still open.
Columns E-G hidden in this example.

Methodology

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.

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.

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.