One row per client. The whole relationship in three sheets.
Clients, conversations and payments each get their own sheet. Formulas join them, so the client list shows who is due a follow-up and who still owes you.
One file, six sheets, no macros
It stays a spreadsheet. Nothing to install and no add-ins, so it opens the same way on any machine. Each data sheet is already one header row and one row per record.
=IF(OR($A2="",COUNTIF(History!$B:$B,$A2)=0),"",MAXIFS(History!$A:$A,History!$B:$B,$A2))| A | E | J | K | |
|---|---|---|---|---|
| 1 | Company | Status | Last contact | Next follow-up |
| 2 | Northgate Ltd | Active | Sep 4, 2026 | Sep 11, 2026 |
| 3 | Larchfield Studio | Active | Aug 1, 2026 | |
| 4 | Ashby Joinery | Paused | Jul 15, 2026 | |
| 5 | Brightwater Bakery | Lead | Sep 24, 2026 | Oct 5, 2026 |
| 6 | . | . | . | . |
Three sheets hold your data. Each row is one thing.
Clients
One row per client
- Company
- Contact person
- Phone
- Status
- How they found you
- What they bought
- Last purchase
- Your field
- Last contact
- Next follow-up
- Paid to date
- Outstanding
The company name is what every other sheet matches on, so it's written once, here, and picked from a dropdown everywhere else. Status and the four shaded columns tell you where each client stands.
History
One row per conversation
- Date
- Client
- Type
- What happened
- What is next
- Follow-up date
What is next turns an archive into a worklist. Add a follow-up date and the client list shows it back to you on the day.
Payments
One row per invoice
- Client
- Amount
- Invoice date
- Due date
- Status
- Paid date
- What it was for
- Days overdue
Paid date is the field people skip. Kept for a year, it shows which clients are always late, which is what you act on at renewal.
Three more sheets keep it running. Start here explains the rules inside the file, Summary counts what needs doing, and Lists holds every dropdown option, so renaming a status changes it everywhere at once. The shaded columns fill themselves in.
Four rules decide whether the sheet still works in a year.
One row per client
No blank rows as separators and no headings in the middle of the data. A row that belongs to no client is invisible to every sort.
One fact per column
"Northgate Ltd, Manchester, Priya" in one cell is three facts nobody can filter apart. In three columns you can sort and count them.
Colour is not data
A formula can't read yellow, and six months on nobody remembers what it meant. If a colour says something, give it a column.
No merged cells
They look tidy and they break sorting and pivot tables the day you need them.
One column tells you who you haven't spoken to.
Last contact looks up the most recent date for each client on History and brings it back to the client list. Nobody maintains it. Log the call on History and the date moves.
Sort it oldest first and the top of the sheet is the list of clients nobody has called in a while. It's the cheapest useful number in the whole file.
Next follow-up works the same way. Give a row on History a follow-up date and the client list shows it, amber from that day until a newer row replaces it.
Days overdue does the same for money. It counts from the due date and clears the moment an invoice is marked Paid.
- Ashby JoineryJul 15, 2026
- Larchfield StudioAug 1, 2026
- Northgate LtdSep 4, 2026
- Brightwater BakerySep 24, 2026
Every figure on Summary is a formula, and each one says how it's counted.
Summary reads Clients, History and Payments. The only number typed on it is the days an active client can go without contact, 60 until you change it.
Each figure sits beside a line explaining it, so one that looks wrong can be checked in a minute. Add a client or mark an invoice Paid and the figures move.
Under the figures, a second table counts clients and payments by how each client found you. It shows which sources bring the clients who pay.
| Metric | Value |
|---|---|
| Active clientsClients with the status Active on Clients. | 2 |
| LeadsClients with the status Lead on Clients. | 1 |
| Follow-ups dueClients whose Next follow-up is today or earlier. It comes from the follow-up date on their latest row in History. | 2 |
| Active clients without contactActive clients whose Last contact is older than the days above. A client with nothing on History is not counted. | 1 |
| OutstandingAmounts on Payments with the status Sent or Disputed. | 1,250.00 |
| Overdue invoicesInvoices on Payments with Days overdue above zero. | 1 |
| Overdue amountThe total of those overdue invoices. | 900.00 |
| Paid this yearAmounts on Payments with the status Paid and a paid date in the current calendar year. | 3,600.00 |
A garage and an agency use the same file. Three things change between them.
Rename Your field, set the conversation types on Lists and note how each business gets paid. Copy the row closest to yours. If none fits and you can't name a field of your own, delete the column.
| Business | Your field | Types on History | How it gets paid |
|---|---|---|---|
| Design or marketing agency | Retainer end date | Call, Meeting, Proposal, Review | Monthly retainer: one row on Payments per month |
| Accountancy practice | Tax regime | Call, Email, Document request, Filing | Monthly fee, with one-off work invoiced on its own row |
| Garage | Registration plate | Booking, Repair, Inspection reminder, Call | Paid on collection, so the due date is the invoice date |
| Hair or beauty salon | Patch test date | Appointment, Cancellation, Message | Paid on the day; Payments holds packages and vouchers |
| Estate agency | Buying, selling or renting | Call, Viewing, Offer, Valuation | Commission when the sale completes |
| Builder or installer | Site address | Site visit, Quote, Job, Call | A row for the deposit, then a row for the balance on completion |
Your field holds one fact. For the full set of fields a client record can carry, and the report each one unlocks, see the 14 fields every SMB client record needs.
A client list holds personal data, and the GDPR applies to it in a spreadsheet too.
If your business is in the EU, or sells to people there, the GDPR covers the names, emails and phone numbers in this file. The points below summarise the articles that touch a spreadsheet. They are not legal advice.
Keep only the columns you use
Personal data has to be limited to what its purpose needs. It's the same rule as deleting a field nobody fills in: if you can't say why a column is there, remove it.
GDPRArticle 5(1)(c)
Delete former clients once you no longer need them
Personal data can be kept only as long as it's needed. The Former status finds those rows with one filter. Invoices follow tax law, which sets its own retention period.
GDPRArticle 5(1)(e)
Protect the file
Security has to match the risk, and the regulation names encryption as one measure. In Excel, Protect Workbook > Encrypt with Password, under File > Info, encrypts the file. In Google Sheets, share it with named people instead of anyone with the link.
GDPRArticle 32
Find everything about one person
People can ask for a copy of the data you hold about them and, in some cases, for it to be erased. Filter Clients, History and Payments by the client's name and you have all of it, which is one reason every sheet picks the name from the same list.
GDPRArticle 15 · Article 17
Leave health notes out
Data about health, such as an allergy noted at a salon, is a special category. The regulation allows it only under specific conditions, such as the person's explicit consent. If a note like that isn't needed, don't write it down.
GDPRArticle 9
A lost file can be a breach
If the file is lost, stolen or sent to the wrong person, you may have to report it to your data protection authority within 72 hours, unless the breach is unlikely to put anyone at risk.
GDPRArticle 33
The template is a file you keep. Syncek doesn't receive what you type into it, and nothing in the file connects to the internet. The regulation places responsibility on the business that keeps the list (Article 24), so no template makes anyone compliant on its own.
When the sheet stops being enough, it moves as it is.
A spreadsheet holds up for a long time, and volume is rarely what breaks it. It strains when two people edit it at once, or when a conversation stays in someone's inbox and never reaches History.
- Delete the empty rows below your last entry. They still carry the formulas, and an import reads each one as a record.
- Import each data sheet on its own. Every one is a header row and one row per record.
- Leave the shaded columns unmatched. They're calculated from the other sheets.
Syncek reads .xlsx files directly, up to 5,000 rows per import, and lets you match each column to a field. It's in early access, so the waitlist is the way in today. Get early access
Questions about the client tracker template
Is the client tracker template free?
Yes. It's a free .xlsx file with no sign-up and no email address asked for. There are no macros and no locked cells, so nothing stops working after a trial and you can change any column, sheet or dropdown option. The example rows use fictional companies with example.com addresses, so delete them before you add your own clients.
Does the client tracker template work in Google Sheets?
Yes. Upload the file to Google Drive and open it with Google Sheets, or open it in Excel or LibreOffice Calc. The dropdowns read their options from the Lists sheet, and the calculated columns use MAXIFS, SUMIFS, COUNTIF, COUNTIFS and TODAY. In Excel, MAXIFS needs Excel 2019 or Microsoft 365, so on Excel 2016 the Last contact and Next follow-up columns show an error instead of a date.
What columns does the client tracker template include?
Clients has company, contact person, phone, email, status, how they found you, what they bought, last purchase and your own field, plus four calculated columns: last contact, next follow-up, paid to date and outstanding. History has date, client, type, what happened, what is next and a follow-up date. Payments has one row per invoice with its amount, dates and status. Summary counts what needs doing.
How does the Last contact column in the template work?
It finds the most recent date on the History sheet for the client in that row and shows it on the Clients sheet. Log a call, email or meeting on History and that client's last contact moves by itself. Sort Clients by the column, oldest first, and the top rows are the clients nobody has spoken to lately. Names must match exactly, which is why History picks them from a dropdown.
Can I track client payments in the same spreadsheet?
Yes. The Payments sheet holds one row per invoice, with the amount, invoice date, due date, status, paid date and what it was for. Days overdue counts from the due date and clears once the status is Paid. On Clients, each row adds up what that client has paid and what is still outstanding, and Summary totals both. Keep the paid date even when an invoice is paid on time.
Is the client tracker template GDPR compliant?
No template is compliant on its own, because the GDPR makes the business that keeps the data responsible for how it is handled. The file helps you follow the rules: one row per client lets you find or delete everything about a person, the Former status shows rows you may no longer need, and Excel can encrypt it with a password. Syncek never receives what you type into it.
Can I import the client tracker template into a CRM?
Yes, one sheet at a time, because each data sheet is one header row and one row per record. First delete the empty rows below your last entry: they still hold the formulas, and an import reads each of them as a record. Leave the shaded columns unmatched, since they are calculated from the other sheets. Syncek reads .xlsx files directly, up to 5,000 rows per import.
When is a spreadsheet client tracker no longer enough?
Rarely because it gets long. A spreadsheet holds a lot of rows before size becomes the problem. It starts to strain when more than one person edits it at the same time, or when conversations stay in someone's inbox and never reach the History sheet. At that point the client and the conversation need to be one record that everyone sees, which is the job a CRM does.
Start with your first client row today.
One file, six sheets, and the four rules already built in.
Download the templateThe CRM this guide was written for.
Syncek is in early access and the public launch is close. Leave your email and the invitation reaches you first, with the launch offer.