Search the blog

Tuesday, 24 June 2014

Tuesday's Tip - How I use.... Spreadsheets for Genealogy

As an accountant I have spent many years using spreadsheets so as a genealogist I haven't changed very much as I use Microsoft Excel all the time. This week I've decide to share a few of the ways I use Spreadsheets for Genealogy

Spreadsheets are more than just a way to add up a column of numbers. They can be used for a wealth of other purposes from simple lists to complex databases.

Anyone who worked with me will know that I spent many years complaining that people used spreadsheets for everything and they shouldn't always try to shoehorn all problems into a spreadsheet. My main reason for this was that spreadsheets do not have inherent controls. Unless you build in controls and checks to a spreadsheet you are likely to double up on things or make mistakes in calculations. Another problem I have with spreadsheets is that there is no universal process associated with them - everyone has their own way of using a spreadsheet which can make it difficult for someone else to understand it. Both of these points can be addressed within the spreadsheet but it's always good to remember them when working with spreadsheet software.

There are many types of spreadsheet software with the most popular probably being Microsoft Excel. It's definitely the one that I'm the most familiar with so I will be referencing it in this blog post. Other spreadsheet programmes include Quicksheet and Numbers.

My main use is to keep track of internet searches.

I have a main website index where I add links to databases as I find them and try to categorise them geographically and by subject. This is really useful if I find a new webpage but I don't have time to investigate fully.

Behind this I have started to keep track of searches on each individual database on an individual sheet per database.

I keep a Research Log in Excel for each couple (and familym that I am researching.  The database log cross references the research log for a particular family where I find valid results.

My research log also includes sheets for the husband and the wife to record their life events. I usually have a summary page at the front listing any files I have on the couple. I have a sheet full of source citation templates so that I can be consistent with my citations and filenames.

If I am using a timeline I like to create them in Excel - I wrote a post on this back at the beginning of the year as part of the 52Ancestors series..

I use Excel to create data uploads to Custodian, to keep track of the Bona Vacantia list, figure out workflows for processes, keep track of finances for the business, plan out training, submitting data to online indexes and all sorts of other purposes.

I try to always keep some sort of notes page on the front which explains what each sheet in the spreadsheet is for - this means if I send it on to someone else then they have a chance of understanding it.

On Pinterest I have a Genealogy and Spreadsheets board where I pin tips for people to follow and relevant articles or blog posts.

Do you use spreadsheets for Genealogy? Are there any spreadsheet functions you'd like to use but don't know how? Is there anything you'd like to see me write a Tuesday's Tips blogpost on? Please leave a comment or get in touch to let me know.

If you've enjoyed reading this article you can tweet about it or why not subscribe to the blog and keep up to date with all the latest news from Full Circle Family History.


  1. I would love to know how you made the spreadsheets!

  2. Hi Kristie
    Thanks for your comment - I'll add it to my list of blog post ideas for the future. If there's anything else you can think of then let me know.
    Thanks for stopping by the blog

  3. Jo. I use a software database, Legacy, to be exact. It does pretty much everything you mention here, with much less work and a lot more power. What is the advantage to using a spreadsheet vs a database? I want to be sure I'm not missing something. I use spreadsheets in my personal life and have even used them when I'm traveling to an ancestors home state. I used them specifically for keeping addresses of cemeteries, their addresses, hours etc. Going forward I will use Evernote for those purposes. I like your blog and am going to read more of your posts. Thanks for sharing with all of us.

  4. Hi Diane,
    I use Family Historian as my genealogy database including sources etc.
    I use Excel more for lists and notes when I don't have access to my database (e.g. I'm on my ipad) or when I'm not sure how the person/family fit into my database. It's handy for recording when I don't find anything which is sometimes more important than when I do make a discovery! Just a small note that I've seen a new source of information and I've had a quick search for Tillin but found nothing can save me time in the future.
    I find that I can tailor the spreadsheets to suit a family or the way I want to analyse the family which gives me more flexibility.
    I do use Onenote but don't have a definite process organised for that yet. Let me know how you get on with Evernote.
    The blog's a bit quiet at the moment but hoping to ramp the posts back up when the school summer holidays are over
    Thanks for visiting the blog!

  5. I use a spreadsheet to keep track of information that I have and what I still need to find. It is just a quick reference guide for me. I keep track of which census's I have, specific birth and marriage and death information and cemetery information. It is a quick what to I have and what I still need to track down for each person in my tree.

  6. We use spreadsheets to sort out same name, same area persons. For example: there are 12 Benjamin Clarks in the same time frame, with very similar records. By putting down the records, by date, by location, who they married, list of children, where the children were born, etc. etc., we have been able to separate out the individual Benjamin Clarks & their families. We've had to add this to our tree, because everyone wants our sources to be their Benjamin.

  7. Hi Jo, are these spreadsheets available to be downloaded from anywhere please? Thank you in advance, Deb