Family Tree Spreadsheet Template Excel Google Sheets | Genealogy Research Organizer, Pedigree Chart, DNA Match, Census and Ancestry Log

THE PROBLEM IS NOT THAT YOU HAVE TOO LITTLE. IT IS THAT YOU HAVE TOO MUCH.

Four censuses that disagree about one man's birth year. A DNA match at 892 cM that you have never attached to anybody. Eleven letters from Cork that an aunt has in a drawer. Three subscriptions renewing. A great-grandmother whose parents you have looked for twice and cannot remember whether you already searched the 1868 register.

Somewhere in that pile is the one thing worth doing next. This workbook finds it.

It holds 30 people in the sample tree. It knows that 25 of the 31 ancestor slots in a five-generation pedigree are filled and 6 are not. It knows that 11 of those people have no source of any kind behind them. It knows one census row is out by 4 years against the birth year you hold, that 3 DNA matches are big enough to matter and are attached to nobody, and that the oldest relative you have not interviewed is 93.

None of that is typed in. It reads your own data, every time you open it.


WHAT'S INSIDE - 16 LINKED TABS, 15,700 WORKING FORMULAS, 24 DATA CHECKS

Dashboard - the state of the research at a glance, then 24 checks that look at your own entries and tell you what needs doing. People with no source. Census ages that contradict your dates. DNA matches with no ancestor assigned. Research tasks past their target. Relatives at the age where the interview cannot wait. Heirlooms with no photograph. Subscriptions about to renew. Nothing is typed on this tab

Individuals - one row per person, entered once. Status, age, source count, a completeness score out of eight, and a data check that catches a death before a birth, a parent ID that is not in the sheet, a lifespan over 110, a person listed as their own father, and a circular parent link that would otherwise fill your pedigree with the same two people forever

Families - one row per marriage. Children are counted automatically from the parent columns on Individuals, including for a family where only one parent is recorded. Years together, and a check for a marriage dated before either spouse turned twelve or after one of them died

Pedigree - five generations, 31 ancestor slots, ahnentafel numbered, built entirely from the Father ID and Mother ID columns. Change the root person and the whole chart redraws. Each generation reports how complete it is, and the empty slots are shown in red because an empty slot is not an error - it is a person who existed and whose name you have not found yet

Sources - the citation log, with a confidence rating on every record: Proven, Probable, Possible or Disproven. This is the tab that stops a family story quietly becoming a fact. It flags a record with no citation text, a person ID that does not exist, and anything still being leaned on after you disproved it

Research Log - open questions, the record to search next, the archive to contact, and a target date. Just as importantly it records what you have already searched and found NOTHING in, so you stop paying twice for the same dead end. Overdue tasks are raised on the Dashboard

DNA Matches - shared cM, segments, platform, and an automatic relationship band read from a lookup table you can edit. Then the part every other sheet skips: a match is not evidence until it is attached to a named person in your tree, so the Next Action column keeps asking - ASSIGN ANCESTOR, CONTACT, Follow up, or Confirmed - and never lets a close-family match sit at the bottom of the list

Census Grid - one row per person per census, recorded exactly as the enumerator wrote it, misspellings and all, because those misspellings are how you find the next record. It then works out the birth year the recorded age implies, compares it with the year you hold, and flags the gap. You set the tolerance, so only the gaps worth chasing are raised

Timeline - every dated event with the age the person was at the time, and a check for anything dated before they were born or after they died

Interviews - living relatives ranked by age, because this is the only research window in genealogy that closes. Anyone at or over the age you set who has not been interviewed is raised as URGENT. Questions to ask, planned date, recording link, stories captured

Heirlooms - the bible, the watch, the letters, the discharge papers. What exists, who has it now, where it is, and whether there is a photograph or scan. Anything above the value you set with no image link is flagged, because objects leave families and photographs do not

Research Costs - subscriptions, record fees, DNA kits, archive travel and books, with days to renewal, an annual run rate and a warning before anything renews. The sample shows $917 a year in recurring cost, which is the figure most people have never added up

