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.

3 thoughts on “How to create a research plan in Excel”

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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