Use three tabs, not one
The most common mistake is one big sheet where quantities are typed over. Use three tabs instead:
- Products: one row per product, with name, pack, opening stock, cost and reorder level.
- Movements: one row per delivery or sale: date, product, in, out, unit, who.
- Counts: what should be there, what was counted, and the difference.
On the Products tab, what is left = opening stock + the sum of that product's ins − the sum of its outs. A SUMIF over the Movements tab does it: =C2+SUMIF(Movements!B:B,A2,Movements!C:C)-SUMIF(Movements!B:B,A2,Movements!D:D). Never type over a quantity again; add a movement instead, so there is always a record of why it changed.
Our free inventory template has all three tabs ready, with notes on every column.
Rules that keep the file right
- One file, one place. Keep it in one shared folder. A copy on a laptop and a copy on a flash drive means two different answers.
- Names from a list. Use the exact product name from the Products tab every time (a drop-down helps), or SUMIF will miss rows.
- Always write who. A column for the person who recorded each line.
- Back it up weekly, somewhere other than the computer it lives on.
Where Excel stops keeping up
Excel works while one person, at one computer, can type everything the same day. It strains when staff at the counter and the gate need to record at the same time, when the network or the power goes and the file is on the office laptop, or when you need to know who changed a quantity (Excel doesn't record that). That is usually the moment to move to an app, and you don't have to retype anything to do it.