Use four connected tables
SKU, product name, category, unit, supplier, cost, selling price, reorder level, and active status.
Date, reference, SKU, movement type, quantity in, quantity out, location, and responsible person.
Supplier ID, contact details, lead time, payment terms, and products supplied.
Count date, SKU, system quantity, physical quantity, variance, explanation, and approval.
Core inventory formulas
Controls that prevent expensive errors
- Give every product one permanent SKU and avoid duplicate naming.
- Record adjustments as movements instead of overwriting balances.
- Use data validation for movement types, units, locations, and status.
- Protect formula columns and keep an untouched backup before imports.
- Count high-value and fast-moving stock more frequently.
- Investigate variances and record the reason and approval.
A useful monthly inventory review
Review items below reorder level, zero-movement products, negative balances, high variances, expiring stock, supplier lead times, gross margin, and total inventory value. A PivotTable can compare movements by product, location, supplier, or month without adding more formulas.
Know when to move beyond Excel. Multiple simultaneous users, barcode operations, serial tracking, complex manufacturing, and real-time multi-location stock may require a dedicated inventory system.
Frequently asked questions
Can Excel manage inventory for a small business?
Yes, when the product range and transaction volume are manageable and the workbook has clear ownership, validation, backups, and review controls.
Should I type the current balance directly?
A stronger system calculates balance from stock-in and stock-out movements, preserving an audit trail.
What is the most important inventory field?
A consistent unique SKU prevents confusion when product descriptions or suppliers change.