Yes, you can track inventory in Excel – if you structure the spreadsheet properly. The key trick: never overwrite stock figures. Record every movement as its own row and calculate stock with a formula. That keeps every number traceable. This guide shows the structure, the formulas and the signs that Excel isn’t enough anymore.
Key takeaways
- Build three areas: items (master data), movements (one row per booking), stock (calculated).
- Core formula: stock = opening stock + SUMIFS over all movements.
- Excel works for one person, a few hundred items and one location. Free template with all formulas ↓
- Once several people withdraw stock, the list is only as accurate as the last update – then it’s time to look at an app.
Contents: When Excel is enough · Three tables · Key formulas · Free template · 5 rules · Limits · Switching without data loss · FAQ
When an Excel inventory sheet is perfectly fine
- One person maintains the sheet and is the only one who withdraws or books stock.
- One location, a few hundred items, a manageable number of movements per day.
- No batches, expiry dates or inspection deadlines that must be proven.
- Tools and equipment don’t need to be assigned to people.
The right structure: three tables instead of one list
Format each area as an Excel table (Ctrl + T) and name it – formulas then grow automatically.
| Table | Columns | Purpose |
|---|---|---|
| Items | ItemNo, Description, Unit, Location, OpeningStock, MinStock, MaxStock, UnitCost, Supplier | Master data – one item per row |
| Movements | Date, ItemNo, Type (receipt/withdrawal/correction), Qty (+/−), Location, Person, Note | One row per booking – never delete |
| Stock (formula columns in “Items”) | Stock, Status, ReorderQty, StockValue | Calculated only |
Tip: record withdrawals as negative quantities and receipts as positive ones – then one sum formula is enough.
Key Excel formulas for inventory
Formulas for English Excel (Microsoft 365) using structured references to tables named Items and Movements.
Current stock
=[@OpeningStock]+SUMIFS(Movements[Qty],Movements[ItemNo],[@ItemNo])Reorder status
=IF([@Stock]<=[@MinStock],"Reorder","OK")Reorder quantity up to max stock
=IF([@Stock]<=[@MinStock],MAX(0,[@MaxStock]-[@Stock]),0)Stock value
=[@Stock]*[@UnitCost]Fill in the description automatically
=XLOOKUP([@ItemNo],Items[ItemNo],Items[Description],"unknown")Usage in the last 30 days
=-SUMIFS(Movements[Qty],Movements[ItemNo],[@ItemNo],Movements[Type],"Withdrawal",Movements[Date],">="&TODAY()-30)That gives you days of cover: stock divided by (30-day usage ÷ 30). How to turn that into a sensible reorder point is explained in Manage warehouse consumables digitally.
- Conditional formatting: a formula rule like
=$J2<=$F2(stock ≤ min stock) highlights critical rows in red. - Data validation: allow item numbers in “Movements” only via a dropdown from the items table – this stops typos that silently corrupt stock.
Free Excel template: inventory tracking with formulas
Everything from this article, ready to use in one file – no sign-up, no email:
- Items and Movements tables with sample data
- Stock, status, reorder quantity and stock value calculate automatically
- Red highlighting at minimum stock, dropdowns to prevent typos
- Later importable directly into Scanwiz
5 rules that keep the sheet accurate
- Never overwrite stock – record differences as correction bookings.
- One file, one place – no email copies, no “inventory_final_v3.xlsx”.
- Book the same day, not at the end of the month.
- Protect formula columns.
- Spot-check counts regularly and note the variance – your most honest quality indicator.
Where Excel reaches its limits
- Bookings happen at the desk, not the shelf – and in between, things get forgotten.
- No scanning: item numbers are typed, and a transposed digit books the wrong item.
- Multi-user work leads to conflicts or locked files; maintaining it on a phone is painful.
- No real audit trail: deleted rows are simply gone.
- Tools, batches, inspections: assignment to people, expiry dates or maintenance intervals quickly become a project of their own.
Signs it’s time to switch: several people withdraw stock, the stocktake takes more than a day, sheet and shelf regularly disagree, or only one person “understands” the file. A detailed comparison with 19 criteria, a cost calculator and sources is on Excel inventory management vs. Scanwiz.
From Excel to Scanwiz – without losing data
A well-structured spreadsheet is the perfect starting point: Scanwiz imports items, quantities, locations and values from Excel. After that, a scan replaces the row someone would otherwise have to type:
- Book by phone camera scan at the shelf – even offline.
- Minimum stock per item and location, automatic shopping list and purchase orders per supplier.
- Unlimited simultaneous users; every movement logged with person and time.
- Mobile stocktaking instead of count sheets.
- Tools with assignment in equipment management; batches and expiry dates from the Growth plan.
- Excel stays for analysis – export anytime.
More than 250 businesses use Scanwiz, rated 4.9 out of 5 from over 120 reviews. Plans start at €45 per month with unlimited users and locations – see pricing.
FAQ on tracking inventory in Excel
How do I create an inventory spreadsheet in Excel?
Create an “Items” table with master data and opening stock and a “Movements” table where every booking gets its own row. Current stock is calculated with SUMIFS from opening stock and all movements.
Which formula calculates stock in Excel?
With withdrawals as negative quantities: =[@OpeningStock]+SUMIFS(Movements[Qty],Movements[ItemNo],[@ItemNo]).
How do I flag items below minimum stock in Excel?
Use conditional formatting with a formula rule comparing stock and minimum stock, plus a status column with IF that returns “Reorder”.
Is Excel suitable for inventory management?
For one person, one location and a few hundred items, yes. Once several people withdraw stock, scanning, simultaneous work and audit trail are missing, and the sheet drifts from the shelf.
Can I move my Excel list into inventory software?
Yes. Scanwiz imports items, quantities, locations and values from Excel. After that, stock is booked by scan and Excel is only used for exports and analysis.
Conclusion
A well-built Excel inventory sheet with a movements table and formulas is a solid start – far better than a list where numbers are overwritten by hand. Its limit isn’t calculation power but the distance between shelf and spreadsheet. As soon as several people withdraw stock, a system that books where the movement happens wins.
Updated October 2026.
Over 250 customers already trust Scanwiz
Your spreadsheet is the perfect start.
Import it into Scanwiz and book by scan from tomorrow instead of catching up later. Start for free or let us walk you through the switch in a personal demo.
