Personal CRM in Google Sheets: the full build, step by step

A spreadsheet is the most underrated personal CRM there is: nine columns, one formula, one formatting rule, and Google Sheets will tell you exactly who you’re losing touch with every time you open the file. The build below takes under an hour. The limits are real too — and they’re in here, not in the fine print.

Why Sheets is a legitimate choice

Before the build: this isn’t the budget option you settle for. For a focused job — show me who I’m drifting from — a spreadsheet is genuinely competitive with paid tools. It costs nothing, syncs everywhere Google does, exports to CSV forever (no lock-in conversation required), and is faster to scan-and-edit on a laptop than most dedicated apps. It’s the difference between an address book and a CRM achieved with the software you already have.

One consideration before you type anything sensitive: this file lives on Google’s servers, and it will contain notes about people who never agreed to be in it. Keep the notes factual and kind — the working test is whether you’d be comfortable with the person reading their own row. That standard costs you nothing in usefulness and spares you ever having to apologize for a cell.

What you’re giving up is also clear, and worth naming now rather than discovering in month two: no notifications, no interaction history worth the name, and data entry that’s tolerable on a laptop and annoying on a phone. If those are deal-breakers for you, the Notion build trades some simplicity for a real interaction log — and whether you should be building anything at all is the question the self-diagnostic answers. For everyone else: one hour, nine columns, here we go. (If you’d rather start from a finished file, the free personal CRM spreadsheet template has the same structure ready to import — these steps then serve as its manual.)

The build

1. Set up the nine columns

   In row 1: Name, Group, Cadence (days), Last contact, Days since, Status, Notes, Channel, Birthday. Freeze the header row (View → Freeze → 1 row). Columns A–F are the engine: who, which circle, how often you want contact, when it last happened, how long ago that is, and whether that’s fine. Notes holds one line of current context (“new job at Vattenfall, dad recovering”), Channel is where you actually reach them (Signal, email, in person), Birthday is self-explanatory and earns its keep twice a year.

2. Fill in 15–25 real people

   Not your 800 address-book entries — the relationships you actively want to keep and tend to lose track of. Set Cadence per person, not globally: 30 days for close friends, 90 for mentors and good ex-colleagues, 180–365 for the warm outer circle. Dunbar (1992) put the ceiling for stable relationships around 150, but start with a number you can face on a Sunday morning. Estimate Last contact dates generously; the system self-corrects from here.

3. Add the days-since formula

   In E2 (assuming Last contact lives in D): =IF(D2="","",INT(NOW()-D2)). NOW() returns the current date-time; subtracting the last-contact date gives elapsed days as a decimal, and INT() floors it to whole days. The IF guard keeps rows without a date blank instead of erroring. Drag the formula down the column. It recalculates on every open — the sheet is now self-updating, which is the entire point: you maintain dates, never math.

4. Add data validation for Group and Cadence

   Select column B, Data → Data validation, criteria Dropdown: friend, family, mentor, professional, community. Then column C, same dialog, dropdown of numbers: 30, 60, 90, 180, 365. This looks pedantic and pays off within weeks — free-typed groups fork into “Friend”, “friends”, and “buddy”, and filtering dies. Validation makes the sheet boring in exactly the way databases are supposed to be boring.

5. Add the overdue formatting rule

   Select your data range (say A2:I200), Format → Conditional formatting, Custom formula is: =AND($D2<>"",$E2>$C2) — a last-contact date exists and days-since has passed cadence. Pick a red/warm fill for the whole row. Add a second, softer rule with =AND($D2<>"",$E2>$C2*0.8) in yellow for “approaching”. Now the file triages itself on open: red rows are the to-do list, no sorting required.

6. Create filter views and the weekly ritual

   Data → Filter views → Create new: one view called Overdue first, sorted by Days since descending; one per Group if your circles are distinct. Filter views don’t rearrange the underlying sheet — they’re saved lenses, which matters once you’re editing on multiple devices. Then the part no formula replaces: a recurring 10-minute calendar slot. Open Overdue first, message the top two people, update their dates and notes. Done weekly, that’s the entire operating cost of the system.

Three upgrades that stay cheap

Once the base sheet has survived a few weeks, three additions earn their keep without turning the file into a project.

A Status formula. Replace the manual Status column with =IF(E2="","",IF(E2>C2,"overdue",IF(E2>C2*0.8,"soon","ok"))). Now column F reads as words, the conditional formatting can key off text instead of arithmetic, and filter views like “everything overdue” become one-click. It also makes the sheet legible to future-you: six months from now, “overdue” needs no decoding, while a bare number does.

A birthdays-this-month view. Add a helper column with =MONTH(I2) (Birthday in column I), then a filter view restricted to =MONTH(TODAY()). Open it on the first of each month and you get the month’s birthdays in one glance — the single highest-yield, lowest-effort touchpoint in relationship maintenance, and the one a plain date column makes you scan for manually.

An append-only Log tab — maybe. If losing history genuinely bothers you, add a second tab with three columns (date, name, two-word note) and one rule: only ever append, never reorganize. It won’t feed the main sheet automatically — resist building lookup chains; that’s how spreadsheets become unmaintained software — but it gives you a greppable memory for “when did I last actually see Ana?” at the cost of one extra row per touchpoint. If that cost sounds heavy, skip it: the main sheet works fine without.

The line to hold: every upgrade above is still just columns and views. The moment you’re tempted by Apps Script triggers, mail digests, or cross-sheet sync, you’ve outgrown the format — that energy is better spent on the graduation decision below.

The honest limits

Three gaps are structural, and no formula patches them.

It never taps you on the shoulder. The sheet computes overdue status perfectly and tells no one. Every paid CRM’s core feature — the reminder that finds you — is absent by design here. The calendar-slot workaround is honest but circular: it works exactly as well as your habit of honoring calendar slots.

History doesn’t exist. One row per person means each update overwrites the last. What did you talk about in March? Gone — the sheet knows only “latest”. A second log tab can preserve history at the cost of double entry; in practice, almost nobody sustains it. This is the single biggest thing a database-shaped tool buys you.

Notes don’t scale and phones don’t help. A cell fits one line of context; a relationship generates pages. And editing validated, formatted rows in the Sheets mobile app is fiddly enough that real capture happens at the laptop, hours later, lossy. Past roughly 100 active contacts, the manual upkeep stops being a ritual and becomes a job — the CRM hygiene burden grows linearly with rows.

When to graduate — and when not to

Stay on the sheet as long as it’s working: a maintained spreadsheet beats an abandoned app by every measure that matters, and zero euros is a strong feature. The reader who stops here, with the file above and the Sunday ritual, has a complete and functioning system.

Graduate when a missing feature starts costing actual relationships — follow-ups dropped because nothing nudged you, context lost to one-line notes, upkeep sliding past 100 contacts. That’s the point where dedicated tools earn money: we make one, Endearist, and the wider trade-offs between sheet, Notion, and the paid category are mapped in our comparison guide — including the cases where staying on the spreadsheet is the right call. The 3-year math for your shortlist is what the CRM cost calculator is for. Until those triggers fire, the sheet is not a compromise. It’s the tool that fits the job.