The most important spreadsheet for the genetic genealogist, IMO, has nothing to do DNA or segments – it’s an Ancestor Spreadsheet. A simple spreadsheet of Ancestors is a very valuable tool.

This spreadsheet starts off very simple and can expand any way you want. Here are the column headers:

Ahnen – Ahnentafel number (this is a key to sorting and will help you in other ways, too)

L Name – Surname

F Name – Given name(s)

B Date – Birth date (pick a standard format and stick with it)

B yr – Birth year

B Place – Birthplace

B St – Birth state

B Co – Birth country

D Date – Death date

D yr – Death year

D St – Death state

D Co – Death country

D cause – Cause of death

M Date – Marriage date

M yr – Marriage year

M Place – Place of marriage

M St – Marriage state

M Co – Marriage country

I Date – Immigration date

Remarks – Add anything you want

D age – Age at death [D yr minus B yr] [also serves as a Quality check]

M age – Age at marriage [M yr minus B yr] [also serves as a Quality check]

Ch – number of children

Rel – religion

Prof – Profession

Mil – highest level

War – RevWar, 1812, CivWar, etc.

Y-DNA [halplgroup of all male line]

mtDNA [haplogroup of all female line]

FG – Find-a-Grave [I paste in the hyperlink for easy access]

LDS – The FamilySearch ID# for the Ancestor [this may change from time to time]

For the spreadsheet header, just type in the above list (to the left of the hyphen) across the top row.

I started with a Tree at Ancestrycom that was mostly just my Ancestors – Click on Tree search (top right); then select ‘List of all people”; then highlight that list and paste it into a spreadsheet. There is still some amount of manipulation, but I had a good base to start. Or you could just type the spreadsheet from scratch. Or perhaps manipulation from a GEDcom or other Tree software.

Here are some benefits and things to do with this Ancestor Spreadsheet:

1. First – this is your personal spreadsheet – modify it any way you want – you can delete any columns you don’t want and/or add any new columns for data you want. You can hide any column(s), so that only the information you tend to use all the time is shown (you can unhide at any time).

2. This is a very handy inventory of your Ancestors – a printout will only be a few pages.

3. This is an easy repository of key data elements of your Ancestors

4. This is my go-to lookup table for Ahnentafel numbers

5. A searchable database

6. A variety of spreadsheet sorts:

                A. Ahnentafel sort – groups husband and wife together and by generation

                B. Surname + B yr – groups surname lines in chron order => surname lineage

                C. B St – groups Ancestors by states; sort on “B county”, if you add that column.

7. I include Potential Ancestors, but highlight them as Potential.

8. I include “Alternate” Ancestors (also highlighted), when there is an unresolved alternative.

9. Blank spaces can highlight data you don’t have – they are a good tickler for what you need to research. Periodically, I’ll select one to tackle.

10. Add an additional row for each generation – this is a “separator” between generations; Example of the text in a Generation row:  32 GEN 6; 3xG grandparents; 4th cousin Matches [where the 32 is in the Ahnen column.

11. I select a some columns that will fit on one page, and hide the rest, and print out about 4 pages (the top Ancestors) which I always have handy – particularly while traveling.  I refer to this spreadsheet literally every day – so, IMO, it’s worth the work it takes to prepare it.

12. Please post any additional uses you find for this Ancestor Spreadsheet.

Here is an example of part of my Ancestor Spreadsheet:

Figure 1:

NOTES

1. This is just a sample; some columns have been hidden or reduced in width to get a lot in the picture.

2. This Ancestor Spreadsheet is not intended to replace my on-line Tree(s) which are full of documentation. This has key data, and an Ahnentafel place holder for every Ancestor. Ancestors in my Tree more than once are in this spreadsheet more than once – it helps to understand Pedigree collapse.

[35BA] Segment-ology: Ancestor Spreadsheet by Jim Bartlett 20211222 [Edited 20211223]