How to track customers: how far Excel gets you and the signs it is time for a CRM

One of your salespeople resigns. The customer list is in the “customers_final_final2.xlsx” file on their computer; what they promised which customer, and who said “call us next month”, is either in their head or in the messages on their phone. The manager opens the file: there are hundreds of rows, but it is impossible to tell which leads are warm and which have been forgotten. This is exactly when the value of customer tracking shows. A well-kept record keeps the customer relationship going even when the employee leaves.

Customer tracking means recording the meetings held with a customer, the promises made, the quotes and the next step, and remembering them on time. In this article we first explain how to build a tidy tracking sheet in Excel, then where the sheet falls short and what to move across when you switch to a CRM.

How do you build a customer tracking sheet in Excel?

Starting with Excel is often a sensible decision: it is quick, everyone knows it and it needs no extra tools. What matters is setting the sheet up properly from the start. The columns below are a sound basis for a simple tracking sheet in which one row describes one customer.

Add three small rules and the sheet lasts much longer: a drop-down list for the stage column, conditional formatting for the next-step date and a weekly review of the sheet by one person. Without rules, a sheet soon turns into a mess that everyone fills in their own way. In the weekly review it is enough to ask three questions: how many rows have a next-step date in the past, are there any rows without an owner, and which customer’s stage has not changed for a long time? The answers show whether a sheet is alive or not.

Three Excel features make this sheet far more useful, and they are often overlooked:

• Data validation: Shows a drop-down list in the stage and owner columns instead of letting people type anything; it prevents duplicate entries caused by different spellings.
• Conditional formatting: Automatically turns a row red if the next-step date is earlier than today; overdue follow-ups catch your eye.
• Filter and sort: When you filter by owner or stage, each employee sees only their own customers.

How far does customer tracking in Excel get you?

Excel works well for a list of a few hundred customers used by one person or a very small team. You can filter, sort and produce a simple summary. The limit has less to do with the number of records than with the number of people using the file and the follow-up workload. Once the same file passes through two people’s hands, version problems appear, notes on the same customer contradict each other and nobody can tell who updated what. The lack of reminders turns the sheet into a passive record: it never tells anyone “call this customer” on its own.

Tracking done through WhatsApp and the phone’s contact list is weaker still. The conversation is instant and personal, but the record is tied to a person: the conversation with the customer stays on the employee’s phone, not with the company. When a promise is made, nobody else can see it. WhatsApp is indispensable for a quick exchange of information; but the tracking itself, meaning who said what and what the next step is, should be written into a shared record.

A fictional example: a small wholesale company employs two salespeople and an accountant. One salesperson promises a customer a discount and writes it only on WhatsApp. At the end of the month the accounts team issues the invoice without the discount, the customer objects and the salesperson is on leave. The problem is not the salesperson’s fault; it is that the promise never made it into a shared record. Had the same promise been written in the notes on the customer card, the accounts team could have seen it before invoicing.

Signs that the spreadsheet is no longer enough

If two or more of the following are happening to you, the sheet is no longer carrying the load:

• Two salespeople have called the same customer, and the customer noticed.
• You have to search your inbox to find out which version of a quote went to the customer.
• The file circulates by email and “which one is the latest?” has become a common question.
• When an employee leaves, the customer information leaves with them.
• To answer “how many open opportunities do we have this month?”, the manager merges sheets by hand.
• Nobody can say how long an opportunity has been waiting.
• Customer notes do not fit in the cells, and correspondence is scattered in different places.
• Everyone sees every customer; there is no way to control who should see what.

Is customer ledger software the same as a CRM?

The two are often confused, but they answer different questions. Customer ledger software tracks the money owed by and to a customer, in other words the accounting side. A CRM tracks the relationship itself: who spoke to whom, when and about what; which opportunity is open; which quote is pending. Seeing post-sale collections in the CRM is useful, because a salesperson wants to know about an outstanding payment before calling the customer; but it does not replace the accounting record. The two complement each other.

What moves across when you switch to a CRM? Step by step

1. Clean up the sheet. Merge duplicate companies, fix spelling differences and fill in missing owners.
2. Decide which fields to move. Company, contact, contact details, owner and stage are essential; old and unused columns go to an archive.
3. Define the stages. Map the typed-in entries in the sheet to a fixed list of stages.
4. Handle open opportunities and quotes separately. Enter ongoing deals into the new system by hand, checking each one.
5. Assign owners. Every card should have an owner; the manager distributes any that are left without one.
6. Run both side by side for a short time. After a set period, make the old sheet read-only and stop updating it.
7. Agree the import method in advance. How the data will be brought into the software should be clear before setup.

Customer tracking in YazSis solutions

YazSis CRM AI is the CRM that runs today in YazSis’s own support and sales centre. Contacts, installations, meetings, opportunities, quotes, collections and email history sit in one place on the company card. A prospect company can be claimed, released back into the pool or assigned in bulk, so no record is left without an owner. A salesperson calls the next company on the call list and records the outcome and the callback time. The opportunity board shows the stages from New to Won, and overdue opportunities are marked in red. Quotes move through a draft, awaiting approval, sent, accepted and rejected flow.

If you would rather run your sales flow inside your own portal, the Sales / Quotation & Customer Management module of KurumsalPortal brings together customer cards, meeting history, versioned quotes, approvals and an opportunity, sales and conversion report. Which tool suits you depends on the size of your team and your existing systems; if you need data to flow to and from systems such as an ERP, the scope is worked out in the needs analysis.

The decision to switch is a process decision before it is a software choice: who is responsible for the customer record, who will write the next step, and which questions will the manager answer from the record? If you have answers to these three questions, tracking will fall into place whichever tool you use. First test your current sheet against the eight signs above. Then write down the stages your sales process goes through and look at how the card and opportunity structure fits it on the YazSis CRM AI page.