How To Use The Excel Family Tree Generator Template

The Excel Family Tree Generator Template uses a format that won’t suit many hobby genealogists.

It is designed to work downward from one pair of grandparents. The tree can include their children, grandchildren, and great-grandchildren.

The default format has only enough room to display first names. I’ll show you how to make the display work with first and last names.

If you want an alternative that works with four grandparents and/or has more generations, I’ll give some suggestions.

But I’ll also give you a step-by-step guide to get this tool working and avoid some bugs that I encountered.

An Example Tree Made With The Generator

Here is a tree that I made with the generator.

Video Companion

If you want a visual walkthrough of the generator, here you go. I cover the same points in this article.

YouTube player

How To Install Excel’s Family Tree Generator

This template doesn’t come bundled with recent versions of Microsoft Excel. But you can install it for free from within Excel.

Follow these steps.

  1. Open Excel and create a new workbook.
  2. Click the “File” menu.
  3. Click “More templates” at the top right of the screen.
  4. Enter “family tree generator” into the search box.
  5. Click on the template in the search results.
  6. Click on the Create button.

This search only returns one template so you’re in no danger of picking the wrong one!

How The Family Tree Generator Works

Don’t worry if you’re not sure what you’re looking at when the generator installs. It’s not obvious but is simple when explained.

The Create button creates a new Excel workbook called FamilyTreeGenerator.

There are two tabs in the workbook: “Family Members” and “Family Tree”.

Family Members tab

The first tab is where you enter names for the family tree. The template comes with an example set of names.

There are two command buttons at the top right of this worksheet.

The “Reset Family Members” clears out all the sample names. It will also clear out any names that you enter – so be cautious with its use!

The “Create Family Tree” button does the magic. It uses the names entered into this tab to automatically draw the family tree in the second tab.

Family Tree tab

It’s important to know that you don’t edit the tree in the “Family Tree” tab.

Instead, you make your additions and changes in the “Family Members” tab.

When you click the “Create Family Tree” in that tab, the tree is redrawn in this worksheet.

Before I give a run-through of the best way to use the generator, I’ll point out some of the limitations of the design.

Drawbacks Of The Generator

There are several limitations with the generator.

Only Two Grandparents In The Tree

The sample set-up and tree provided only has two grandparents: “Glenn” and “Vange”.

The generator doesn’t work with four grandparents.

I tested this by entering two people below Glenn and Vange. But the generator ignores the extra pair when creating the family tree.

Basically, the format was designed to work with one set of grandparents.

This Tree Counts Downwards From The Grandparents

The description for the generator mentions that it works with four generations. I initially thought that must mean it starts with myself and goes up to my great-grandparents.

But it is designed to work the other way round. You start with a set of grandparents and the generated tree can go down to their great-grandchildren.

If you’re used to entering yourself and working upward, this tree will have you, your parents, and one set of grandparents.

But you’ll also be able to add your own children. That may be just the format you want!

If you want last names, edit the tree template

The sample tree only uses first names.

I tried creating a tree with first and last names. Unless the two names are very short, this does not display well with the current template.

Here is the template with “Thomas Postlethwaite” and “Mary Ford”.

As you can see, Mary squeezes in but poor Thomas is truncated left and right.

My tip: the chosen font size for the name fields is set to 16. I found that reducing the font size for these cells to 10 ensured that the display showed both first and last names.

Depending on the typical length of names in your own family tree, you may need to drop it down to 8.

Your tree may not fit in a single printed page

The example tree I showed earlier will fit on a single printed landscape page.

However, the default tree with a new installation does not.

I found that if there are more than three siblings in the generation below the grandparents, then the tree spills into a second page.

Odd visual format

This family tree mixes a vertical and horizontal display. Here is a part of the sample tree that comes with a fresh installation:

This is showing that Walter married twice. His two wives are Jessica and Marie.

The names in the green boxes are all part of the lower generation, despite the vertical format. They are siblings and half-siblings.

I point this out because I initially thought that the vertical lines meant parent/child relationships.

Avoiding Crashes And Errors

When I started using the tool, I edited the existing names and relationships in the Family Members tab.

But when I clicked the “Create Family Tree” button, Excel crashed with an error.

My advice is to start from scratch by clearing out the sample. This is what I describe in the next section.

Grandparents

Pick one set of grandparents to work with. This tree represents their descendants.

Enter the grandfather’s first name in cell B10 and the grandmother’s first name in cell C10.

Their children

Enter the first name of their first child in cell E10. The first name of their spouse goes into F10.

The third field is a drop-down that lets you pick their parents. You are only going to see the entry for the two grandparents that you entered.

Enter the first name of their second child in cell E11. Once again, add a spouse and pick the grandparents in the adjacent cells.

Repeat downward for the number of children that the grandparents have.

Their grandchildren

Repeat the steps above for the next generation.

If you entered several siblings in the previous generations, you will have more options to choose from here in the “Parents” dropdown list of column K.

The children of their grandchildren

This is the final level.

The big difference here is that you can’t enter a spouse.

Why? I don’t know. This format doesn’t make much sense to me.

Simply enter a name and pick the parents of each person in this generation.

Creating the family tree

This step is easy.

Click the “Create Family Tree” button at the top right of the screen.

This action switches to the Family Tree tab. You should see the names and relationships you entered in the tree format.

Saving Your Generated Template

If you try to save the generator workbook as the default “.xlsx” extension, you will get a warning message.

This is because the workbook contains code.

Instead of using the default extension, you should save it with either of these two extensions:

  • Excel macro-enabled workbook (.xlsm)
  • Excel binary workbook (.xlsb)

You’ll then get a warning message about personal information and a mysterious entity called the Document Inspector. You can safely ignore this message.

Alternatives To The Family Tree Generator

I have several tutorials that walk you through building a tree in Excel that fits on a single landscape page. Each tutorial has an accompanying video if you prefer a visual guide.

These trees assume that you’re working from the bottom up. The lowest level is the first generation (possibly yourself), and the tree format shows all ancestors up to a certain number of generations.

The generator tree displays in the classic vertical format. The only alternative that I can fit on a single page in this format is the four-generation vertical family tree in Excel.

However, several more generations can fit on a single page when using the horizontal pedigree format that looks like this:

Sample five-generation tree (our template)

Take your pick from these tutorials:

Margaret created a family tree on a genealogy website in 2012. She purchased her first DNA kit in 2017. She created this website to share insights and how-to guides on DNA, genealogy, and family research.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.