Are you juggling multiple research plans and struggling to see the big picture of your family history research? If you’ve been using separate plans for each family group or project, you might be missing opportunities to work more strategically.
A master genealogy research plan consolidates all your research into a single, comprehensive Excel workbook. This approach gives you an overview of your entire research landscape, helps you identify gaps and priorities, and makes it easier to plan your next steps efficiently.
In my article How to Create a Research Plan in Excel, I showed you how to develop individual research plans for each family group. That method works well, you might find you need a broader perspective to research strategically. A master plan addresses this need by bringing everything together in one place.
What is a Research Plan and What are the Benefits?
A genealogy research plan identifies:
- the questions that you want to answer
- the hypotheses you want to test
- the information that you need, and
- the tasks you need to undertake.
Use your research plan to track your progress, pick up where you left off, record the sources used and document a summary of your findings. A research plan supplements the main documentation in your family history software or online family tree.
Why Create a Master Plan?
Having multiple research plans worked for me for many years. But it became difficult to plan my family history research as a whole. I needed a way to get an overview of my entire research, identify gaps and priorities, and work strategically across all my family lines.
Previously, I developed various separate tools for these purposes: a research index, the Tree Health Assessment Tool, and a BDM index to track birth, death and marriage records. While these tools were helpful, they created their own management challenges.
The alternative approach is to combine these tools into a single master plan. Having one plan keeps all your information together. You’re less likely to lose documents or information, and you can work more strategically. It’s also easier to take your plan with you when you go out to research.
I currently use Notion for my master plan, but I want to demonstrate how you can create a master plan in Excel for those who prefer spreadsheets or don’t use Notion.
How to Structure Your Master Genealogy Research Plan
This master plan uses six worksheets, each with a specific function. Keeping related information together in one workbook aids planning and prevents information from getting scattered across multiple files.
Sheet 1: Research Index
The research index provides a summary of key research questions or status for each family group in your tree.
Excel columns are designed for filtering, so separate your data based on how you might want to filter it.
Column setup:
- Column A — Family. List family groups using the surnames of both spouses. Each row represents a different family group.
- Column B — Grandparent line. Assign each family group to one of your four grandparent lines. Use your grandparents’ surnames. Mine are: Everett (father’s father), Flanagan (father’s mother), Hend (mother’s father) and Rusten (mother’s mother).
- Column C — Generation. Assign each family group to their generation. You are generation 1, your parents are 2, your grandparents are 3, and so on (see sheet 6).
- Column D — ID. Add unique identifier numbers for the couples. I use the RIN from my family history software.
- Column E — Key research questions or status. Add the most important research question for that family group. You don’t need content for every family group. Update this regularly as your research progresses.
- Column F — Rating. Assign a complexity rating to the research required. This helps you choose which family to work on based on available time. See the key on Sheet 6.
Filtering example: To see all family groups on just one grandparent line, filter Column B for that grandparent’s surname.
Sheet 2: Evidence
This sheet stores the table format of your Tree Health Assessment. It provides a summary of evidence strength for key information or events for each family group.
This assessment helps you identify where your research foundation is strongest and where it needs reinforcement. For instructions on creating this assessment, see my article How to Apply the Family Tree Health Assessment Tool.
Sheet 3: Plan and Log
This is the main sheet containing all your research questions, hypotheses and tasks.
Column setup:
- Column A — Grandparent line. Use your four grandparents’ surnames.
- Column B — Generation. Assign each question, hypothesis or task to the relevant generation.
- Column C — ID. Add unique identifier numbers for the person(s) relevant to the question, hypothesis or task.
- Column D — Surname. Insert the surname of the person relevant to the question, hypothesis or task.
- Column E — Ref. no. Assign a unique number to each question, hypothesis or task. Tasks attach to either a question or hypothesis as sub-numbers. Add the first letter of the grandparent line to the number, and restart numbering for each grandparent line.
- Column F — Your research question, hypothesis or task. Write the question, hypothesis or task in this column.
- Column G — Category. Categorise as question, hypothesis or task.
- Column H — Status. Open (O) or Closed (C).
- Column I — Sources to examine & repositories
- Column J — Sources used & citation
- Column K — Notes
- Column L — Analysis
- Column M — Transcript or extract
If you wish, you can add more columns for information that might assist your research planning. For example, you could add a column for country and then filter all research for that location. Or add a column for subjects such as convicts or immigration to group related research topics together.
Sheet 4: Surnames
In my article Create a Surname Tree to Organise Your Family History, I showed how to create a tree providing an overview of surnames in your family tree and which line they belong to. This serves as a useful research tool.
On this sheet, either insert an image of your surname tree or create a table version instead.
Sheet 5: BDM Index
In my article Using Excel to Track Birth, Death and Marriage Records, I demonstrated a spreadsheet for tracking vital records you need and those you’ve already obtained. This index helps you plan and prioritise record purchases while ensuring you don’t waste money on duplicates.
Add this index to Sheet 5 of your master plan to keep all your planning tools in one place.
Sheet 6: Key
Use this sheet to list explanations, abbreviations and keys for your other sheets.
Research complexity ratings for Sheet 1:
- 1 Significant research question or problem
- 2 Needs research to progress further or relationships not proven
- 3 Needs research but only to tidy things up. Not essential to progress further
- 0 No work currently needed. All relationships sufficiently proven
- 4 Previous generations need to be proven first
Generation key:
- 1 me
- 2 parents
- 3 grandparents
- 4 great grandparents
- 5 2x great grandparents
- 6 3x great grandparents
- 7 4x great grandparents
- 8 5x great grandparents
- 9 6x great grandparents
- 10 7x great grandparents
- 11 8x great grandparents
Tree Health Assessment colours for Sheet 2:
- Green: Strong evidence
- Yellow: Some evidence, insufficient
- Red: No evidence or not researched
- Grey: Not applicable
Working Strategically With Your Master Plan
The power of a master plan lies in its ability to help you work strategically across your entire family history. Use the filtering features to do things such as:
- Focus on one grandparent line at a time
- Identify all open research tasks across your tree
- Find families with brick wall problems based on complexity ratings
- Plan research trips by filtering for specific locations or repositories.
The interconnected nature of the sheets means you can move between high-level planning (Sheet 1) and detailed research tracking (Sheet 3), while keeping supporting tools like your BDM index readily accessible.
Try a Master Genealogy Research Plan
A master genealogy research plan helps you coordinate your research and work more systematically. By consolidating your planning tools into a single Excel workbook, you gain the overview needed to make informed decisions about where to focus your research time and energy.
Start by setting up the six sheets outlined above, then gradually migrate your existing research questions and tasks into the master plan format. You don’t need to complete everything at once and you don’t need to include everyone who is in your family tree. Build your master plan incrementally as you work on different family lines.
For more articles and information about using Excel in family history, head to the Excel page. You can also download your free copies of my Excel guides on my Free Stuff page. A guide about this master plan (with examples) will be available for download soon.
About the Author
Danielle Lautrec is a genealogy educator, researcher, and author of The Good Genealogist. With qualifications in history, family history, and historical archaeology, she teaches for the Society of Australian Genealogists.
