Using Excel to manage searches

I used to think that the mistake I was making with my research was that I was conducting searches that I had already undertaken. I was wrong.

In fact, duplicating your search is a good strategy, in the right circumstances. If you repeat a search you may discover sources that were not available last time you checked. You may also get improved results because your knowledge about the family or about the sources has improved, or because there are new search tools available.

No, the biggest problem with the way most of us search is that we are not systematic enough and we do not make decent notes about what we have searched.

An Excel search plan solves both of these problems and it gathers information that you can then use to create your source citation.

I do not use a search plan all the time, but there are situations where it can be useful. For example, if I am having a lot of trouble finding someone or something, or if there is conflicting evidence and a systematic approach is required to resolve the conflict. A search plan can also be useful if you are researching a location which is unfamiliar to you and you want to be sure you do not miss any important collections.

Step 1 Create your table

A good search plan documents: when, where, what and how. This information can then inform subsequent searches.

Create these columns and format them:

  1. Name
  2. Objective
  3. Date
  4. Repository
  5. Collection
  6. Criteria – date range
  7. Criteria – surname
  8. Criteria – location
  9. Criteria – any other details


Objective – what information are you looking for? Having a specific objective helps you target your search. It determines where you look and what search criteria you use.

Date – the date you searched

Repository – this could be online or offline, e.g. Ancestry or NSW State Library

Collection –repositories gather sources into collections or databases. Which one did you search?

Criteria – which search terms and date ranges did you try?

Eight suggested columns in Excel for a table to manage searches when researching family history
Add your columns, then adjust the column width appropriately

Step 2 Add filters

The main benefit of creating your search plan in Excel is that you can add filters. This helps you manage the data and plan new searches based on what you have already done.

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.

Two columns of an Excel spreadsheet illustrating drop-down arrows used in filtering data
Add filter buttons

You can use the drop-down arrows to filter the data using any column. You could, for example, filter the plan to show all searches conducted for a particular objective or for a particular repository and/or collection. Do this before you decide on your next search.

Step 3 Create checklists

You often need to conduct similar searches for different people, because they have something in common. You can systematise these searches by creating checklists for different categories.

For example, you might make a checklist of collections to search for everyone who lived in London or a checklist of collections to search for everyone who was transported to Australia as a convict.

Create a new file and then start a new worksheet for each category and save the file. I call mine Search Checklists. Whenever you have a search plan that relates to one of your categories, copy the worksheet for that category from the Search Checklists file to the search plan file for that person.

To copy a worksheet, right click the tab for that worksheet and select Move or Copy, tick the box that says Create a Copy and select the file that you want to copy it to.

If you search the internet you may find that someone has already created a checklist for your category, or has a guide that you can use to create your own. For example, I used the NSW State Archives Convict Guide and the Australia-Convicts page on the FamilySearch Wiki to create this example checklist for convict research.

Extract of an Excel checklist for researching Australian convicts in family history
Extract from an example checklist for convict research

Please note that this is just an example checklist for convicts for demonstration purposes. It is not an exhaustive list of repositories or collections for convict research.

The purpose of a checklist is to suggest collections for searching. You still need to add the actual searches to your search plan.

1 thought on “Using Excel to manage searches”

Leave a Reply

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

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