How to build a customer record sheet in Excel
A customer record exists to decide something, not to file everything you know. Which fields earn their place, which to leave out, and why the good-looking sheet is usually the one nobody fills in.
Syncek Team · CRM reference library
/ 4 min read / Art. #74
To build a customer record sheet in Excel, use one row per customer and one column per field, with four groups of fields: identification, commercial, operational and the date of last contact. Each field has to help you decide something: who to call, what to offer, how much credit to extend. A field that feeds none of those decisions is work to fill in and noise to read.
Which fields should a customer record sheet have?
Identification: company name, contact person, phone, email. The company name has to be written identically everywhere, because it is what everything else joins on.
Commercial: how they found you, what they bought, when they last bought.
Operational: whatever is specific to your business and changes what you do. In a garage it is the registration plate; in an accountancy practice it is the tax regime. This is the field nobody can copy from a template, and it is usually the most useful one on the sheet.
Date of last contact. If you can only have one field besides the name, this is it.
Which fields look useful and are not?
Free-text notes with no date. After a year they are an unreadable block and nobody knows what is still true. History belongs in a second table where each row is one interaction with its own date, not in a cell that grows.
Fields that repeat what is already somewhere else. If the address lives in your invoicing, copying it here guarantees the two will diverge.
Fields "for later". A field left empty for a year is a field you delete.
Both lists in one place:
| Field | On the sheet? |
|---|---|
| Identification: company name, contact person, phone, email | Yes, with the company name written identically everywhere, because everything else joins on it |
| Commercial: how they found you, what they bought, when they last bought | Yes |
| Operational: whatever is specific to your business, such as the registration plate in a garage or the tax regime in an accountancy practice | Yes, and it is usually the most useful field on the sheet |
| Date of last contact | Yes: if you can only have one field besides the name, this is it |
| Free-text notes with no date | No: history belongs in a second table, one row per interaction with its own date |
| Anything that repeats what is already somewhere else, such as the address in your invoicing | No: the two copies are guaranteed to diverge |
| Fields "for later" | No: a field left empty for a year is a field you delete |
What test should a new field pass?
One question: who reads this, and what changes because of it? If there is no answer, the field is not underused, it is unjustified. It is the same test that decides which field types make sense, and it is what separates a ten-field record that is always complete from a thirty-field one that is always half done.
Should a customer record sheet look like a form?
A sheet designed as a form, with boxes and colours and merged cells, reads well and cannot be filtered, sorted or counted. Merged cells break any pivot table, and Microsoft's own PivotTable guide says to avoid them. Colour says nothing to a formula. In Excel the sheet that works is one row per customer, one column per field. If you want to see a single customer in something pleasant to read, that is a view built on top of the data, never the way the data is stored. Build the underlying table first and think about appearance afterwards.
When does a customer spreadsheet stop being enough?
When two people need to write at once, when you need to know what the record said six months ago, or when somebody has to be reminded on a date. None of those is solved by more fields, and none of them depends on how many customers you have. Until then, a short record kept in order comfortably beats a complete form nobody updates.
Frequently asked questions
What is a customer record sheet for?
Deciding something, not storing everything. Who to call, what to offer, how much credit to extend. That is the whole purpose, and it is what makes a record short: every field feeding none of those decisions is work when you fill it in and noise when you read it. A ten-field sheet that is always complete is worth more than a thirty-field one that is always half done.
What makes a field unjustified rather than underused?
One question decides it: who reads this, and what changes because of it? If there is no answer, the field is not waiting for someone to start using it, it was never justified. The same test retires fields, not only adds them. A field left empty for a year is a field to delete, and a field added "for later" is a field you are deleting in a year.
Which fields almost always earn their place?
Four groups. Identification: company name, contact person, phone, email. Commercial: how they found you, what they bought, when they last bought. Operational: whatever is specific to your business and changes what you do. And the date of the last contact, which is the one to keep if you can only keep one field besides the name.
Which field on a customer sheet can never come from a template?
The operational one, and it is usually the most useful field there. A garage records the registration plate; an accountancy practice records the tax regime. No template knows which, because the field exists to change what you actually do next, and what you do next is specific to the trade. Everything a template can give you is the part that matters least.
Should a customer sheet repeat data that lives in your invoicing?
No. Copying the address across guarantees the two versions will diverge, and then neither is trustworthy, because nothing says which was edited last. Keep the field where the work happens and join on something stable instead. That is what the company name is for, which is why it has to be spelled identically everywhere rather than approximately.
Should a customer record sheet look like a form?
Not the stored version. A sheet designed as a form, with boxes, colours and merged cells, reads nicely and cannot be filtered, sorted or counted: merged cells break any pivot table and colour says nothing to a formula. One row per customer and one column per field is the working shape. A pleasant single-customer view is something built on top of that, never the way the data is kept.
Does the number of customers decide when to leave a spreadsheet?
No, and that is the useful part. The three limits arrive regardless of size: two people needing to write at once, needing to know what the record said six months ago, and needing somebody reminded on a date. None of them is solved by adding fields, and none waits for a row count. Until one of them costs you something, a short well-kept sheet beats a complete form nobody updates.