Daily sales register template in Excel
Daily sales register template in Excel
At the end of the day, almost every small business has a rough idea of how much it sold, but very few can say exactly what was sold, to whom and how it was paid. That information stays in the seller's memory, on a loose piece of paper or in a notebook nobody else can read, and when month-end arrives it has to be rebuilt in a rush. What is left is a single global number, and a global number settles nothing: it does not say which product moves best, which payment method dominates, or what the average ticket of the business is.
This daily sales register template in Excel solves exactly that: a sheet with two hundred rows where every sale of the day is recorded, the line total is calculated automatically and the file keeps adding up the period. It includes the automatic line total, the payment method drop-down list, the period totals, the summary with the average ticket and a small table showing how much came in through each payment method.
⬇ Download the template (Excel .xlsx)What it is and who it is for
The daily sales register is the book of the day: the list where, sale by sale, you write down what was sold and how it was paid. It is not an accounting report or a financial statement; it is the raw detail that everything else is built from. Its value lies in consistency: if it is filled in every day, at the end of the month you have the complete history of the business without having to ask anyone what happened.
It is meant for small and mid-sized businesses that sell products or services and do not yet have an integrated invoicing system: neighbourhood shops, stationery stores, hardware stores, distributors, bakeries, workshops and service providers. It also helps in larger operations, as the day's backup when the main system is down or when you want to compare what the software says with what was actually collected at the counter.
One difference is worth clarifying, because it is often confused. This template answers what was sold and how it was paid, and its main contribution is the average ticket together with the breakdown by payment method. The sales-by-seller control is a different thing: that template measures targets, individual performance and commissions. The two complement each other but do not replace each other; if what you need is to settle commissions, you need the second one, and if what you need is to understand the day, this is the right one.
What the template includes
The file comes with the sheet already laid out, with the columns that depend on the data solved by formula. This is what you find when you open it:
| Section | What it brings and what it is for |
|---|---|
| Daily register | Two hundred rows to record the day's sales, with room to spare for several months of operation. |
| Automatic total | Multiplies quantity by unit price on every line, so you only type the two figures and the result appears on its own. |
| Payment method | Drop-down list with cash, transfer, card and credit, so the data is not left to the taste of whoever types it. |
| Period totals | Adds up the recorded sales and counts the completed lines, so the number of sales is also visible. |
| Summary | Shows the period sales, the number of sales, the average ticket and the units sold. |
| Sales by payment method | Small table that splits the total between cash, transfer, card and credit. |
The sheet has no macros or passwords: it is an ordinary Excel file with visible formulas that you can adapt to your operation without depending on anyone. If you need more than two hundred rows, copy the last one and drag the formulas down; if you run several locations, add a location column and filter by it.
The columns of the sheet
Every column has a specific job. The three that carry the most weight are the total, the payment method and the document, because almost all the later analysis comes from them. This is the full list:
| Column | What you write |
|---|---|
| Date | The day of the sale. It helps to use the same date format across the sheet so you can group later. |
| Document | The invoice, receipt or bill number. It is the key of the sale: you collect and claim against the document, not against memory. |
| Customer | The customer's name. For a counter sale with no name, you can write "counter" to make it clear there was no identified customer. |
| Product or service | What was delivered. Writing it the same way every time allows you to group by product later. |
| Quantity | How many units were sold on that line. |
| Unit price | The price of a single unit, before discounts. |
| Total | Automatic: quantity times unit price. It is the value of the line. |
| Payment method | Drop-down list: cash, transfer, card or credit. |
| Seller | Who served the sale, so it can be matched later with the sales-by-seller control. |
| Notes | Discounts, agreements with the customer, pending deliveries and any detail that explains the line. |
How the calculation works, with an example
The only formula running on each line is quantity times unit price. Everything else is addition: the period total, the number of sales and the units. With those three figures the summary builds the average ticket, which is simply sales divided by the number of sales. Let us look at one specific day:
| Day | Document | Customer | Product | Quantity | Unit price | Total | Payment method |
|---|---|---|---|---|---|---|---|
| 3 | Invoice 201 | Almacén La Esquina | Notebooks | 2 | 8,500 | 17,000 | Transfer |
| 3 | Receipt 202 | Counter | Ream of paper | 1 | 22,000 | 22,000 | Cash |
| 4 | Invoice 203 | Ferretería Sur | Archive boxes | 5 | 12,000 | 60,000 | Credit |
When the period closes, the file adds the three lines and shows sales of 99,000, three recorded sales and an average ticket of 33,000. In the small payment method table, those same 99,000 are split like this: 22,000 in cash, 17,000 by transfer and 60,000 on credit. That is where a reading appears that a global total would never give you: about sixty out of every hundred sold in the period were still waiting to be collected. That single line is reason enough to keep the register every day.
Step by step to start today
- Open the file and check the header: the start date of the period and the lists that feed the payment method are there.
- Set the format of the money columns and of the date column, so that every record looks the same.
- Record the first sale: date, document, customer, product, quantity and unit price. The total appears on its own.
- Choose the payment method from the drop-down list and write down the seller. Do not leave those two cells blank.
- At the end of the day, compare the file total with the money that actually came into the till or the bank account.
- At the end of the week or month, look at the summary: sales, number of sales, average ticket and the breakdown by payment method.
Tips and common mistakes
- Writing everything from memory at the end of the day: the later a sale is written down, the more detail is forgotten. Ideally you record it on the spot or, at the latest, when the shift closes.
- Leaving the payment method blank: without that piece of data the breakdown by payment method is incomplete and half the value of the file is lost.
- Writing the customer in different ways: "Almacén La Esquina", "Almacén la esquina" and "La Esquina" are three customers for any filter. One single name per customer is the rule.
- Mixing up the unit price and the total: if a document has several lines, each product goes on its own row with its own price; the unit price is always the price of one single unit.
- Using the notes column for everything: notes explain the line, they do not replace fields. If a piece of data matters, it deserves its own column.
- Typing the currency symbol inside the cell: values go in as clean numbers; the symbol belongs to the column format, not to the data.
When it is worth moving to sales software
The template handles daily control very well when the volume is moderate and one person keeps it. Its limit is clear: the file lives on the computer of whoever runs it, it does not connect the sale with inventory or with receivables, and everything depends on someone sitting down to type it in. When the business grows and several people start passing through the same counter, that discipline becomes the most fragile link. A system such as Kardex Tauro records the sale once and from there come inventory, receivables and reports, so the summary of the day is already built whenever you want to look at it.
To put it plainly: if you sell a handful of lines a day, the template is enough and it is the right step to start getting organised. Software is justified when the data cannot depend on one person's memory, when you run several locations or when you need to know at any moment how much you have sold without waiting for someone to close the sheet.
The daily sales register is the memory of the business: what is not written down is lost. Start today, even with this week's sales, and in a few days you will have something no notebook ever gave you: knowing exactly what you sell, how much you sell and how customers pay. Download the template for free and open the book of the day.
⬇ Download the template (Excel .xlsx)
