Stock In/Out Google Sheets Template: See On Hand, REORDER and OUT OF STOCK
You sold the last box of your best seller on Tuesday. You only noticed on Friday, when a customer asked for it and the shelf was empty. Your notebook said there were "about ten" left.
Running out by surprise is one of the most common problems in a small shop, and it rarely comes from laziness. It comes from a system that records what happened but never tells you what it means. A stock in/out sheet fixes that: you write down each movement, and the sheet works out what is on hand and warns you when it is time to reorder.
This guide shows you how to build a simple stock in/out balance tracker in Google Sheets yourself. At the end there is an optional ready-made template if you would rather skip the setup.
What a stock in/out sheet needs to do
A good stock in/out tracker answers three questions for every item:
- How much is on hand right now? Opening quantity, plus everything that came in, minus everything that went out.
- Is it time to reorder? Is the on-hand number at or below the level where you normally buy more?
- What happened, and when? A list of every movement, so you can find mistakes.
Everything else is extra. If your sheet answers these three, it is doing its job.
Step 1: Make an Items tab
Create a new Google Sheet and name the first tab Items. Add these columns:
| Item | Unit | Opening qty | Reorder level |
|---|---|---|---|
| Paper cups 8oz | Pack | 40 | 10 |
| Kraft bags small | Piece | 300 | 100 |
The reorder level is the number at which you want a warning. A simple way to choose it: how many do you usually sell while you wait for a new delivery? Add a little extra as a buffer.
Use one row per item and spell each name exactly the same way every time. "Paper cups 8oz" and "paper cup 8 oz" will be counted as two different items.
Step 2: Make a Movements tab
Add a second tab called Movements. Every time stock comes in or goes out, add one row:
| Date | Item | Action | Qty | Note |
|---|---|---|---|---|
| 2026-10-01 | Paper cups 8oz | In | 20 | Supplier delivery |
| 2026-10-02 | Paper cups 8oz | Out | 6 | Sale |
Keep the Action column to a short fixed list such as In and Out. In Google Sheets you can make it a dropdown: select the column, then use Data > Data validation and choose "Dropdown". Dropdowns stop typos from breaking your totals.
Step 3: Calculate On hand
Back on the Items tab, add a column On hand. For the first item in row 2, use a formula like this:
=C2 + SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "In") - SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "Out")
In plain words: opening quantity, plus all the "In" rows for this item, minus all the "Out" rows. Copy the formula down for every item.
Step 4: Add a status column
Add one more column, Status:
=IF(E2<0, "NEGATIVE STOCK", IF(E2=0, "OUT OF STOCK", IF(E2<=D2, "REORDER", "OK")))
Now each item shows OK, REORDER or OUT OF STOCK at a glance. NEGATIVE STOCK means you recorded more going out than you had, which almost always means a missing "In" row or a typo. Use Format > Conditional formatting to colour REORDER yellow and OUT OF STOCK red, so problems stand out.
Common mistakes to avoid
- Half-filled rows. A movement with no item name or no quantity can quietly change your totals, or get ignored. Check the last few rows each day.
- Renaming items. If you change "Cups" to "Paper cups 8oz" on the Items tab, old movements still say "Cups" and stop counting. Rename in both places, or give each item a fixed code.
- Forgetting damaged or lost stock. Broken items are stock going out too. Record them as Out with a note, so on-hand matches the shelf.
- Never counting the shelf. Once a month, count a few items by hand and compare. If they differ, add a correcting row and note why.
A simple daily routine
- Record deliveries as In when you unpack them.
- Record sales or use as Out at the end of the day (or straight away, if you can).
- Glance at the Status column before you take a big order or place a supplier order.
Five minutes a day is enough for most small shops.
When a spreadsheet is not enough
A stock in/out sheet is a quantity tracker. If you need inventory valuation, profit reports, barcode scanning, a checkout till, or separate totals for several warehouses, look at dedicated inventory or POS software instead. For a small shop or online seller that just needs to know what is on the shelf, a sheet is often all you need.
Related guides
Quick recap
- Items tab: name, unit, opening quantity, reorder level.
- Movements tab: date, item, In or Out, quantity.
- On hand = opening + in - out.
- Status shows OK, REORDER or OUT OF STOCK, so you see problems before your customer does.