Using Excel for Kitchen Inventory
Excel is a genuinely capable inventory tool if you build it properly, and most people do not. This sets out a structure that works, the conversion formula that prevents most errors, and the three limits you will eventually hit whatever your skill level.
A structure that survives
The single most important rule: the dashboard contains no typed numbers. Everything on it is a formula. The moment someone types a figure into a derived sheet, the whole thing quietly stops being trustworthy.
The formula that prevents most errors
Unit cost is where Excel inventories go wrong, because people enter an estimate. Derive it instead:
unit_cost = pack_price / pack_quantity_in_base_unit
Keep one base unit per ingredient — grams for solids, millilitres for liquids — and convert once, in the ingredients sheet. A 16 kg sack at £18.40 becomes 16000 g and £0.00115 per gram. Every recipe then multiplies grams by that figure. Mixing kilos and grams across sheets is the most common source of order-of-magnitude errors.
For theoretical stock: opening + SUMIFS(deliveries) - SUMIFS(usage), and variance is that minus the latest count.
Make it maintainable
- Use tables (Ctrl+T) so ranges expand automatically and SUMIFS does not silently miss new rows.
- Data validation on item names — free text guarantees “Flour”, “flour” and “Flour ” become three ingredients.
- One file, in a synced folder. Versions named _FINAL_v3 are how data is lost.
- Lock the formula columns so nobody overwrites a calculation with a number.
The three structural limits
These are not skill problems. No amount of Excel expertise removes them.
- Allergen propagation. You can list allergens per ingredient. You cannot make a change to that row rewrite every label that depends on it — because the labels are not in the spreadsheet.
- Label output. A cell is not a compliant PPDS label. Labels end up in Word and drift out of step with the recipes.
- Concurrent use. Two people in one sheet is a recipe for a lost afternoon.
The first has legal consequences; the other two are inconvenience. See allergen tracking for a food business for why that chain matters.
Frequently asked questions
Can I use Excel for inventory management? Yes, well, up to roughly 50 ingredients and one user. Beyond that maintenance cost rises faster than a subscription.
Is there free inventory software? Some general tools have free tiers, but they do not model recipes, so they cannot deduct stock when you bake — which is the part a kitchen needs.