How to Build a Client Tracker in Excel: A Step-by-Step Guide That Actually Works
Not a template to download blindly — a walk through the structure, formulas and formatting that make a client tracker genuinely useful, so you can build and adapt your own.
Published 2026-07-13 · 4 min read
Most client tracker templates fail for the same reason: they are built to look impressive in a screenshot rather than to survive six months of daily use.
This guide walks through building one yourself. It takes about forty minutes and you will understand every part, which means you can change it when your process changes.
Before you open Excel
Decide two things first, because they determine the entire structure.
What stages does a client move through? Write them down in order. For most service businesses it is something like: Enquiry, Quoted, Negotiating, Won, Lost, Completed. Yours might differ. Six or fewer is ideal — more than that and you spend longer classifying than selling.
What do you need to know at a glance? Usually: who is overdue a follow-up, what the pipeline is worth, and which clients have gone quiet. Design backwards from those answers.
Sheet one: the client list
One row per client. Never one row per interaction — that is a different sheet, and mixing them is the single most common mistake.
Columns, in this order:
| Column | Type | Why |
|---|---|---|
| Client ID | Text | Lets other sheets reference this row reliably |
| Company | Text | |
| Contact name | Text | |
| Text | ||
| Phone | Text | |
| Status | Dropdown | Your stages from earlier |
| Value | Currency | Quoted or expected value |
| First contact | Date | |
| Last contact | Date | |
| Next action | Text | What you owe them |
| Next action date | Date | Drives your overdue alerts |
| Notes | Text |
Client ID matters more than it looks. Use something simple and permanent — C001, C002. Company names get retyped and misspelled; an ID never changes.
Making Status a real dropdown
Select the Status column. Go to Data, then Data Validation. Choose List and type your stages separated by commas.
This is not cosmetic. Free-typed status values produce Won, won, WON and Won with a trailing space, and every formula that counts them silently undercounts. A dropdown makes that impossible.
Sheet two: the activity log
One row per interaction. This is the sheet that tells you what actually happened.
Columns: Date, Client ID, Type — call, email, meeting — Summary, Outcome, Follow-up needed.
Keep summaries short. One line. The purpose is to remind you, not to transcribe the conversation.
The formulas that earn their place
Four formulas do most of the work. Everything else is decoration.
Days since last contact. In a spare column on the client sheet:
=TODAY()-J2
Where J2 is the Last contact date. Any client above about thirty on this number has gone quiet.
Overdue flag. A simple yes or no:
=IF(L2<TODAY(),"OVERDUE","")
Where L2 is Next action date.
Last contact pulled from the log. This is the one that stops the client sheet going stale:
=MAXIFS(ActivityLog!A:A,ActivityLog!B:B,A2)
It finds the most recent date in the log for that Client ID. Now Last contact updates itself every time you log an interaction — you never maintain it by hand.
Interaction count.
=COUNTIF(ActivityLog!B:B,A2)
Useful for spotting clients you have chased eight times who are never going to buy.
Conditional formatting that actually helps
Two rules. Resist adding more — a sheet where everything is coloured tells you nothing.
Overdue follow-ups in red. Select the whole data range, add a new conditional formatting rule using a formula, and enter:
=$L2<TODAY()
Set the fill to a light red. Note the dollar sign before L and none before 2 — that makes the rule check column L while applying to the entire row.
Gone quiet in amber. Same approach:
=TODAY()-$J2>30
Light amber fill. Now the two things you most need to see announce themselves without you looking for them.
Sheet three: the summary
This is what turns records into a system. Four numbers, refreshed automatically.
Count by stage:
=COUNTIF(Clients!F:F,"Quoted")
Repeat for each stage. Put them in a small table with the stage names in the adjacent column.
Pipeline value:
=SUMIF(Clients!F:F,"Quoted",Clients!G:G)
Overdue count:
=COUNTIF(Clients!M:M,"OVERDUE")
Won this month:
=SUMIFS(Clients!G:G,Clients!F:F,"Won",Clients!J:J,">="&EOMONTH(TODAY(),-1)+1)
Add a bar chart from the count-by-stage table and you have a pipeline view that updates itself.
Freeze the header row
View, then Freeze Panes, then Freeze Top Row. Trivial, and you will thank yourself on row ninety.
Using it so it does not rot
The structure is the easy part. Every abandoned tracker died the same way — nobody updated it.
Three habits keep it alive. Log the interaction immediately after it happens, not at the end of the day when you have forgotten the detail. Check the overdue column every morning before anything else. Review the whole sheet on a fixed day each week and clear out anything dead.
A tracker updated inconsistently is worse than no tracker, because you start trusting numbers that are wrong.
When to stop using Excel
Excel handles a few hundred clients comfortably. Past that, filtering slows and finding things becomes a chore.
Move on when several people need to edit simultaneously and Google Sheets is not enough, when you need automatic email logging, or when you genuinely need mobile access.
Until then, a well-built spreadsheet does the job of software costing several hundred dollars a year — and you own it outright.