Recently, the DNAsleuth shared a new way to visualize 7 family generations on one Excel spreadsheet and gave several examples of how it could be used.

I love this idea and I immediately had my techie person (hubby Dave) create a template for me.

I decided to color code my ancestors’ places of birth, which was the first example provided by DNAsleuth. It is a fabulous way to visualize data and, although I knew I was a great example of the American melting pot, this table proved it.

My ancestry is 50% Slovak, which accurately reflects my father’s side of the family, including the empty boxes representing the non-existence of records before the early 1800s in my great grandparents’ villages.

The red boxes represent those ancestors for whom birthplaces are unknown. Robert and Catherine (MNU) Carlisle, my most annoying brick walls, left no records hinting as to their origins. They were “Loyalists,” but Robert’s military service was at Fort Cumberland in Canada. I’ve found no viable clues as to Catherine’s origins. She may have come in the flotilla of ships from New York to Parrtown, New Brunswick, or could have been from a pre-Loyalist family. Walter Stewart was a Loyalist from New York, but have no idea who his parents were and if he was born in New York or somewhere in the U.K. Same situation for his wife, Sarah (MNU).

My colonial American roots are somewhat hidden in this chart because of the Canadian ancestors, who are noted in orange. All of these families were Loyalist lines that fled the U.S. in 1783, but most of whom had been living in the colonies for generations.

My Danish ancestors are coded in green and represent my great grandmother’s family. My Swedish ancestors are in light blue.

Philip Crouse represents my one Dutch line. He was from Zeeland, Netherlands and, from family lore, was born there (c1760), emigrating to the colonies as a young boy. The family undoubtedly came in through Philadelphia, but by the time of the American Revolution, Philip was living in North Carolina. I’ve never found any clues as to the names of his parents, but it is said he had some brothers who remained in North Carolina after the war.

Philip didn’t marry Sarah Burt until he settled in Canada – she was the daughter of Loyalist Benjamin Burt from Connecticut – and I would love to know why he felt so strongly about supporting the king. He was a recent immigrant and a very young man, of Dutch origin and left brothers behind to move to Canada. It is unusual and an atypical scenario for a Loyalist.

I loved creating this visual picture of my ancestry and will definitely be using the template to try out other data sets.

Thank you to DNAsleuth for this idea! 🙂

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.

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.

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:

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.

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. There is even a Guild of One-Name Studies 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.

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.