To make an inventory spreadsheet in Google Sheets, use two tabs: an Items tab with one row per thing you stock (name, unit, where it lives, an opening count and a reorder level), and a Movements tab where every delivery, use, sale and bit of waste gets its own row with a positive or negative quantity. Then one formula, SUMIF, adds each item's movements to its opening count to give what's on hand, and an IF beside it says "Reorder" when on hand drops to the reorder level. Add a dropdown so item names always match, freeze the header row, and color the reorder rows red. Below is each step with the exact formulas, and a free template.
The short version
- Items tab: Item, Unit, Where it lives, Opening count, Reorder at, On hand, Status.
- Movements tab: Date, Item, What happened, Quantity (+ in, − out), Note.
- On hand:
=D2+SUMIF(Movements!B:B, A2, Movements!D:D). - Status:
=IF(F2<=E2, "Reorder", "OK"). - A dropdown on the Movements item column, pulling from the Items tab.
- Conditional formatting to turn "Reorder" red.
- Freeze the header, protect the formula columns, share with your team.
- When it outgrows itself, TaroStack takes the same list in an afternoon. More below.
Why two tabs, not one
The common first spreadsheet has one row per item and a "Quantity" column people overwrite. It works until the number is wrong, and then there's no way to find out why: nobody knows who changed it, when, or from what. A movements log fixes that. The opening count never changes, every change is its own dated row, and on hand is worked out from both. If the flour is short, you can read down its rows and see what happened.
Step 1: the Items tab
| Column | What goes in it | Example |
|---|---|---|
| A Item | One clear name per thing | Bread flour |
| B Unit | The unit you count in | lb |
| C Where it lives | The shelf or area | Dry store A |
| D Opening count | Your starting count, typed once | 150 |
| E Reorder at | The level that means "order now" | 180 |
| F On hand | A formula (step 3) | 165 |
| G Status | A formula (step 4) | Reorder |
Keep one name per thing. "Flour", "AP flour" and "all-purpose" as three rows is three counts that never agree, and the formula in step 3 only adds up rows whose name matches exactly.
Step 2: the Movements tab
| Date | Item | What happened | Quantity | Note |
|---|---|---|---|---|
| Oct 12 | Bread flour | Received | 200 | 4 bags of 50 lb |
| Oct 13 | Bread flour | Used | −90 | Monday and Tuesday bakes |
| Oct 15 | Bread flour | Used | −90 | Wednesday and Thursday bakes |
| Oct 15 | Bread flour | Waste | −5 | Split bag |
Positive numbers come in, negative go out. Every delivery, every bake or batch, every sale you don't get from a till, and every bit of waste gets a row, in the item's counting unit. When you count and the shelf disagrees, add a "Count correction" row for the difference rather than changing the opening count.
Step 3: the on hand formula
In F2 on the Items tab:
=D2+SUMIF(Movements!B:B, A2, Movements!D:D)
SUMIF looks down the Movements item column for rows matching A2 and adds their quantities. For the flour: 150 + 200 − 90
− 90 − 5 = 165 lb. Drag the formula down the column. To keep empty rows blank, wrap it:
=IF(A2="", "", D2+SUMIF(Movements!B:B, A2, Movements!D:D)).
Step 4: the reorder flag
In G2:
=IF(F2<=E2, "Reorder", "OK")
Then select column G, open Format, Conditional formatting, choose "Text is exactly", type Reorder, and pick a red fill. Now the list of what to buy is every red row. How to set the reorder level itself, from daily use and lead time, is in reorder point example.
Step 5: a dropdown for item names
On the Movements tab, select column B, open Data, Data validation, add a rule, choose "Dropdown (from a range)" and enter
Items!A2:A200. Now anyone logging a movement picks the item from the list, which keeps every name an exact match. Do
the same for "What happened" with a fixed list: Received, Used, Sold, Waste, Moved, Count correction.
Step 6: make it safe to share
Freeze the header row (View, Freeze, 1 row) so the column names stay put. Protect columns F and G (Data, Protect sheets and ranges) so nobody types over a formula. Share it with edit access for the people who log movements. And add a Value column, on hand times unit cost, if you want a stock value at a glance.
Get the template
Download the inventory spreadsheet for Google Sheets. It has both tabs built as above, with the formulas, the dropdowns, the red reorder flag and a value column, filled with a small bakery's example so you can see it working. Open it in Google Sheets with File, Import, Upload. It's an ordinary file with no macros, and it also works in Excel, LibreOffice and Numbers. If you make things from recipes, there's a version that takes ingredients off by recipe in Google Sheets inventory template with recipes.
Where this stops working
A two-tab sheet is honest and cheap, and for one person with a few dozen items it's enough. It strains in four places. More than one person logging at once, because two people type into the same row or forget to log at all. Lots and dates, because one row per item can't say which bag expires first. Recipes, because every batch means typing six negative rows in the right units. And more than one place, because the market stall's stock needs its own column, then its own tab, then its own spreadsheet. The full comparison is in inventory software vs a spreadsheet.
How TaroStack does it
TaroStack is the same idea as the two-tab sheet, an item list plus a record of every movement, with the typing taken out.
Your sheet comes with you. Items, suppliers and opening stock import from the spreadsheet you already have, with every row previewed before anything is saved, so the Items tab you built becomes your item list in an afternoon. Or take a photo of a handwritten count sheet or a supplier's price list. No plan limits how many items you have.
Movements log themselves. Receiving a delivery adds it, at the price you paid. Recording a batch takes each ingredient off stock in the right unit, from the right lot. A sale or a shipped order takes it out, and Shopify orders come in. Waste is written off with a reason. Every movement is a permanent record of who, when and why, which is the Movements tab nobody has to keep, and stock can't go below zero by mistake.
The red rows become a reorder list. Every item shows days of cover at the rate it's going out, with a red line where an order placed today would arrive too late, and reorder points are suggested from how stock actually moves. The suggested order quantity counts what's already on order and rounds up to the supplier's pack size and minimum. Lots carry their dates, and the oldest goes first.
And more than one person can count. Counts run on phones, even in the walk-in with no signal, and show what a difference is worth before it's posted. Everyone sees the same numbers, limited to the sites they work at.
| The job | Two-tab Google Sheet | In TaroStack |
|---|---|---|
| On hand | SUMIF over the log | Kept live, per site and shelf |
| Logging a batch | Six negative rows by hand | Recorded with the batch, by lot |
| What to reorder | Red rows | Days of cover, suggested quantities |
| Who changed it? | Version history, maybe | Every movement, with who and why |
Items, receiving, counts, lots and the reorder list are on every plan, from $49 a month; recipes and batches are on Standard at $99. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.