Category Archives: Excel for Genealogy

Using Excel to Manage Your Genealogy Research

Microsoft Office has been around forever. So long that I think we use the various programs found in it so much that we overlook them as a genealogy tool.

About 15 years ago, I actually typed my 149 page book about my husband’s Williams book by hand from scratch using Microsoft Word.

Excel is another great program that has many uses for genealogy, but I think most people rely on straight lists, whether kept on paper, in Evernote or somewhere else.

If you have never looked at Excel, here is what a new “book” would look like:

ExcelCrop1
Getting Started with Excel

This isn’t going to be an Excel tutorial, but for those new to this program, each of the column and rows can be lengthened and enlarged. Excel can sort by each column and within a series of columns. That is just the beginning!

What are some ways that Excel can be used for genealogy?

  1. Research log – Remember in the old days when we used paper research logs? This one is still available at the Family History Library and local family history centers. I used it for many years.

ResearchlogtemplateCrop
FHL Research Log

It still does the trick, but if a researcher wants to move to a computer- or cloud-based technology, Excel fits the bill. Columns can be set up for each of the categories found on the paper log. An Excel page could look something like this:

LogCrop1
Excel Research Log Example

The great thing about Excel is that you can enter names as you work (I suggest “last name, first name” format for later sorting) and then save and sort when you have finished a research session.

Many researchers don’t keep any research log at all. How many times should you have to search in the same place and in the same source for a birth record for John Adams? Answer: Once. However, if you are now looking for a birth record, say in the same town where John Adams was born, but you are looking for his sister’s birth, the research log will tell you right off if you have already looked there.

Research logs are probably the single best use of Excel for genealogical purposes, but they certainly aren’t the only use.

2. Particularly for those beginning to work on family history, Excel can be used to catalog family photos. Each photo, whether paper or digital, can be assigned a number and entered into an image database. A researcher can describe the photo, its provenance (from where it was obtained), approximate date of the photo, contact information from the person who shared the photo (or website where it was found), who is in the picture and, if known, occasion for the photo. For more sophisticated users, tags and metadata can also be entered.

CharlesAdams2  LuluAdams1  ThomasAdams
Charles, Lulu and Thomas Adams

What happens when someone has amassed hundreds or thousands of images? Excel can easily handle that number. With a naming plan (family surnames, etc.), the database can be sorted and searched.

3. One name studies or one place studies – I have a number of unique surnames in my family tree. I have considered doing a one name study and have actually written an article or two on the descendants of the earliest known ancestor with that surname.

GuildOneNameStudiesCrop
Guild of One-Name Studies

There is even a guild to help you get started on your one-name study!

I have a friend who created a database of all known residents of a tiny village in Europe. He can readily connect queries to information about each person who lived there.

Entering the individuals into an Excel database would make finding a particular person simple.

4. Tracking stories, articles or blog posts – I set up an Excel database for each blog post I’ve written, divided into years. I track the posting date, the post title and sometimes notes. I haven’t ever bothered to paste the link into the entry, but I certainly could do that. If you share family stories privately, Excel can be used to track which story you’ve told and with whom it has been shared.

PostExcelCrop
January Posts in Excel

These are just a handful of suggestions for using Excel in your family history research. Any project that needs multiple entries, particularly in large numbers and that requires sorting and shifting of data views is perfect for Excel.