How to track clients in Excel (and when to stop)

Most small businesses start tracking clients in a spreadsheet, and that is the right call. It is free, you already know how it works, and for the first thirty or so clients it is genuinely the best tool. The trouble is that nobody shows you how to lay it out, so it grows into something with fourteen columns, three tabs called "Copy of", and a nagging feeling that you have forgotten to call someone.

This guide gives you a layout that holds up, the few formulas that make it work, and an honest list of the signs that it is time to move on.

The layout: one row per client, one sheet for notes

Keep the main sheet flat. One row per client, one column per fact, no merged cells, no colour coding that only you understand. These columns cover almost every service business:

Then add a second sheet called Log with three columns: Date, Client, Note. Every call, email or meeting gets a line. This is the part people skip, and it is the part that saves you when a client says "but you told me in March that…". A flat log is searchable; a Notes cell with six months of history is not.

Three things that turn a list into a tracker

1. Make it a Table. Click anywhere in the data and press Ctrl+T (Cmd+T on a Mac). Excel now treats it as one object: new rows inherit the formatting and formulas, the header row stays visible when you scroll, and every column gets a filter. In Google Sheets the equivalent is Format → Convert to table.

2. Lock the Status column to a list. Select the Status column, go to Data → Data Validation, choose List, and type Lead,Quoted,Active,Dormant,Closed. Now nobody can type "actve" and break your filters. Do the same for Source.

3. Highlight what is overdue. Select the whole table, go to Home → Conditional Formatting → New Rule → Use a formula, and enter:

=AND($I2<TODAY(), $E2<>"Closed", $I2<>"")

adjusting I to your Next date column and E to Status. Pick a pale red fill. From now on, anything you have let slip is red the moment you open the file. Add a second rule with =$I2=TODAY() in pale amber for today's actions.

That is the whole system. Sort by Next date every morning, work down the red and amber rows, and log what you did.

Habits that keep it alive

• Update it at the moment, not at the end of the day. Put the phone down, type the log line, set the next action. Thirty seconds now or ten minutes of reconstruction on Friday.
• Every client has a next action, even if it is "check in, January". A row with no next date is a client you will forget.
• One file, one place. Not on the desktop and in Dropbox and emailed to yourself. If it lives in OneDrive or Google Drive you also get version history for free, which is your backup.
• Never delete a client. Set Status to Closed. Closed clients come back, and their history is worth having.
• Export a copy every month to a dated file. Spreadsheets get corrupted; it is rare, but it happens on the day you need it most.

When to stop

A spreadsheet stops being the right tool at a fairly predictable point. You will recognise some of these:

• You scroll to find people. Past about a hundred rows, Ctrl+F is your main interface, and the log sheet is thousands of lines long.
• You have forgotten a follow-up that cost you a job. The conditional formatting only works if you open the file. A tracker that does not tell you what is due today is a list, not a system.
• Two people edit it. Shared spreadsheets work until they do not: overwritten rows, a filter left on that hides half the clients, a "final v3" that is not the final one.
• You are on your phone more than your desk. Spreadsheets on a phone are miserable, and that is exactly when you want to see a customer's history: standing in their kitchen.
• The notes have moved out of the file. Some in the log, some in your email, some in WhatsApp. That is the moment the tracker stops being the truth.

None of this means you did it wrong. It means the spreadsheet did its job and you have more clients than it was built for.

Moving on without losing anything

The main reason people stay on a spreadsheet too long is the fear of re-typing everything. You should not have to. Any CRM worth using imports a CSV: in Excel, File → Save As → CSV, and the column names above (Name, Company, Email, Phone, Notes) are the ones importers look for. Keep the spreadsheet for a month as a safety net, then archive it.

What you gain is not features. It is one screen that shows what is overdue and what is due today the moment you open it, every note and quote tied to the person, and the same thing on your phone as on your desk. That is the whole difference between a list and a system.