Teaching kinship with a spreadsheet, one row per person

A family tree drawn on paper is easy to read and hard to question. Put the same family into a spreadsheet and you can ask it things. Who are this person's grandparents? Who are her cousins? What do you call the relationship between these two? Writing the formulas forces you to say exactly what "cousin" means, and that is the real lesson.

This piece builds such a spreadsheet step by step for an invented family of 12. It works in Google Sheets and in current Excel (Excel 2021, Excel 2024 or Microsoft 365). Two steps use FILTER, which older Excel versions lack, and those are flagged. Everything else relies on long-standing functions: INDEX, MATCH, COUNTIF and VLOOKUP.

The grandparent columns use kin-type letters: F father, M mother, B brother, Z sister, S son, D daughter, read left to right from the person in question. FM is the father's mother, MF the mother's father and MBD the mother's brother's daughter. The full notation is on our notation page.

The family

This family is hypothetical. Abe and Bea have two children, Dan and Eva. Dan has a daughter, Ivy, with Faye, and later a son, Jack, with Gwen. Eva and Hal have two children, Kate and Leo. Hal's mother, Cleo, is known; his father is not. Nobody recorded Faye's or Gwen's parents. Three generations, a pair of half siblings and one missing grandfather: enough to make the formulas earn their keep. It helps to sketch the diagram before typing anything, as in how to draw a kinship diagram.

The first tab, called People, holds the raw data in columns A to E, with headers in row 1 and people in rows 2 to 13:

Four rules keep the formulas honest:

1. One row per person, including everyone who married in.
2. Every person gets a short ID that starts with a letter. Names repeat and get misspelled; IDs do not, and the letter keeps the spreadsheet from treating them as numbers.
3. The Father and Mother columns hold IDs, never names.
4. Unknown means blank. Not a question mark, not "unknown". The formulas read a blank as "not recorded".

Step 1: grandparents

Add four columns headed FF, FM, MF and MM in F to I, and type these into row 2:

F2  =IFERROR(INDEX($D$2:$D$13,MATCH($D2,$A$2:$A$13,0))&"","")
G2  =IFERROR(INDEX($E$2:$E$13,MATCH($D2,$A$2:$A$13,0))&"","")
H2  =IFERROR(INDEX($D$2:$D$13,MATCH($E2,$A$2:$A$13,0))&"","")
I2  =IFERROR(INDEX($E$2:$E$13,MATCH($E2,$A$2:$A$13,0))&"","")

Then fill them down to row 13. Read F2 from the inside out. MATCH finds which row holds this person's father. INDEX then reads the Father column of that row, which gives the father's father. The &"" at the end turns an empty result into empty text; without it Excel would show a 0 wherever a grandparent is unknown. IFERROR covers people whose own parents are blank, since MATCH has nothing to find.

The youngest generation then looks like this:

Notice what the blanks say. Kate has four grandparent slots but only three known grandparents, and the sheet keeps that honest instead of guessing.

Step 2: full siblings

Full siblings share both parents. Put this in J2 and fill down:

J2  =IF(OR($D2="",$E2=""),"",IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$13,($D$2:$D$13=$D2)*($E$2:$E$13=$E2)*($A$2:$A$13<>$A2))),""))

Inside FILTER, each comparison produces TRUE or FALSE for every row, and multiplying them gives 1 only where all three hold: same father, same mother, not the person themselves. TEXTJOIN strings the matching names together with commas. The outer IF leaves the cell empty for anyone with a missing parent, since two blanks would otherwise count as a match.

The result: Dan and Eva list each other, and so do Kate and Leo. Ivy and Jack list nobody, because they share only a father. That is correct, and it makes a good first question for a class: what would you change to find half siblings? (The answer is at the end.)

Two version notes. Excel's FILTER returns an error when nothing matches, and Google Sheets' FILTER has no argument for an empty result, so the IFERROR wrapper is what lets one formula work in both. In an older Excel without FILTER, the cell will simply stay blank, so if nothing appears, check your version first.

