I created an ancestor table in Excel for quick reference but I can’t leave well-enough alone so it now has several tabs to help me look at my ancestors in different ways.

The worksheet is organized into tabs (below) that I adjust and add to as I learn more about keeping track of this information. Any cell with a gold background is a calculation so don’t change it unless you know what you’re doing.

How to use this template

1. Download the Excel template file (updated: 16 May 2021).
2. Complete the Ancestor Table.
3. Check the Pedigree Chart to make sure the immediate ancestors are correct. I find this check helps make sure I filled in everything in the ancestor table.
4. Check your Surname Table and Research Count to see where you should spend more time.
5. Add DNA Matches as you confirm their relationship to the subject person in the ancestor table (#1).

The Template

Ancestor Table

The ancestor table, also called an ahnentafel, is a list of direct ancestors for any person you’re researching. You can learn more about creating and managing an ancestor table from this NEHGS video.

The father and mother numbers for any person are automatically calculated in the right-most columns to help you get a more remote ancestor in the right row. A person’s child is also calculated.

Pedigree Chart

The pedigree chart does not require any data entry. It automatically pulls names and birth & death years from the ancestor table.

Surname Table

I found the surname table does not require any data entry; it pulls all data from the ancestor table. This video, from Family History Fanatics by Devon Lee, explains how to find new surnames from each generation into a simple table. The idea is to find where you are missing new branches.

Research Count

The research count does not require any data entry; it pulls all data from the ancestor table. This is another way to see where you need to focus your research due to ancestors you haven’t found and added to your ancestor table yet. I learned about this approach from Crista Cowan in her Family History Done? What’s Your Number? video. Crista also wrote a blog post with the same title.

Metrics

I like to count things so I have a general statistics tab to count locations, oldest, youngest, etc. You will need to change the location names to match the location names you enter into the ancestor table. I use the country or state only so the text isn’t too long.

If you need to add more rows for additional locations, be sure to duplicate the formula.

DNA Matches

This tab only pulls the name of person #1 from the ancestor table. I add confirmed matches only on this tab. You can take the data from your testing service – 23 and me, Ancestry DNA, Family Tree DNA (FTDNA), My Heritage, etc. I also have a column for the GEDMatch.com kit.

You can add or remove columns to fit your research needs. This is the newest tab so I’m still tinkering with it.

You will need to adjust the conditional formatting on the BRANCH column. I enter my grandparent’s surname in this column to quickly show how the person is related to me. The names are color-coded so you need to change the conditional formatting in Excel to match your surnames.

That’s it for now. Let me know if you have any questions or ideas for updates to the template. I really hope this helps.

Update: 16 May 2021 – Added 5x generation to the Ancestor Table tab. Still need to work out the surname tab update.