Settings, Lists, Start Here and Disclaimer - your constants, your dropdowns, a four-step setup, and plain language about what this file is and is not


THE THING THAT BREAKS EVERY OTHER GENEALOGY SPREADSHEET

Excel's calendar begins on 1 January 1900. An 1836 birth cannot be stored as a date in any spreadsheet, and forcing it produces text that silently breaks every calculation downstream - which is why so many family-tree templates quietly stop working the moment you go back past your grandparents.

This one is built the other way round. Every historical date has two columns: the date written exactly as the record gives it ("abt 1870", "19 Jan 1868", "bef 1901") and the YEAR on its own in a number column. All the arithmetic reads the year column, so ages, census conflicts, lifespans and timelines work for the 1700s exactly as they work for last year. The sample tree reaches 1836 and every figure in it calculates.

Modern working dates - date accessed, target date, planned interview, renewal - are ordinary dates and sort and filter normally.


BUILT FOR

The hobbyist who has been at this for years and has the file sprawling across four documents. Anyone who has just had a DNA result back and does not know what to do with a list of strangers. The person in the family who has been handed the box of photographs. Anyone assembling a history for a reunion or as a gift, and anyone who keeps a spreadsheet alongside their Ancestry or MyHeritage tree because they want their own copy of their own research.


HOW IT WORKS

1. Open Settings and set the living-person window, the census age tolerance and the interview urgency age
2. Delete the sample family and enter yourself, then your parents, then theirs
3. Put your own Person ID in the gold cell on the Pedigree tab
4. Open the Dashboard whenever you sit down to research

That is the whole workflow. Everything else is calculated.


COMPATIBILITY

Microsoft Excel 2016, 2019 and 365, Windows and Mac
Google Sheets - File, Import, Upload, Replace spreadsheet
LibreOffice Calc and Apple Numbers

No macros. No add-ons. No array formulas. No subscription. Works offline, so your family's data stays on your own machine. The file is yours forever.


PLEASE READ BEFORE BUYING

This is a research organiser and a record-keeping aid. It is not genealogical proof and not evidence of descent.

The data checks compare your entries against each other. A clear check means your entries are internally consistent. It does not mean they are correct, and it cannot tell you that you have attached the wrong Edward Hale to the right family. That judgement is yours, which is what the confidence rating on the Sources tab is for.

The relationship estimated from shared centimorgans is a probability range, not an answer. The bands overlap heavily - 200 cM is consistent with a second cousin, a first cousin twice removed and several other relationships at once - and endogamous populations inflate shared cM substantially. Treat every band as a hypothesis to test against records.

Anyone with no death year, born inside the window you set, is shown as Living (assumed). That is a guess made by a formula. Take care with the details of living people before sharing a file or uploading a tree; dates of birth, maiden names and places of birth are exactly the information used to answer security questions. DNA results can also reveal misattributed parentage and previously unknown siblings, more often than most people expect.

Repositories and subscription sites set their own terms for copying and republishing records. Store links and citations freely; check the terms before republishing images.

The sample family is fictional. Every person, record, repository reference and DNA match in it was invented to show you the shape of a working file.


WHAT YOU GET

One .xlsx file, delivered instantly on purchase. Free updates. Free customisation help - message the shop with any question about this file and you will get a real answer.

This is a digital download. Nothing is posted.

📗 HOW TO OPEN IN GOOGLE SHEETS (free & easy):
This is one file that works in BOTH Excel and Google Sheets — there's no separate file to find, you just import the download once:
1. Go to sheets.google.com and open a blank spreadsheet
2. Click File ▸ Import ▸ Upload
3. Drag in the .xlsx file you downloaded from this order
4. Choose "Replace spreadsheet" and click Import data
All tabs load as a full Google Sheet saved in your Drive — edit it on any device. (Prefer Excel? Just double-click the file to open.)

———————————————
VIDEO TUTORIAL INCLUDED
This template comes with a step-by-step video walkthrough to help you get set up in minutes. Your tutorial link is included with the instant download.