Step 3: a cousin finder

Here is the rule worth teaching: two people are first cousins when they share at least one grandparent and no parent. If they share a grandparent but not a parent, their parents must be two different children of that grandparent, which makes the parents siblings (full or half).

Choose a person by typing an ID into O1. Then fill O2 to O7 with that person's father, mother and four grandparents:

O2  =IFERROR(INDEX($D$2:$I$13,MATCH($O$1,$A$2:$A$13,0),1)&"","")

In O3 to O7, change the final 1 to 2, 3, 4, 5 and 6, which picks out columns E to I. Now three more columns on each row:

K2  =IF($A2=$O$1,0,($F2<>"")*COUNTIF($O$4:$O$7,$F2)+($G2<>"")*COUNTIF($O$4:$O$7,$G2)+($H2<>"")*COUNTIF($O$4:$O$7,$H2)+($I2<>"")*COUNTIF($O$4:$O$7,$I2))
L2  =IF($A2=$O$1,0,($D2<>"")*COUNTIF($O$2:$O$3,$D2)+($E2<>"")*COUNTIF($O$2:$O$3,$E2))
M2  =IF(AND(K2>0,L2=0),"first cousin","")

K counts the grandparents this row shares with the chosen person, one check per grandparent slot, with each check switched off when the slot is blank. L does the same for parents. M applies the rule. To list the cousins in one cell:

O9  =IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$13,$M$2:$M$13="first cousin")),"")

With Kate (P11) chosen:

Leo shares three grandparents with Kate, not four, only because Hal's father is unknown. And Ivy and Jack, half siblings of each other, are both full first cousins of Kate: each shares the couple Abe and Bea with her.

Step 4: naming any relationship

The cousin rule only finds cousins. To name any pair, use a second tab, Pair, built on one idea: for two people X and Y, find their closest shared ancestor and count the steps up from each. Call the steps from X a and the steps from Y b. Then a = 1, b = 1 means siblings; a = 2, b = 1 means Y is X's uncle or aunt; a = 2, b = 2 means first cousins. These are the same two numbers our cousin calculator asks for.

Type X's ID in B1 and Y's in B2. In A5 to A11 type the slot numbers 0 to 6 (0 for the person, 1 father, 2 mother, 3 to 6 the grandparents FF, FM, MF, MM). In C5 to C11 type the steps up for each slot: 0, 1, 1, 2, 2, 2, 2. Then fill these down from row 5 to row 11:

B5  =IF(A5=0,$B$1,IFERROR(INDEX(People!$D$2:$I$13,MATCH($B$1,People!$A$2:$A$13,0),A5)&"",""))
D5  =IF(A5=0,$B$2,IFERROR(INDEX(People!$D$2:$I$13,MATCH($B$2,People!$A$2:$A$13,0),A5)&"",""))
E5  =IF(B5="","",IFERROR(INDEX($C$5:$C$11,MATCH(B5,$D$5:$D$11,0)),""))
F5  =IF(E5="","",C5+E5)

Column B lists X and X's known ancestors, column D lists Y and Y's. For each of X's ancestors, E looks for the same person in Y's list and returns Y's steps up to them, b. F adds the two. The smallest total marks the closest shared ancestor:

B13 =IF(COUNT(F5:F11)=0,"none",MIN(F5:F11))
B14 =IF(B13="none","",INDEX(C5:C11,MATCH(B13,F5:F11,0)))
B15 =IF(B13="none","",INDEX(E5:E11,MATCH(B13,F5:F11,0)))
B16 =IF(B13="none",0,COUNTIF(F5:F11,B13))
B17 =INDEX(People!$C$2:$C$13,MATCH($B$2,People!$A$2:$A$13,0))
B18 =IF(B13="none","no common ancestor in the sheet",IF(AND(B14>0,B15>0,B16=1),"half ","")&IFERROR(VLOOKUP(10*B14+B15,Names!$A$2:$D$10,IF(B17="M",2,IF(B17="F",3,4)),FALSE),"not in the name table"))

