Build an Inventory Sheet That Warns You When Stock Runs Low
Most stock sheets tell you what you have. This one tells you what to buy. Set a minimum level per item, log deliveries and issues as they happen, and the sheet flags anything that has fallen below the line — with the quantity to order already worked out.
⬇ Download the Inventory Tracker (.xlsx)
Free, no email required. Works in Excel 2016 and later. Prefer Google Sheets? Open a copy in your own Drive.
The rest of this guide explains how the warning works, so you can set the thresholds to match how your own stock moves.
Nothing here is typed except the yellow cells — the status, the colour and the order quantity all follow from the movement log.
What it does
| Sheet | What it's for |
|---|---|
| Products | One row per item. You set the code, name, cost, opening quantity and minimum level; the sheet works out everything else. |
| Movements | Every delivery and every issue, one row each. This is the only sheet that changes day to day. |
| Summary | How many items are fine, low or out; what needs ordering; stock value by category. |
The rule that makes it useful
Stock on hand is never typed. It's derived:
=IF($A2="","",$E2+$F2-$G2)Opening quantity, plus everything received, minus everything issued. The In and Out columns are themselves formulas, each a SUMIFS reading the movement log:
=SUMIFS(Movements!$E$2:$E$301,
Movements!$B$2:$B$301, $A2,
Movements!$D$2:$D$301, "In")Two conditions: this item's code, and movements of type In. Change "In" to "Out" for the other column. If the pattern is unfamiliar, the SUMIFS guide covers it in full.
⚠️ This is the discipline the whole sheet depends on: never edit On hand directly. If a physical count disagrees with the sheet, add a movement for the difference and note why. Overwriting the formula breaks that row permanently and you won't notice for weeks.
Three states, in the right order
=IF($A2="","",
IF($H2<=0,"Out of stock",
IF($H2<=$I2,"Low","OK")))Out of stock is tested before Low, and that order isn't arbitrary. Zero is also below the minimum, so if Low came first, an item at zero would report as merely Low and you'd treat an emergency as a routine reorder.
Nested IFs stop at the first condition that's true, so the most serious case has to be checked first. The same principle applies to any grading or banding formula — highest severity at the top. There's more on the mechanics in the IF formula guide.
Note <= rather than < in the Low test. An item sitting exactly on its minimum has reached the reorder point — that's what a minimum means.
The quantity to order
=IF($A2="","",IF($H2<=$I2,MAX($J2-$H2,0),""))When stock is at or below the minimum, order the difference between what you have and the Reorder-to level. MAX(...,0) guards against a negative result if someone sets the reorder level below the minimum — without it you'd get an instruction to order minus twelve units.
The column stays blank while an item is fine, so the Order now column doubles as your purchase list: filter it for non-blanks and that's the order.
Setting the minimum level
The template can't guess this for you, and a wrong threshold makes the whole thing useless — too low and you run out anyway, too high and you tie up cash in stock you don't need.
A workable starting point:
minimum = (how much you use per week) × (weeks the supplier takes) + a bufferIf you get through 10 a week and delivery takes a week, a minimum of 15 gives you five units of margin for a late lorry. Items that are cheap and small deserve a generous buffer; items that are expensive or bulky deserve a tight one.
Why the movement log has a lookup
You enter a SKU on the Movements sheet, and the item name appears next to it:
=IF($B2="","",IFERROR(INDEX(Products!$B$2:$B$61,
MATCH($B2,Products!$A$2:$A$61,0)),"SKU not found"))This is INDEX MATCH doing what it's best at: the code lives in column A of the product list and the name in column B, so a lookup is straightforward — but writing it this way means the columns can move later without breaking anything.
The IFERROR wrapper turns a missing code into "SKU not found" rather than #N/A. Without it, one mistyped code fills your log with errors and hides the real ones.
The colours
Two conditional formatting rules on the whole product range:
| Formula | Fill |
|---|---|
=$K2="Out of stock" | Light red |
=$K2="Low" | Amber |
Items that are fine get no colour at all — which is deliberate. If every row is coloured, nothing stands out; the eye should land only on what needs action.
Building it from scratch
- Products sheet — SKU, Item, Category, Unit cost, Opening, In, Out, On hand, Min level, Reorder to, Status, Order now, Stock value.
- Movements sheet — Date, SKU, Item, Type, Qty, Note. Data validation on SKU (list from Products) and Type (
In,Out). - SUMIFS for In and Out; the arithmetic for On hand; the nested IF for Status; the MAX formula for Order now.
- Two conditional formatting rules keyed on the Status column.
- Summary sheet — COUNTIF and SUMIF by status, SUMIF by category.
Around forty minutes. Longer than the other templates, and worth it — stock formulas are the ones you'll most want to adjust to your own way of working.
Does this work in Google Sheets?
Yes. SUMIFS, INDEX, MATCH, IFERROR, nested IF and formula-based conditional formatting all behave identically. Only the menu path differs: Format → Conditional formatting → Custom formula is.
When it goes wrong
On hand doesn't match the shelf
Something wasn't logged, or was logged as the wrong type. Filter the Movements sheet by that SKU and read down — the error is almost always a missing Out, because deliveries get recorded and issues get forgotten.
An item shows Out of stock but you can see it
Usually a duplicate SKU on the Products sheet. Two rows share a code, movements attach to whichever the SUMIFS finds, and the other reads as empty. Codes must be unique.
Movements shows "SKU not found"
The code was typed rather than chosen from the drop-down, or the product was deleted after the movement was logged.
Everything says Out of stock
The IF($A2="","",...) guard is missing. Empty rows have zero stock, and zero is at or below every minimum.
Common questions
Can it handle several locations?
Add a Location column to Movements and a third condition to each SUMIFS. Beyond two or three locations, this stops being a spreadsheet job.
Can it track batch or serial numbers?
Not as built. That needs one row per unit rather than per movement, which is a different structure — and at that point stock software earns its cost.
Why not just update a quantity column by hand?
Because you lose the history. A movement log tells you what you used, when, and on what job — which is what lets you set sensible minimums later. A single quantity tells you nothing about how it got there.
How many products will it hold?
Sixty rows of products and 300 movements as supplied, and both extend by inserting rows inside the existing range. Past a few thousand movements, recalculation gets noticeably slow.
The short version
- On hand is opening plus In minus Out, never typed.
- Test Out of stock before Low, or an emergency reads as routine.
<=in the Low test, because reaching the minimum is the trigger.MAX(...,0)stops the order quantity going negative.- Set the minimum from usage per week × supplier lead time, plus a buffer.
- Correct a miscount with a movement, not by overwriting the formula.
Next steps: the In and Out columns are SUMIFS with two conditions, the status column is nested IF logic, and the item lookup is INDEX MATCH. If duplicate SKUs are causing trouble, COUNTIF will find them before they cause damage.
Comments
Post a Comment