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:
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]
I’m curious how you handle uncertain ancestors in this spreadsheet (and in your underlying tree). I have a few examples where there are two (or more) potential parents that are credible. Do you include these? Do you indicate confidence?
I use this *primarily* for known/confirmed Ancestors. For me it’s a crib sheet of what I know – a reference sheet, rather than a research tool. However, I do have several instances where there is a long time debate over the Ancestor – and I want to list list both (as a reference). So I use the same Ahnentafel number in a row for each of them. and I indicate in a separate column “Alt”; and I can add any notes I want in a Comment or Remarks Column. And, FYI, I also indicate “Dup” in a separate column for any Ancestor who is duplicated…. Jim
Do you have an example I can see?
I’ve updated the post to include a picture of part of my Ancestor Spreadsheet. Jim
LikeLiked by 1 person
Do you have a link to an example?
Janice, I’ve updated the post to include a picture of part of my Ancestor Spreadsheet. Jim
LikeLiked by 1 person
What do you do with pedigree collapse? I have multiple instances on my father’s side.
I repeat the Ancestor. This is one of the key features of an Ancestor Spreadsheet – our biological ancestry doubles at each generation going back – using all the Ahnentafel numbers – so I make sure an Ancestor is listed for each Ahnentafel number. Sometimes these duplicate Ancestors are on different generations, sometimes with different spouses – and Ancestor Spreadsheet helps keep track of it all. Jim
LikeLiked by 1 person
I agree …. I have been using an “ancestor spreadsheet” for many years and it is my “go-to” tool for recording, organizing and analyzing genealogy data. In addition to the features and functions that you mention, I use colour-coding which allows me to sort by colour for family constellations plus shades of the same colour to distinguish generations within families. I try to enter most data using cut and paste from online sources (i.e. search results) and then reformat dates as YYYY.MM.DD (using a formula) so that the spreadsheet can be sorted by dates. As you say, including the link to the online source for each line of data is also very handy.
Thanks for all the knowledge that you have shared over the years!
Saanichton, BC, Canada
LikeLiked by 1 person
Gary, Congratulations on forming your own Ancestor Spreadsheet, and thanks for the kind feedback. In my case I decided to not try to collect everything about an Ancestor, just the inventory and some key data. Most of the documentation is in my on-line Tree. The benefit of an on-line Tree is that many people can see it, and it draws out more hints and links. At one point I included columns for the census years, not to enter data, but to check off that I had found each Ancestor in each census year they were alive. Like you, I like to format data in my spreadsheets to facilitate sorting. Jim