B14 is a, B15 is b, B16 counts how many shared ancestors sit at that closest distance, and B17 fetches Y's sex. B18 builds the answer. If two people share only one ancestor at that distance (a couple would give two), the relationship is a half one, so the word "half" goes in front. Direct lines are the exception: a grandparent is always a single shared ancestor, so the formula skips the check when a or b is 0.

The third tab, Names, is a lookup table in A1 to D10, with headers in row 1. The key is 10 × a + b, a plain number, because a key typed as 2-1 risks being read as a date.

Some pairs to try, with the answers the sheet gives:

The last row is a useful surprise for students. Hal and Eva are married, and the sheet correctly finds no blood link, because spouses are linked through their children, not through an ancestor.

Exercises, with answers

1. Find half siblings. In a new column, keep rows that match on exactly one known parent:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$13,((($D$2:$D$13=$D2)*($D2<>"")+($E$2:$E$13=$E2)*($E2<>""))=1)*($A$2:$A$13<>$A2))),"")

Ivy and Jack now list each other, and nobody else changes.

2. Add the coefficient of relationship to the Pair tab: =IF(B13="none",0,B16*0.5^B13). Each shared ancestor at the closest distance contributes one path with a + b links, worth one half for each link. For Ivy and Kate that is 2 × (1/2)^4 = 1/8, and for Ivy and Jack 1 × (1/2)^2 = 1/4. This shortcut assumes nobody in the tree appears twice; in a family with cousin marriages you need the full path method in the coefficient of relationship, worked out by hand.

3. Look at the cousins in this family. Kate's cousin Ivy is her MBD, and Ivy's cousin Kate is her FZD, because the linking siblings, Dan and Eva, are a brother and a sister. Every cousin pair here is a cross cousin pair. Ask the class which relatives would get different terms in a terminology that separates cross from parallel cousins, and what change to the data would create a parallel cousin.

4. Extend the sheet to great-grandparents: eight more columns built the same way from the grandparent columns, slots 7 to 14 on the Pair tab with 3 steps up, and new keys. Key 30 is great-grandparent, 3 great-grandchild, 31 great-uncle or great-aunt, 13 grandnephew or grandniece, 32 and 23 first cousin once removed, and 33 second cousin.

Where GEDCOM fits

Genealogy programs exchange trees as GEDCOM files (GEnealogical Data COMmunication), a plain-text format maintained by the Family History Department of The Church of Jesus Christ of Latter-day Saints, whose first public draft appeared in 1984. GEDCOM does not store a father and mother on each person, as our People tab does. It stores families: each family record (FAM) points to up to two partners, tagged HUSB and WIFE, and to children (CHIL), and each individual record (INDI) points back to the family it was born into (FAMC) and the families in which it is a partner (FAMS). To convert our sheet by hand, make one family record for each distinct pair of values in columns D and E. This family needs five: Abe and Bea, Dan and Faye, Dan and Gwen, Hal and Eva, and one for Hal's parents that lists Cleo as the only known partner.

Sources and further reading

• FamilySearch GEDCOM 7.0 specification. Family History Department, The Church of Jesus Christ of Latter-day Saints. https://gedcom.io/specifications/FamilySearchGEDCOMv7.html
• Microsoft Support. "Look up values with VLOOKUP, INDEX, or MATCH." https://support.microsoft.com/en-us/office/look-up-values-with-vlookup-index-or-match-68297403-7c3c-4150-9e3c-4d348188976b
• Microsoft Support. "FILTER function." https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
• Microsoft Support. "TEXTJOIN function." https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
• Google Docs Editors Help. "FILTER function." https://support.google.com/docs/answer/3093197
• Google Docs Editors Help. "TEXTJOIN function." https://support.google.com/docs/answer/7013992
• Wright, Sewall. 1922. "Coefficients of inbreeding and relationship." The American Naturalist 56 (645): 330-338. https://doi.org/10.1086/279872