Customer database template in Excel
Customer database template in Excel
When a business has twenty customers, the information lives in the head of whoever sells. When it has a hundred, it lives in a notebook, in two different spreadsheets and in message threads nobody can search later. The day someone needs to know how much a customer owes, how much credit they have left or who is in charge of the account, finding that answer becomes a job of its own. A customer base that is actually in order answers those questions in a single lookup.
This customer database template in Excel gathers the whole catalogue into one sheet: contact details, assigned salesperson, credit limit, current balance, and the two columns the workbook fills in by itself, available credit and status. The file is free, it opens in Excel and in any compatible spreadsheet, and it comes with forty customer records ready to fill in and the formulas already built. You write the limit and the balance; the workbook tells you how much each customer still has available and what status they are in.
⬇ Download the template (Excel .xlsx)What a customer database is and who it is for
A customer database is the single record where the information of every person or company that buys from the business is kept: name, how to reach them, who serves them, how much credit they are authorised to use and how much they owe right now. It is not a contact agenda and it is not a mailing list. It is the catalogue used to decide who can buy on credit and for how much. Its value shows up the moment a business sells on terms, because then the balance stops being a detail and becomes the backing for the next sale.
It is useful, first, to sell on credit without running out of backing. If a customer has a limit of 3,000,000 and already owes 2,400,000, the template warns that only 600,000 are still available and stops an order from going out that would be hard to collect later. It is useful, second, to spread the portfolio: by looking at the assigned salesperson and the balance of each record, it is easy to see which accounts are being served and which were left without an owner.
It fits any small or medium business that sells on terms or serves regular customers: distributors, hardware stores, neighbourhood shops, workshops, service providers and trading companies. It asks for no accounting knowledge and no special software, and the values are written without a currency symbol.
How it differs from a contact agenda
A contact agenda stores names and phone numbers so you can call someone. A customer database stores what you need in order to decide: how much credit that customer has, how much they owe and who is responsible for the account. The difference is not size but purpose.
It is not the receivables ledger either, although the two are neighbours. The receivable tells you, movement by movement, what was invoiced and what was paid. The customer database keeps the summary figure for each person: the balance. That balance is exactly what gets collected later, which is why the two tools should always tell the same story. When they do not, the usual culprit is a database that nobody updates.
What the template includes
The workbook has one sheet of customer records and a summary block. This is what you will find when you open it:
| Item | What it brings |
|---|---|
| Forty customer records | Rows ready to hold the whole catalogue, from the name or company name down to the internal notes. |
| Customer code | A short identifier to cite the record without confusion when two names look alike. |
| Credit limit | The amount authorised for each customer; you write it and it is the ceiling for sales on terms. |
| Current balance | What the customer owes today; it changes as invoices are issued and payments come in. |
| Available credit (automatic) | The limit minus the balance: what can still be sold on credit without going over the ceiling. |
| Status (automatic) | Classifies each record as no limit, at the limit or limit available, according to the limit and the balance. |
| Last purchase | The date of the most recent purchase, so you can see at a glance which customers have gone quiet. |
| Totals | Sums of the limits and the balances of the whole base, to see how much credit is authorised and how much is out there. |
| Base summary | Counts the registered customers, shows the total limit, the total balance and how many customers are at the limit. |
The columns of the sheet
Each row is a customer and each column is one piece of information. These are the columns, in the order they appear:
| Column | What is written |
|---|---|
| Code | A short, unique identifier; it is best to follow a series, for example CUS-001, CUS-002. |
| Name or company name | The full name of the person or the company, exactly as it appears on the document. |
| Document | The identification number used for invoicing; it helps avoid duplicate records. |
| Phone | The direct contact number, with the area code when the customer is in another city. |
| The address where invoices, quotes and payment reminders are sent. | |
| City | Where the customer receives goods or has their address, useful for routes and visits. |
| Assigned salesperson | Who serves the account; this person owns the relationship and the collection. |
| Credit limit | The maximum amount authorised for buying on terms; it is typed by hand. |
| Current balance | What the customer owes as of the date of the check; it is also typed by hand. |
| Available credit | Automatic: the limit minus the balance. It is never shown as a negative, even if the balance goes over the limit. |
| Status | Automatic: no limit when nothing is authorised, at the limit when the credit is used up, and limit available in every other case. |
| Last purchase | The date of the most recent purchase, to see whether the account is still alive. |
| Notes | Free space for agreements, special conditions or any detail that has no column of its own. |
The two automatic columns are not edited: available credit and status. If someone overwrites them with a typed value, the base stops warning and loses exactly what makes it useful.
How the status is calculated
The rule is simple and worth knowing before filling in the records. First the available credit is calculated by subtracting the balance from the credit limit. When that result is above zero, the customer still has room and the status reads limit available. When the limit is authorised but the result reaches zero or less, the status reads at the limit: no further sale on terms should go out until a payment comes in. And when the limit is zero, the status reads no limit, which is the case of customers who pay cash or whose credit has not been approved yet.
The three statuses work like an office traffic light: green to keep selling on terms, amber to collect before invoicing, and grey for the customer whose credit has not been approved.
Worked example
Suppose Corner Store has a credit limit of 3,000,000 and a current balance of 2,400,000. The template does the subtraction and the record looks like this:
| Concept | Value |
|---|---|
| Credit limit | 3,000,000 |
| Current balance | 2,400,000 |
| Available credit (automatic) | 600,000 |
| Status (automatic) | Limit available |
With that result the salesperson knows that up to 600,000 can still be shipped on credit and not a cent more. If the customer asks for an order of 800,000, there are three orderly ways out: collect a payment first to free up credit, cut the order down to what is available, or request a limit increase from whoever authorises it.
The same exercise with other numbers shows the other two statuses. A customer with a limit of 1,000,000 and a balance of 1,000,000 is at the limit. And a newly registered customer with no authorised limit is at no limit even though they owe nothing: the template is not punishing them, it is simply saying that credit has not been approved yet.
The base summary closes the picture: it adds up the limit of every record, adds up the balance and counts how many customers are at the limit. If the total balance sits very close to the total limit, the business has almost all of its credit out there and has little room for new sales on terms. That is the figure to watch month after month.
How to use the template step by step
- Download the file and save it with the year in the name, for example customer-base-2027.xlsx, so it does not get mixed up with earlier versions.
- Write the customers who already buy on credit first and leave the cash customers for later: that way the base is useful from day one.
- Build the code of each record with a clear series (CUS-001, CUS-002) and check that it does not repeat; the code is the key of the record.
- Fill in the authorised credit limit and the current balance of each customer, taking the balance from the day’s receivables list and not from the salesperson’s memory.
- Read the whole status column and write down the customers sitting at the limit: those are the collection calls of the week.
- Update the base every time a payment comes in or an invoice goes out: a balance left five months behind is of no use at all.
Tips and common mistakes
- Keeping the base on one person’s computer. If the file lives on a single machine, the information leaves with them when they go on holiday. Store it where the team can reach it.
- Duplicating customers. Two records for the same Corner Store, one written with the name and one with the document number, throw the totals off. Search by document before creating a record.
- Leaving the balance untouched. The balance is the piece that drives every calculation: an old balance produces a false status, almost always a more optimistic one than reality.
- Authorising limits with no criteria. A limit is a risk the business takes on; it should be set from the customer’s payment record, not from what they ask for at the counter.
- Mixing up the limit and the term. They are two different things: the limit says how much, and the term or payment method says when. The template controls the how much; the when is agreed separately.
- Not recording the last purchase. That date is what shows which customers stopped coming, and it is often the first sign of a service or price problem.
When to move to software with Kardex Tauro
Excel works very well while the base is a single file, the person filling it in is one and sales are recorded at a single till. The limits appear when there are several branches with different lists, when the credit limit has to change with every invoice in real time, and when three people need to see the real balance at the same moment. That is where duplicate versions, stale balances and customers who still get credit they had already used up come from.
That is the moment to consider specialised software such as Kardex Tauro, which centralises customers, sales and receivables and deducts the credit as each invoice is issued, with no chains of files sent by email. The work done today is not wasted: the codes, the limits and the habit of checking the status of every account are exactly what a system needs to start with ordered data.
Download the template, register your credit customers today and read the status column: that is the warning that prevents a sale that could not be collected later.
⬇ Download the template (Excel .xlsx)
