Inventory management in Excel: a working template, where it breaks, and what comes next
Almost every stock system starts as an inventory management system in Excel, and for a while that is the correct decision. Excel is free, it is on the machine you already own, and you can change it without asking anyone. If you have a few hundred SKUs in one location and one person touching the numbers, good Excel inventory tracking will outperform most software you could buy.
Then it stops working, usually in the same five ways and usually all at once.
This is the honest version: an Excel inventory management structure worth copying, the specific point at which each part fails, and what the alternatives actually cost you. No pretence that you need software on day one, and no pretence that a stock management system in Excel lasts forever.
The Excel inventory template worth copying
You can download a hundred free inventory spreadsheet templates and most of them share the same fatal design. Rather than hand you another one, here is the structure — it takes ten minutes to build in Excel or Google Sheets, and unlike the free templates it will not quietly corrupt your numbers.
Most free inventory management Excel templates fail because they are one sheet — a list of items with a Quantity column that people overwrite. That single design decision causes almost every problem later, so start with three sheets instead.
Sheet 1 — Items. One row per SKU, and this sheet never contains a live quantity.
| SKU | Name | Category | Unit | Cost | Price | Location | Reorder point |
|---|---|---|---|---|---|---|---|
| TSH-BLK-M | T-shirt, black, M | Apparel | ea | 4.20 | 14.00 | Main | 12 |
| MUG-CER-01 | Ceramic mug 350ml | Homeware | ea | 2.85 | 9.50 | Main | 24 |
| CBL-USBC-2M | USB-C cable 2m | Accessories | ea | 1.40 | 7.00 | Annexe | 40 |
Sheet 2 — Movements. One row per event. You never edit a quantity anywhere; you add a row here.
| Date | SKU | Type | Qty | Reason | Who | Ref |
|---|---|---|---|---|---|---|
| 2026-08-01 | TSH-BLK-M | receipt | +48 | Delivery | AR | PO-1043 |
| 2026-08-02 | TSH-BLK-M | sale | −3 | Till | Shop | — |
| 2026-08-04 | TSH-BLK-M | adjustment | −2 | Damaged in stockroom | JM | — |
| 2026-08-05 | TSH-BLK-M | count | +1 | Stocktake variance | JM | CNT-08 |
Type is receipt, sale, adjustment, transfer or count. Quantity is positive for stock in,
negative for stock out. Ref is the PO number, invoice number, or count session.
Sheet 3 — On hand. Derived, never typed:
=SUMIFS(Movements[Qty], Movements[SKU], [@SKU])
Add a reorder flag beside it:
=IF([@OnHand] <= [@ReorderPoint], "REORDER", "")
That is the whole system, and the important part is not the formulas. It is that on-hand stock is a calculated result, not a field anybody can type into. Every quantity change leaves a dated row saying who did it and why.
If you copy nothing else from this page, copy that. It is also, exactly, the thing that separates real inventory software from bad inventory software — and the reason most AI-generated stock apps are broken on arrival is that they skip it and make quantity a directly editable number.
Where it breaks
A spreadsheet built that way will carry you further than most. It still ends, and it ends predictably.
1. Two people open it at once
This is usually the first failure and the one that destroys trust fastest. Shared editing in Excel or Google Sheets does not lock rows the way a database does. Two people receiving different deliveries at the same time will overwrite each other, and the loss is silent — no error, no conflict prompt, just a number that is quietly wrong.
You notice weeks later when the shelf says 8 and the sheet says 14, and by then there is no way to reconstruct which entry went missing.
Excel's own protections do not save you here. Shared workbooks, co-authoring and change tracking all assume the conflict is visible — two people editing the same cell. Inventory conflicts are rarely that. They are two people appending different rows and one save winning, or a filtered view hiding the row somebody else just added. Nothing flags it because, as far as Excel is concerned, nothing went wrong.
2. Nobody can explain a discrepancy
Even with a movement log, a spreadsheet has no enforced history. Anyone can edit a past row. Delete one movement and every subsequent balance shifts, with no trace that it happened.
The moment a discrepancy costs real money — a supplier dispute, a shrinkage investigation, an insurance claim — you need to prove what the stock level was on a given date and who changed it. A spreadsheet cannot do that, because its history is editable by design.
3. The count becomes a guess
A physical stocktake in a spreadsheet is someone walking the stockroom with a printout, writing numbers in a margin, then typing them in. There is no variance review, no lock while counting, no record of what was counted versus what was skipped, and no auditable moment where the count is committed.
So the count either gets typed straight over the on-hand figure — destroying the movement history — or it gets entered as a giant adjustment with the reason "stocktake", which tells you nothing.
4. Purchase orders have no states
In a spreadsheet a PO is a tab with a list on it. Real receiving is not a single event: an order is drafted, sent, partially received, back-ordered, and eventually closed. Stock arrives in two deliveries, one item short, one substituted.
With no states, people fix it by editing quantities directly — which puts you back to a typed-in number with no history, and undoes the one good decision you made at the start.
5. It cannot be in two places
The moment you have a second location, a spreadsheet forces a choice: duplicate the whole file per site and lose the consolidated view, or add a location column and lose the ability to let each site edit safely. Transfers between locations become a pair of manual adjustments that nobody reconciles, and stock in transit exists nowhere.
What a real one has that a spreadsheet doesn't
We maintain a hand-counted inventory of what retail stock systems actually ship, tabulated across 120 retail-only sources. Ranked by how often each capability appears, the top of that list is remarkably consistent — and it maps almost exactly onto the five failures above.
| Ranked by frequency across 120 retail sources | Count |
|---|---|
| On-hand stock by item and location — derived from movements, never a free-edit field | 32 |
| Item/SKU master — categories, units, cost and sale price | 24 |
| Purchase-order lifecycle — draft → sent → partially received → closed | 24 |
| Physical stocktake — count session, variance review, commit, lock, export | 23 |
| Reports and export files — real files, not toast-only exports | 21 |
| Receiving against POs and ad-hoc — with discrepancies and backorders | 18 |
| Low-stock reorder queue — reorder points, min/max, create-PO handoff | 17 |
| Store-to-store transfers — send, receive, variance, history | 15 |
| Barcode and label printing | 14 |
| Stock command dashboard — with drill-downs, not decorative widgets | 14 |
| Immutable movement ledger — before/after, reason and actor on every change | 12 |
These are counts of sources in which each capability was found, ranked. They are not percentages of products, and we do not present them as such — the denominator is reviewed sources, not distinct vendors.
Read the list next to the failures and the pattern is obvious. The most common capability in real retail systems is the one your spreadsheet gives up first: stock derived from movements rather than typed in. The stocktake, the PO lifecycle and the movement ledger are all mechanisms for answering "why does the shelf disagree with the system" — the exact question a spreadsheet cannot answer.
What about the free inventory software?
Before the three options, the question that usually comes next: if Excel runs out, is there free inventory management software that does this properly?
Partly. The free tiers are real, and for a small single-location operation several of them are genuinely enough. What you should know before you migrate:
- Free tiers cap the thing you will hit first. Usually item count, locations, or users — and users is the one that matters, because the reason you are leaving Excel is that a second person needs access.
- Most free tiers include the ledger. This is the important part. Even a limited free plan usually derives stock from movements rather than letting you type over it, which is the single biggest upgrade over a spreadsheet.
- Export is the thing to check first, not features. Ask what you can take with you and in what format before you put two years of movement history into it. A system you cannot leave is a worse trap than a spreadsheet.
- Open-source is free like a puppy. Self-hosting means you own backups, updates and security patching. That is a real job. If nobody on your team wants that job, it is not free.
If a free tier fits your operation, take it. It beats both a spreadsheet and a rushed custom build. The three options below are for when it does not.
Your three options, honestly
Keep the inventory management system in Excel
Correct if: one location, one person editing, low SKU count, and no external party will ever ask you to prove a historical stock level. Add the three-sheet structure above and you have removed most of the risk you can remove for free.
Stop when a second person needs to edit it, or the first supplier dispute arrives.
Buy software
Correct for most retailers, most of the time. Off-the-shelf inventory systems are mature, cheap relative to your time, and someone else maintains them.
The real cost is not the subscription — it is fit. You will adopt their model of a stocktake, their PO states, their idea of a transfer. If your operation matches, that is a bargain. If you have a genuinely unusual workflow — consignment stock, kit assembly, per-batch expiry, a shared stockroom across trading entities — you will pay in workarounds forever, and workarounds are where the manual spreadsheets creep back in.
Build it
Two years ago this was a bad idea for anyone without a development budget. It is now a serious option, because a coding agent can produce a working stock system in a day or two.
The catch is that it will produce the wrong one unless you tell it exactly what to build. Left to guess, an agent builds a quantity field you can type into, a stocktake that is a plain table, and purchase orders with no receiving state. It reproduces the spreadsheet's failures in a database, which is worse than the spreadsheet — because now the flaws are hidden behind a UI that looks finished.
Feature parity is not the hard part. Agents get the visible features right. What they miss is the substrate: the movement ledger, the count-commit-lock cycle, the PO state machine, and the rule that no quantity is ever directly writable.
If you build it, build these first
In order, from the ranked list above:
- The movement ledger, before anything else. Every quantity change writes a row with before, after, reason and actor. Nothing edits a stock number directly — not the UI, not an import, not an admin screen.
- On-hand as a derived value. Same rule as the spreadsheet formula, enforced by the schema rather than by discipline.
- A stocktake that is a session, not a form: open, count, review variance, commit, lock, export. The lock is what makes it auditable.
- Purchase orders with real states, including partial receipt and backorder, because that is what actually happens on a loading bay.
- A low-stock queue that hands off to a PO — not a dashboard card that someone is supposed to notice.
Everything below that on the list — transfers, barcodes, dashboards — is genuine scope, and genuinely optional for a first version. Build them when the operation needs them, not because they appear in a feature comparison.
If you want the longer version of that build path, we have written it up: what a retail inventory management system actually includes goes through the full inventory, and how to build a retail inventory management system covers the sequence. If you are weighing the middle option, build vs buy lays out the trade honestly.
And if you are going the build route with a coding agent, the Retail Inventory Management planner is the spec we hand ours — the ledger rule, the lifecycle states and the scope boundaries written so the agent cannot quietly skip them.