John SerraTo track inventory in Google Sheets, use three tabs instead of one list of quantities. An Items tab describes what you stock and when to reorder it. A Movements tab records every receipt, sale, loss, and count correction as a new row. A Stock tab calculates what’s on hand from those movements, and flags anything at or below its reorder point. Nobody types a quantity into the Stock tab, so as long as people add rows and corrections instead of changing old ones, the history stays intact.
Why One “Quantity on Hand” Column Fails
The common first attempt is a list of products with a quantity column that people update by hand. It works for a few weeks, then breaks in predictable ways:
- You can’t tell what happened. If a count drops from 12 to 8, the sheet doesn’t say who took the four, when, or whether they were sold, spoiled, or lost.
- Formulas and formatting get overwritten. Anyone typing in the wrong cell can wipe out a calculation without noticing.
- You can’t see usage. Without dated movements, you can’t work out how fast something sells or when you’ll run out.
The fix is to keep two kinds of records apart. Master records describe things that change rarely, such as the item list and supplier lead times. Transaction records log events as they happen. The balance is always calculated from the transactions, never typed.
The Three Tabs at a Glance
| Tab | What it holds | Who types in it |
|---|---|---|
| Items | One row per product: cost, supplier, usage, lead time, reorder point | You, when products change |
| Movements | One row per stock event | Whoever receives, sells, or counts stock |
| Stock | Calculated balances and reorder flags | Nobody (formulas only) |
If you’d rather start from a working version, copy the inventory template. It has the three tabs, the formulas, the dropdowns, and the reorder flag already built, with made-up example data and room for 199 items. Before you use it, follow “Clear the Example Data Before You Start” below. The rest of this article explains how each part works so you can build it or adapt it.
Tab 1: Items
Make a tab named Items with these columns in row 1:
| Column | Header | What goes in it |
|---|---|---|
| A | SKU | A short unique code, such as PKG-001. Never reuse one. |
| B | Item | The plain-English name |
| C | Category | Packaging, supplies, finished goods |
| D | Unit | The one base unit you count, buy, and use this item in: each, roll, kilogram |
| E | Unit cost | What you last paid per base unit |
| F | Supplier | Who you buy it from |
| G | Average daily use | How many base units you use or sell per day, as an estimate to start |
| H | Lead time (days) | Days from placing an order to having it in hand |
| I | Safety stock (days) | Extra days of use to cover late deliveries and busy spells |
| J | Reorder point | Calculated, see below |
The reorder point
A reorder point is the stock level at which you should place an order. A common way to set it is to cover the use during the supplier’s lead time, plus a safety buffer:
Reorder point = average daily use × (lead time + safety days)
In the sheet, with row 2 as the first item, cell J2 holds:
=IF(A2="","",G2*(H2+I2))
Copy it down the column, as far as you expect to have items (the template does this to row 200). The IF leaves the cell blank for empty rows.
Pick one base unit per SKU and use it everywhere: in every movement, in daily use, and in unit cost. If you buy mailer boxes by the case of 12 but use them one at a time, log a delivery of one case as 12 each and divide the case price by 12 for the unit cost. Mixing cases and pieces in the same item quietly corrupts the balance, the value, and the reorder point.
Here is a made-up example. A business uses 10 mailer boxes a day. The supplier takes 7 days to deliver, and the owner wants 5 days of cushion. The reorder point is 10 × (7 + 5) = 120 boxes. When stock reaches 120 or fewer, it’s time to order.
Daily use is the weakest number in this formula, because at first it’s a guess. The Stock tab below shows the real use from your own movements, so you can correct the estimate after a month or two.
Tab 2: Movements
Make a tab named Movements with these columns:
| Column | Header | What goes in it |
|---|---|---|
| A | Date | When the movement physically happened |
| B | SKU | Chosen from a dropdown that reads the Items tab |
| C | Movement type | Chosen from a fixed list |
| D | Quantity | A positive number, decimals allowed |
| E | Reference | Order number, PO number, or count date |
| F | Logged by | Initials |
Quantity is always positive. The movement type decides whether stock goes up or down. Use this list of seven types. Each starts with IN or OUT:
- IN – Opening count
- IN – Purchase received
- IN – Return to stock
- IN – Count correction up
- OUT – Sale or use
- OUT – Waste or damage
- OUT – Count correction down
Start with opening counts. On the day you start, count everything and log one “IN – Opening count” row per SKU, dated that day. That’s the only time you enter a starting balance. After that, the balance is always the sum of movements. If an item has zero stock, skip its opening row; the quantity rule below requires a number above zero. Dates are for things that have physically happened, so log a purchase when it arrives, not when you order it.
Add the dropdowns
Dropdowns stop typos like “Tomato” and “Tomatoes” from becoming two items. Select the cells in column B (B2 down to row 1,000), then choose Data, then Data validation. In the Data validation rules panel, click Add rule. Under Criteria, choose Dropdown (from a range) and enter Items!A2:A200. Under Advanced options, leave “If the data is invalid” on Reject the input, then click Done.
Repeat for column C, using the seven movement types. If you’re building from scratch, first make a fourth tab named Lists, type “Movement types” in A1, and type the seven types in A2 to A8. The template already has this tab. Then use Dropdown (from a range) with Lists!A2:A8. For column D, choose Greater than and enter 0, so nobody enters a negative quantity.
Select the cells before you click Add rule, and don’t change the “Apply to range” box afterward. When you finish, the rules panel should list three rules, one for each column. If a rule you added earlier disappears, you changed the range of a rule that already existed; add it again. Then test it: type a made-up SKU in column B. Google Sheets should reject it with a message that the entry violates the data validation rules.
Tab 3: Stock
Make a tab named Stock. Row 2 holds the formulas for the first item, and you copy that row down.
| Column | Header | Formula in row 2 |
|---|---|---|
| A | SKU | =IF(Items!A2="","",Items!A2) |
| B | Item | =IF(A2="","",Items!B2) |
| C | Received | =IF(A2="","",SUMIFS(Movements!$D:$D,Movements!$B:$B,$A2,Movements!$C:$C,"IN - *")) |
| D | Removed | =IF(A2="","",SUMIFS(Movements!$D:$D,Movements!$B:$B,$A2,Movements!$C:$C,"OUT - *")) |
| E | On hand | =IF(A2="","",C2-D2) |
| F | Reorder point | =IF(A2="","",Items!J2) |
| G | Status | =IF(A2="","",IF(E2<0,"CHECK COUNT",IF(E2<=F2,"REORDER","OK"))) |
| H | Unit cost | =IF(A2="","",Items!E2) |
| I | Stock value | =IF(A2="","",E2*H2) |
The * in "IN - *" is a wildcard, so the formula adds up every movement whose type starts with “IN – “. That’s why the type names matter and why the dropdown is locked to the list.
Two safeguards sit in the status formula. If on-hand comes out negative, which should be impossible, the status says CHECK COUNT instead of looking fine, because that points to a missed receipt or a typo. The reorder test uses “at or below,” so a count exactly on the reorder point triggers it.
Make the flag visible
Select column G, then choose Format, then Conditional formatting. Use Text is exactly REORDER and a soft red fill. Add a second rule for CHECK COUNT with a yellow fill. The Stock tab then shows what needs ordering at a glance.
What the example shows
With the template’s made-up data, the Stock tab calculates:
| SKU | Received | Removed | On hand | Reorder point | Status | Stock value |
|---|---|---|---|---|---|---|
| PKG-001 | 600 | 293 | 307 | 120 | OK | $260.95 |
| PKG-002 | 30 | 17 | 13 | 13.5 | REORDER | $31.20 |
| LBL-001 | 13 | 3 | 10 | 5 | OK | $60.00 |
| INK-001 | 4 | 1 | 3 | 1.8 | OK | $66.00 |
| PRD-001 | 400 | 159 | 241 | 126 | OK | $747.10 |
| PRD-002 | 150 | 67 | 83 | 63 | OK | $481.40 |
The packing tape has 13 rolls on hand against a reorder point of 13.5, so it’s flagged. Stock value is on-hand quantity times the last unit cost you entered. It’s a working figure for the shop floor, not an accounting valuation, because your accountant may use a different costing method.
Check your usage estimate
Two more columns help you test the daily-use guess against reality. They only mean something for an item once you have 28 full days of history for that item, so the template leaves them blank until then. Use these estimates only when every movement for that item has been recorded throughout the last 28 completed days; an older first entry alone does not establish complete logging. In column J, =IF(A2="","",IF(COUNTIF(Movements!$B:$B,$A2)=0,"",IF(MINIFS(Movements!$A:$A,Movements!$B:$B,$A2)>TODAY()-28,"",SUMIFS(Movements!$D:$D,Movements!$B:$B,$A2,Movements!$C:$C,"OUT - Sale or use",Movements!$A:$A,">="&(TODAY()-28),Movements!$A:$A,"<"&TODAY())/28))) gives average daily use over the last 28 completed days, not counting today or any future-dated rows. Column K, =IF(A2="","",IF(N(J2)>0,E2/J2,"")), divides on-hand stock by it to estimate days of stock left.
The blanks are deliberate, and they are per item. If an item’s first logged movement is only ten days old, dividing by 28 would treat the missing eighteen days as days when nothing sold, so that item stays blank even if other items have a long history. An item with no movements at all also stays blank rather than showing zero. If the real use differs a lot from the number in the Items tab, update the Items tab and the reorder point follows. Because these columns depend on today’s date, the template’s example data (October 1 to 15, 2026) is a finished illustrative period, and the columns will show nothing until you’ve logged 28 days of your own.
Clear the Example Data Before You Start
The template comes with made-up transactions. If you leave them in, your real items inherit fictional stock. To start clean:
- On the Movements tab, select the data rows from row 2 down to the last example row and press Delete. This clears the contents but keeps the header row and the dropdown rules. Don’t delete the rows themselves.
- On the Items tab, type over the example rows in columns A to I with your own items. Leave column J alone; it holds the formula. Clear any leftover example rows below your last item.
- Back on Movements, log one “IN – Opening count” row for each item with your real count.
- Check the Stock tab. On hand should match what you counted on the shelf for every item before you log anything else.
How People Use It Day to Day
Receiving a delivery, selling or using stock, and discarding spoiled goods each add one row to Movements. Here are three made-up rows:
| Date | SKU | Movement type | Quantity | Reference | Logged by |
|---|---|---|---|---|---|
| 2026-10-07 | PKG-001 | IN – Purchase received | 200 | PO-0101 | JS |
| 2026-10-08 | PKG-001 | OUT – Sale or use | 90 | Week 2 shipments | JS |
| 2026-10-13 | PRD-001 | OUT – Waste or damage | 4 | Damaged in storage | MK |
If the work is steady, log in batches, such as once a day for sales and at receiving time for deliveries. The point is that every change has a row, a date, and a reason. For high-volume sales, you can total a day’s sales into one row per SKU instead of one per sale.
Protect the Items and Stock tabs so only you can edit them. Choose Data, then Protect sheets and ranges, and limit editing on the formula cells. Staff then only need access to Movements, where mistakes are visible and correctable. Protection limits who can edit a range; it isn’t a security control, and editors of Movements can still change or delete old rows. Keeping the history intact is a habit: add rows, never rewrite them.
Count a Few Items Regularly and Fix Differences with a Row
No sheet matches the shelf perfectly. Items get miscounted, damaged without being logged, or taken. A cycle count catches this without closing the business: count a handful of SKUs on a rotating schedule, and cover everything over a few weeks. How many you count at a time depends on how many items you have and how long a count takes.
When a count disagrees with the sheet, don’t edit the Stock tab or delete old rows. Add a correction row. If the sheet says 20 and the shelf has 17, log “OUT – Count correction down”, quantity 3, with a reference like “Cycle count 2026-10-15”. If the shelf has more, log “IN – Count correction up” for the difference. The balance fixes itself, and the history keeps the evidence. If corrections for the same item keep going down, that is the clue to look for waste, theft, or an unlogged use.
What a Spreadsheet Doesn’t Do
- It doesn’t know about open orders. An item stays flagged REORDER until you log the receipt, even if you ordered it yesterday. Put the PO number in a note column on the Items tab, or mark the row, so two people don’t order the same thing twice.
- It doesn’t change costs by lot. Unit cost is a single number, so it can’t show first-in, first-out valuation.
- It tracks one place. Several locations, transfers between them, and lot or expiry tracking need a different design.
- It doesn’t scan. Entry is by hand unless you add other tools.
Stay in Google Sheets until it stops being enough for a specific reason like these, not just because the item count grows. If you outgrow it, look for tools that solve the exact problem: barcode scanning, multi-location transfers, or lot tracking. How much a small business should spend on data tools can help you weigh the cost.
Connect It to Alerts and Cash
The REORDER flag appears when someone opens the Stock tab. If you want an email when an item crosses its reorder point, How to set up automatic alerts in Google Sheets covers the options, including a script that emails you once when a number crosses a line. That script checks a number against a minimum, so watching a text status for each item would need an adapted script rather than just new cell references. Orders also have to be paid for, so it helps to put planned inventory purchases into your cash forecast. The 13-week cash flow tracker has a row for vendor and inventory payments.
Frequently Asked Questions
How many items can this handle?
The template has formulas for 199 items (rows 2 to 200), and its dropdowns accept entries down to row 1,000 of Movements. To go past either limit, extend everything together: copy the Items column J formula and the Stock row 2 formulas further down, extend the Stock conditional formatting range, widen the SKU dropdown source (Items!A2:A200), and extend the validation rules on Movements. Performance depends on your file, and Google recommends closed ranges instead of open ones in formulas, so if a large Movements tab gets slow, change the whole-column references to bounded ranges. You can also archive older years to another file after carrying forward a closing count as new opening rows.
Can several people use it at once?
Yes. Google Sheets lets several people edit the same file. What matters is the discipline: everyone logs movements in the same way, and only a few people can change the Items and Stock tabs.
What about returns from customers?
If the returned item goes back on the shelf, log “IN – Return to stock”. If it can’t be resold, don’t log anything else against stock: the original sale already took that unit out, so logging it as waste would remove it a second time. Say you start with 10, sell 1, and get a damaged return. The balance should stay at 9. Record what happened to the damaged unit in a note or the Reference column of a separate log. Use “OUT – Waste or damage” only for units that are actually counted in stock.
Does this replace my accounting records?
No. It tracks quantities for operations. Your accounting records have their own rules for how inventory is valued and reported.