How to track clients with a spreadsheet
The mistake is keeping the history in the client list. A history is a second sheet where every row is an interaction, not a client. The five columns, the one formula that connects them, and why most of these die in three weeks.
Syncek Team · CRM reference library
/ 4 min read / Art. #72
To track clients in a spreadsheet, keep their history on a second sheet, where each row is an interaction, not a client, with five columns: date, client, type, what happened and what is next. One formula then pulls each client's most recent interaction date back into the client list. Almost everyone starts the other way, adding the history to the client list itself. Once there is a "last call" column, then "previous call", then "notes 3", every client has a different number of interactions, and columns always run out.
Which five columns does a client history need?
Date. A real date format, not text. Without it you cannot sort, and everything else is decoration.
Client. Exactly the same identifier you use in the client list. If one sheet says "Northgate Ltd" and the other says "Northgate", you have two clients.
Type. Call, email, meeting, quote. Four or five fixed options chosen from a list, never typed by hand.
What happened. One line. Not the minutes.
What is next. What needs doing and by when, or blank if nothing. This is the column that makes the history worth keeping: without it you have an archive, with it you have a worklist.
That is enough to filter by client and read their whole story in order, or filter on a non-blank "what is next" and get your week.
The five columns at a glance:
| Column | What to write | Why |
|---|---|---|
| Date | A real date format, not text | Without it you cannot sort, and everything else is decoration |
| Client | Exactly the same identifier as in the client list | "Northgate Ltd" in one sheet and "Northgate" in the other are two clients |
| Type | Call, email, meeting or quote, picked from a list of four or five fixed options | A dropdown list means nothing is typed twice two ways |
| What happened | One line, not the minutes | Three words in the same minute beat a paragraph on Friday that never gets written |
| What is next | What needs doing and by when, or blank if nothing | It turns an archive into a worklist |
Which formula connects the two sheets?
Pull the most recent interaction date for each client back into the client list. With the history on a sheet named History, dates in column A and clients in column B, and the client in A2 of the list, the formula is =MAXIFS(History!A:A,History!B:B,A2) in Excel 2019 or later and in Google Sheets. That single column is what turns two loose sheets into something like a system, because sorting it oldest-first produces the list of neglected clients with no extra work. It is the cheapest useful number in the whole setup and it is almost never built, because it is nobody's job until somebody decides it is.
Why do most of these die in three weeks?
Not the structure. The moment of writing. A history gets filled in as you hang up the phone, or it does not get filled in. If it means opening a laptop, finding the file and scrolling past four hundred rows, it will not happen, and within three weeks there are gaps. After that you stop trusting it, and a sheet you do not trust gets abandoned on its own.
What actually works: sort newest-first so you always type in row two, use dropdown lists so nothing is typed twice two ways, and write three words in the same minute rather than a paragraph on Friday that never gets written.
Where does a spreadsheet history stop being enough?
The usual three, and they arrive sooner here than in the client list because a history grows much faster.
There are no reminders, so "what is next" only works if a human remembers to look. Two people can write at once only in a file set up for it, which in Excel means co-authoring from OneDrive or SharePoint; on a shared drive the second person finds it locked, and a copy becomes a second history. And there is no way to attach an interaction to a deal rather than to a client, which is the one that bites first as soon as you sell anything with stages, because a client with three open jobs has one history and no way to tell which thread a note belongs to.
Until then, two sheets and one formula is considerably more than most businesses have. It works as long as the data stays clean and the client table is built properly, and the honest comparison of when to move on is here.
Frequently asked questions
How do you keep a client history in a spreadsheet?
On a second sheet, with one row per interaction instead of one row per client. Five columns carry it: date in a real date format, the client identifier exactly as the client list spells it, a type chosen from a fixed list, one line on what happened, and what is next. Filter by client to read a whole relationship in order, or filter on a non-blank "what is next" to get the week's work.
Can the history go in the same sheet as the client list?
It can, and it stops working within a few months. Each new interaction needs another column, so the sheet grows "last call", then "previous call", then "notes 3", and every client carries a different number of them. Columns always run out, and the ones that exist are half empty. One row per interaction has no such ceiling, which is the whole reason the history gets its own sheet.
What columns does an interaction log need?
Five. Date, as a real date rather than text, or nothing can be sorted. Client, spelled exactly as the client list spells it. Type, picked from four or five fixed options so nothing is written two ways. What happened, in one line rather than minutes. And what is next, with a deadline or blank. That last column is what turns an archive into a worklist.
Which formula connects the history to the client list?
A formula that pulls each client's most recent interaction date back into the client list, keyed on the identifier both sheets share: =MAXIFS(History!A:A,History!B:B,A2) in Excel 2019 or later and in Google Sheets, with dates in column A of the History sheet, clients in column B, and the client in A2 of the list. Sorted oldest first, that single column is the list of clients nobody has spoken to in a while, produced with no extra work. It is the cheapest useful number in the setup and almost nobody builds it, because it is nobody's job until somebody decides it is.
Why do spreadsheet histories stop getting filled in?
Because of when the writing happens, not how the sheet is built. A history gets filled in as the call ends or it does not get filled in at all. If that means opening a laptop, finding the file and scrolling past four hundred rows, it will not happen, and gaps appear inside three weeks. After that nobody trusts it, and a sheet nobody trusts is abandoned without a decision.
How do you stop the same client being recorded two ways?
Use one identifier and never retype it. If the client list says "Northgate Ltd" and the history says "Northgate", the formula joining them sees two different clients and the record splits in half. The type column carries the same risk, so pick it from a dropdown list rather than typing it. Sorting newest first helps too: you always write in row two, where a mistake is visible.
When does a spreadsheet history stop being enough?
At three limits, and they arrive sooner than they do in the client list because a history grows much faster. There are no reminders, so "what is next" only works while a person remembers to look. Two people can write at the same time only in a file set up for it, and anything less locks one of them out or splits the history into copies. And an interaction can only attach to a client, never to a deal, which bites first when you sell anything with stages.