Creating a Family Tree Chart in Excel with Dates
Use B/M/D fields, the 1 Jan 1890 date style, and City, County, State places in your Excel pedigree chart — without messy auto-formatting.
Names on a chart start the story. Dates and places make it a record someone can verify fifty years from now — matching courthouse ledgers, cemetery stones, and the notes your aunt keeps in a drawer.
Creating a family tree chart in Excel with dates means more than typing numbers under a name. Genealogy-standard pedigree charts use B: (birth), M: (marriage), and D: (death) labels, each with separate date and place lines. This guide covers what those fields mean, how to write dates like 1 Jan 1890, how to order places as City, County, State, and how to stop Excel from mangling your entries.
Building the chart structure itself — templates, SmartArt, or shapes — lives in How to Create a Family Tree Chart in Excel. Choosing between free downloads and print-ready pedigree workbooks is covered in the family tree chart excel template selection guide.
Key takeaway: A pedigree chart worth keeping uses labeled B:/M:/D: fields with consistent dates (1 Jan 1890) and places (City, County, State) — not names alone in a SmartArt box.
Why Dates and Places Belong on a Pedigree Chart
A decorative tree with first names works for a classroom bulletin board. A reunion binder, heritage donation, or research log needs life events — when someone was born, where they married, where they are buried.
Dates anchor generations. Places explain migration — why great-grandfather appears in both Cork and Chicago records. Without labeled fields, Excel family trees devolve into inconsistent notes: "born ~1890?" in one box and "1/2/1890 Chicago" in another.
Structured pedigree charts treat each person as a small data card: name plus three events, each event split into date and place. That mirrors paper forms used by genealogical societies and archive volunteers worldwide.
Understanding B, M, and D Fields
B: stands for Birth. Enter the birth date on the first line and birth place on the second — typically the town or city where the person was born, not necessarily where the family lived later.
M: stands for Marriage. Record the wedding date and location. If the person never married, leave both M: lines blank. Multiple marriages may need extra notes or supplemental sheets; single-page pedigree templates usually allocate one M: block per individual.
D: stands for Death. Enter death date and place (death location or burial site, following whichever convention your family uses consistently). For living relatives, leave D: lines empty. Some charts add the word "living" beside the name instead of inventing a death date.
B/M/D Field Reference
| Field | Means | Date line | Place line | Leave blank when |
|---|---|---|---|---|
| B: | Birth | 1 Jan 1890 or abt. 1 Jan 1890 | City, County, State | Never for documented individuals |
| M: | Marriage | Same date style | Marriage location | Person never married |
| D: | Death | Same date style | Burial or death location | Person is living |
Templates built for genealogy — including paid pedigree workbooks — pre-label these rows so you do not rebuild the structure in every cell. DIY SmartArt trees rarely include separate B/M/D lines; you squeeze everything into one text box and lose scanability.
Date Format: Use 1 Jan 1890 Style
Genealogy readers expect day + abbreviated month + four-digit year: 1 Jan 1890, 15 Mar 1922, 3 Dec 2001.
Why this format?
- No month/day ambiguity — 03/04/1905 means March 4 in the US and April 3 in much of Europe. Text months remove the guesswork.
- Sort-friendly enough — When every date in a chart uses the same pattern, human eyes compare generations quickly even if Excel does not sort them as true dates.
- Matches archive habits — Many published family histories and society forms use similar styling.
When dates are uncertain
Use standard modifiers in front of the date:
- abt. — about (circa a known point)
- bef. — before (record proves an event happened prior to this date)
- ca. — circa (same spirit as abt.)
- est. — estimated
Examples: abt. 1 Jan 1890, bef. 15 Mar 1922.
Living people
Do not fabricate death dates. Leave D: blank or note "living" near the name. For birth dates of living relatives, some families omit the full year for privacy — document your house rule and stay consistent.
Warning: Excel converts typed dates like 1-2-34 into serial numbers and reformats them — set cells to Text or type an apostrophe before the date (e.g., '1 Jan 1890) so your pedigree stays readable.
Place Format: City, County, State
American genealogy convention lists places from specific to general:
City, County, State
Examples:
- Springfield, Sangamon, Illinois
- Galway, Galway, Ireland (county name repeats for Irish civil records context)
- Brooklyn, Kings, New York
When you know county but not city: , Cook, Illinois — leading comma shows a missing city. When only the state is known: , , Ohio.
International trees adapt the pattern: City, Province, Country or City, Region, Country. The rule is the same: small place first, larger jurisdiction after, commas as separators.
Avoid mixing formats on one chart — "IL" on one line and "Illinois" on the next forces readers to translate. Pick state abbreviations or full names and stick with one.
Place names belong on the line below each date, paired with B:, M:, or D:. Keeping date and place on separate lines matches standard pedigree PDFs and printed forms archivists recognize.
Enter Dates in Excel Without Breaking Layout
Excel treats dates as numbers by default. That helps budgeting spreadsheets; it hurts pedigree charts where 1 Jan 1890 should display exactly as typed.
Fill One Person's B/M/D Record
- Select the date cells — Highlight birth, marriage, and death date cells; Format Cells → Text (or use a template with pre-formatted text placeholders).
- Enter birth — Type B: date as 1 Jan 1890 on the date line; place as Springfield, Sangamon, Illinois on the place line below.
- Add marriage if known — M: date and place on their lines; leave both empty if the person never married.
- Add death or mark living — D: lines for deceased relatives; leave D: empty for living people (some charts add "living" in the name field instead).
- Apply the same pattern — Copy the structure to every individual so readers can scan any cell group the same way.
Prefix trick: typing '1 Jan 1890 (leading apostrophe) forces text in a general-format cell. The apostrophe hides on print.
Template advantage: pedigree workbook templates ship with text-friendly cells and visible B:/M:/D: labels — you fill lines instead of designing field layout from scratch.
Work outward from individual #1 (the focal person) through parents and grandparents. Match each chart position's number if your workbook uses cross-page references ("Cont. on chart no.").
DIY Charts vs Structured Pedigree Templates
SmartArt and shape-based trees from the Excel how-to guide accept freeform text — but they do not enforce B/M/D rows. Over time, cousins add dates in conflicting formats and places without counties.
Structured templates solve the layout problem:
- Labeled B:/M:/D: blocks per person
- Separate date and place lines
- Chart numbering for multi-page trees
- Print areas that hide instruction columns
If your project needs reunion-ready printing with consistent genealogy fields, the Family Tree Chart Excel Template ships four portrait/landscape layouts with B/M/D placeholders for $3.9 USD one-time. Open the Start Here sheet, pick a layout, and fill dates before the oral-history details fade.
For template selection criteria (layouts, numbering, hide-before-print), see the family tree chart excel template guide.
Key takeaway: One consistent date style and one place order across every person matters more than filling every blank on the first pass — you can return when research turns up courthouse records.
Frequently Asked Questions
What do B:, M:, and D: mean on a family tree chart?
Birth, Marriage, and Death. Each abbreviation introduces a date line and a place line. Standard on pedigree forms used in genealogy research.
What date format should I use in an Excel family tree?
1 Jan 1890 style — day, three-letter month, four-digit year. Add abt., bef., or ca. when dates are estimated. Avoid bare numeric dates that Excel may reformat.
How do I write places on a pedigree chart?
Use City, County, State for US locations, comma-separated, specific to general. Leave segments blank with commas if information is missing (e.g., , Cook, Illinois).
Will Excel change my dates automatically?
Yes, unless cells are formatted as Text or you prefix entries with an apostrophe. Pedigree templates with text-formatted placeholders reduce this risk.
Can I add dates to a SmartArt family tree?
You can type dates inside SmartArt boxes, but space is limited and fields are not labeled. For full B/M/D structure, use a pedigree template or a custom cell layout with fixed rows.
