How do I build a personal CRM in Google Sheets?

Download the CSV, open it in Sheets or Excel, and freeze the header row. Give every person a tier (5, 15, or 50) and a cadence in weeks, let a formula compute the next-touch column, and color overdue rows with conditional formatting. The most important component isn't technical: a fixed weekly slot where you actually walk the list.

What do the 5/15/50 tiers mean?

The numbers come from Robin Dunbar's research on relationship layers: roughly five intimate confidants, about fifteen close friends, around fifty good acquaintances. In the sheet they drive the cadence — tier 5 hears from you every week or two, tier 15 about monthly, tier 50 quarterly. They're guidelines, not quotas you need to fill.

How often should I check in with friends?

There's no universal number — which is why the template sets a cadence per person. Workable starting points: inner circle every one to two weeks, close friends every three to four, the wider circle once a quarter. Then adjust against reality: warm, quick replies mean shorten the interval; two silences in a row mean lengthen it.

Isn't it weird to track friendships in a spreadsheet?

It only feels odd because forgetting happens invisibly while writing things down is visible. Nobody calls a birthday calendar cold — this sheet is the same principle taken one step further. It would only become weird if the list existed to optimize relationships rather than remember them. As long as it ends in real messages to real people, it's simply memory on paper.

Free template

Personal CRM spreadsheet template

Searching for a personal CRM spreadsheet usually means you've realized two things at once: your relationships deserve more deliberate attention than your memory provides, and you're not ready to commit to yet another app. Fair on both counts. This template is the full system in one sheet — tiers, a per-person cadence, a warmth score, birthdays, partner and kids' names — because half-systems are what fail. The honest trade-off: a spreadsheet can hold this structure, but it can't act on it. It won't remind you that Maya's cadence lapsed two weeks ago or that a birthday is on Friday; you have to open the file and look. Many people run the sheet happily for a year or two. When the looking itself becomes the bottleneck, this CSV imports into Endearist as-is — tiers, cadences, and warmth included — which is exactly why the columns are structured the way they are.

Download CSV

Opens in Excel, Google Sheets, Numbers — or imports straight into Endearist.

See all templates →

What each column means

Column	How to use it	Example
Name	One row per person, not per household — you have relationships with people, not with addresses.	Maya Donnelly
Email	Whatever channel actually reaches them — for WhatsApp-only friends, the number can live here instead.	maya.donnelly@hey.com
Tier (5/15/50)	5 = inner circle, 15 = close friends, 50 = the good wider circle. Borrowed from Dunbar's layers — the tier drives the cadence.	15
Cadence (weeks)	Maximum weeks between two real touches. Guidelines: tier 5 → 1–2, tier 15 → 3–4, tier 50 → 12.	3
Warmth (1–5)	Your gut read on the current state: 5 = very close right now, 1 = nearly dormant. Deliberately subjective — update it after each touch.	4
Birthday	YYYY-MM-DD format; if you don't know the year, use 1900 as a placeholder and sort by month and day only.	1988-09-14
Partner / kids	Names of the key people in their life. "How's Theo liking school?" carries more than any amount of small talk.	Partner Sam, son Theo (6)
How we met	The origin of the relationship in one line — the shared beginning is often the best hook for reconnecting after years.	College housing in Madison, 2014
Last contact	Date of the last real exchange (call, meetup, message with a reply) as YYYY-MM-DD. Likes don't count.	2026-05-30
Next touch	Compute it as last contact plus cadence — by formula or by hand. This column is your early-warning system.	2026-06-20
Notes	Ongoing topics, current worries, open promises — the raw material your next message is made of.	Starting a new job in Denver in August; wanted trail tips for the Rockies

How to use this template

Assign tiers first, everything else second
Enter all the names and assign tiers 5, 15, and 50 in one pass — without stopping to look up emails or birthdays. Tiering is a gut call and gets worse the longer you deliberate. Fill in details afterwards, tier by tier, starting with the inner circle.
Let the sheet compute the next touch
Put a formula in the next-touch column: last contact plus cadence times seven — in Sheets, something like =I2+(D2*7). Freeze the header row, format both date columns as dates, and add data validation so no "sometime in June" entry breaks the math.
Highlight overdue people automatically
Create a conditional-formatting rule that colors any row whose next-touch date is before TODAY() — bold for tier 5, subtle for tier 50. The file then shows you at a glance where a relationship is cooling, weighted by importance rather than alphabet.
Run a weekly ten-minute review
Once a week: sort ascending by next touch, walk the overdue rows, send two or three messages, update the dates and warmth scores. Ten minutes is enough. A personal CRM lives or dies not by its structure but by this ritual.

Mistakes to avoid

Starting with 200 people
The 5/15/50 tier logic adds up to roughly 70 people — that's not an accident, it's about the ceiling of what anyone can actively maintain. Set up 200 rows on day one and you've built a guilt machine. Start small and consciously promote people in when there's room.
Scoring warmth once and never touching it again
A warmth score from January says nothing by June — relationships cool quietly. If you don't glance at the number after each touch, the column becomes decoration. Couple the update to logging the last contact: two cells, one motion.
Sensitive notes in a shared cloud file
This sheet holds the most private things you know about people: kids' names, crises, health issues. In a shared Google Sheet or a synced team folder, that's a data leak waiting to happen. Keep the file local or in a folder only you can open — and leave the most delicate things out entirely.
Letting the system quietly become a sales pipeline
If every row needs a "use", it's no longer a personal CRM — it's prospecting wearing a friendship costume, and people can feel it. Tiers map closeness, not utility. Check yourself honestly: would this person still be tier 15 if they could never do anything for your career?

Frequently asked questions

How do I build a personal CRM in Google Sheets?: Download the CSV, open it in Sheets or Excel, and freeze the header row. Give every person a tier (5, 15, or 50) and a cadence in weeks, let a formula compute the next-touch column, and color overdue rows with conditional formatting. The most important component isn't technical: a fixed weekly slot where you actually walk the list.
What do the 5/15/50 tiers mean?: The numbers come from Robin Dunbar's research on relationship layers: roughly five intimate confidants, about fifteen close friends, around fifty good acquaintances. In the sheet they drive the cadence — tier 5 hears from you every week or two, tier 15 about monthly, tier 50 quarterly. They're guidelines, not quotas you need to fill.
How often should I check in with friends?: There's no universal number — which is why the template sets a cadence per person. Workable starting points: inner circle every one to two weeks, close friends every three to four, the wider circle once a quarter. Then adjust against reality: warm, quick replies mean shorten the interval; two silences in a row mean lengthen it.
Isn't it weird to track friendships in a spreadsheet?: It only feels odd because forgetting happens invisibly while writing things down is visible. Nobody calls a birthday calendar cold — this sheet is the same principle taken one step further. It would only become weird if the list existed to optimize relationships rather than remember them. As long as it ends in real messages to real people, it's simply memory on paper.

Last updated: 2026-06-10

When the spreadsheet gets too small.

Endearist imports this CSV directly — and reminds you before contacts go quiet. Free up to 25 contacts.

Start free