POS system in Excel: a sales and cash-drawer template that works, where it breaks, and what replaces it
Plenty of small shops start with a POS system in Excel. A product list with prices, a sheet where the day's sales go, a cash-drawer count at closing, and a daily sales report built from a pivot table. For a market stall, a pop-up or a shop with one till and one person behind it, that is a perfectly sensible way to begin.
It is also worth being clear about what it is. A spreadsheet is not a till. It cannot scan a barcode at speed, open a drawer or print a receipt. What it can be is a very good record of what the till did — and most of the value in a point of sale system is exactly that record.
This is the honest version: a point of sale Excel structure that is worth copying, the exact place each part of it fails, and what your options are when it does.
The POS spreadsheet worth copying
Most free point of sale Excel templates are a single sheet with one row per day: date, total sales, cash, card. That records a number you cannot check. Start from the sale line instead, and derive every total from it.
Sheet 1 — Products. One row per thing you sell.
| SKU | Product | Price | Tax rate | Category |
|---|---|---|---|---|
| COF-250 | House coffee beans 250g | 9.50 | 0% | Coffee |
| MUG-01 | Ceramic mug | 12.00 | 20% | Homeware |
| CRD-05 | Greeting card | 3.25 | 20% | Stationery |
Sheet 2 — Sale lines. One row per item sold, grouped by receipt number. This is the heart of it.
| Receipt | Time | SKU | Qty | Unit price | Discount | Cashier |
|---|---|---|---|---|---|---|
| 1041 | 09:14 | COF-250 | 2 | 9.50 | 0.00 | Hina |
| 1041 | 09:14 | MUG-01 | 1 | 12.00 | 1.20 | Hina |
| 1042 | 09:31 | CRD-05 | 3 | 3.25 | 0.00 | Hina |
Sheet 3 — Tenders. How each receipt was paid. A receipt can have more than one row, because people pay part cash, part card.
| Receipt | Tender | Amount |
|---|---|---|
| 1041 | Card | 29.80 |
| 1042 | Cash | 9.75 |
A receipt is balanced when its lines and its tenders agree:
=SUMPRODUCT((Lines[Receipt]=[@Receipt]) * (Lines[Qty]*Lines[Unit price] - Lines[Discount]))
- SUMIFS(Tenders[Amount], Tenders[Receipt], [@Receipt])
Anything other than zero is a keying mistake, and now you can find it.
Sheet 4 — Drawer. One row per till per trading day. This is the cash-drawer close that most shops do on the back of an envelope.
| Date | Till | Opening float | Cash sales | Cash refunds | Paid out | Expected | Counted | Variance |
|---|---|---|---|---|---|---|---|---|
| 2026-09-15 | Till 1 | 150.00 | 412.75 | 12.00 | 20.00 | 530.75 | 528.50 | −2.25 |
Cash sales and cash refunds come from Sheet 3 with a SUMIFS. Expected is float plus cash sales
minus refunds minus paid-outs. Counted is the one number somebody types, at close, from the drawer.
Variance is the difference, and a daily sales report built on this sheet tells you not just what you
sold but whether the cash agrees.
Sheet 5 — Daily sales report. A pivot of the sale lines by day, product and cashier. Never typed.
The principle is the same as in any good record: totals are derived from lines, and the only number anyone types at the end of the day is what they physically counted. If you already track stock this way, the movement log in our inventory management in Excel guide slots straight in: every sale line is a stock movement out.
Where it breaks
1. Nobody types sales into a spreadsheet at the counter
This is the first and biggest failure. At the counter, with a queue, nobody opens a spreadsheet and keys in a receipt line by line. So the lines get written later, from memory or from a paper pad, or the shop gives up and records one total per day. At that point Sheet 2 is fiction and everything derived from it is decoration. The spreadsheet can only ever be as good as the moment of sale, and it is not present at the moment of sale.
2. Returns and exchanges have nowhere to go
A refund is not a negative sale. It needs to point back to the original receipt, put the item back into stock (or not, if it is damaged), take cash out of the drawer or refund a card, and apply whatever discount the original sale had. An exchange does all of that and a new sale at once. In a spreadsheet all of this becomes a negative row with a note, unlinked to anything, and the next month nobody can tell whether receipt 1041 was refunded, exchanged, or both.
3. The drawer close can be edited after the fact
The cash-drawer sheet is only useful if the close is final. In a spreadsheet, the counted figure and the day's cash sales can both be changed tomorrow, by anyone with the file. A variance can be made to disappear by editing a sale line, and there is no trail showing it happened. For a shop owner that is the difference between knowing the till was short and suspecting it.
4. Discounts need a second person, and a spreadsheet has no second person
Every shop has a rule like "only the manager can give more than 10% off" or "voids over a certain amount need approval". A spreadsheet cannot enforce it. The Discount column accepts any number from anyone. The rule lives in staff training, which means it lives nowhere once the manager is not in the shop.
5. Two tills, two shops
Two tills mean two people adding sale lines at the same moment, and shared spreadsheets do not lock rows — simultaneous edits can overwrite each other silently. A second shop is worse: two files with no combined view, or one file with a store column and no safe way for each shop to enter its own sales. Stock transfers between shops become two rows that never quite reconcile.
What a real one has that a spreadsheet doesn't
We keep a hand-counted inventory of what retail POS products actually ship, built from 60 products. Here is how often the core capabilities appear:
| Capability | Products (of 60) |
|---|---|
| Cashier sell and checkout | 60 |
| Actual stock visibility, with movement history | 58 |
| Reports that drill down to the source sales | 51 |
| Customer context — history, loyalty | 44 |
| Tender handling — cash, change, split payments | 36 |
| Multi-location and transfers | 34 |
| Returns, refunds and exchanges | 29 |
| Register, shift and cash close | 18 |
| Staff permissions and approvals | 16 |
| Purchasing and receiving | 13 |
Two things stand out. First, checkout is universal — all 60 products are built around the moment of sale, which is exactly the moment a spreadsheet is absent from. Failure 1 is the whole reason POS software exists.
Second, look at the bottom of the list. The register close and staff approvals — failures 3 and 4 — are documented by fewer than a third of products. That does not mean they are rare. It means marketing pages rarely talk about them, because a counted cash close is not exciting to advertise. They are still what decides whether the numbers can be trusted. If you evaluate POS software, ask about them directly; if you build one, do not let them fall off the list because nobody mentions them.
Your three options, honestly
Keep the POS spreadsheet
Correct for a market stall, a pop-up or a small shop with one till, one person on it, and mostly cash or card-reader payments. Use the five sheets above, do the drawer close every day, and treat the file as the record of what happened rather than the thing that makes it happen.
Stop when you need a second till, when refunds become regular, or when you notice you cannot say whether yesterday's drawer was short.
Buy software
Correct for most shops, and cheaper than people expect — see what a POS system actually costs, including the lines that are not the software. The free POS software roundup covers what the no-cost tiers include and where they stop, and cash register vs POS system draws the line between the two honestly.
The real cost is fit: their idea of a return, a drawer close, a discount rule. For most retail that is fine. It stops being fine with unusual workflows — consignment, made-to-order items, a shop that is also a café.
Build it
A coding agent can now produce a working till in days. It will produce a checkout screen, a product grid and a sales chart very convincingly. What it tends to skip is everything below the chart: refunds that reverse what the sale wrote, a drawer close that locks, approvals at the point of work. When three AI models built the same POS spec, the checkout screens were not what set them apart. The strongest work was below them: a return that reverses money, stock and loyalty points in one step, and a rule that nobody can approve their own action. The full write-up walks through each build.
If you build it, build these first
- Sale lines and tenders as the source of truth. Every receipt balanced, every total derived.
- Returns and exchanges linked to the original receipt, reversing the stock, cash and discount the sale wrote.
- A trading-day lifecycle for each till. Opening float, cash in and out, counted close, variance — and a close that cannot be edited once committed.
- Approvals at the point of work. A second person signs off a big discount or void there and then, not in an admin screen later.
- Stock moved by every sale line. The till and the stock count must agree without anyone copying numbers between them.
Loyalty, multi-store, purchasing and integrated card terminals are real scope, and optional for a first version.
For what actually matters when choosing one, see POS system for small business, counted across the same 60 products. And if you are building with a coding agent, the Retail POS planner is the spec we hand ours: the sale, return and drawer lifecycles, written so the agent cannot quietly skip them.