How to track inventory in Google Sheets
Most stock spreadsheets start as one list with a quantity column that somebody edits whenever something sells. It works for a month. Then two people edit it on the same afternoon, a number is wrong, and nobody can say when it went wrong or why.
The fix is a small change in layout. You stop storing the stock number and start storing the things that change it.
Use two tabs: products and stock moves
Products has one row per thing you sell. It holds facts that rarely change.
| SKU | Product name | Category | Unit cost | Selling price | Opening stock | Reorder level |
|---|---|---|---|---|---|---|
| M-101 | Blue mug | Mugs | 120 | 299 | 40 | 15 |
| M-102 | Red mug | Mugs | 120 | 299 | 25 | 15 |
Stock moves has one row per movement. Every delivery, sale, return or write-off is a new row. Rows are never edited after the day they are added.
| Date | SKU | Type | Quantity | Note |
|---|---|---|---|---|
| 2026-11-02 | M-101 | In | 24 | Supplier delivery |
| 2026-11-03 | M-101 | Out | 6 | Order for Ravi |
The SKU is what ties the two tabs together, so it has to be typed identically in both. The easiest way to guarantee that is a dropdown: select the SKU column in Stock moves, choose Data > Data validation, and point it at the SKU column on the Products tab. Do the same for Type with the two values In and Out. Google's help page on dropdown lists walks through the menus.
Add the three formulas that do the work
These go on the Products tab, in new columns to the right of Reorder level. With the layout above, SKU is column A, Unit cost is D, Opening stock is F and Reorder level is G.
Stock in (column H) adds up every In movement for this SKU:
=SUMIFS('Stock moves'!D:D, 'Stock moves'!B:B, A2, 'Stock moves'!C:C, "In")Stock out (column I) is the same with Out:
=SUMIFS('Stock moves'!D:D, 'Stock moves'!B:B, A2, 'Stock moves'!C:C, "Out")Stock left (column J) is opening stock, plus in, minus out:
=F2+H2-I2
Fill all three down the column. For the blue mug above that gives 40 + 24 − 6 = 58.
SUMIFS reads as: add up the quantities, but only the rows where the SKU matches this row and the type is In. If you have not used it before, Google's SUMIFS reference has more examples.
Flag what needs reordering
Add one more column that compares stock left with the reorder level:
=IF(J2<=G2, "Reorder", "OK")
Now filter that column for Reorder and you have your purchase list. A sensible starting reorder level is the number of units you sell during the time a new delivery takes to arrive, plus a small buffer. If you sell about five blue mugs a week and the supplier takes two weeks, a level of 15 gives you one spare week.
For stock value, multiply stock left by unit cost: =J2*D2. Sum that column and you know how much money is sitting on the shelf.
Keep the formulas safe
The weak point of this layout is someone typing a number over a formula. Two habits prevent most of it:
- Shade the formula columns a different colour, so it is obvious which cells are worked out and which are typed in.
- Protect them. Select the formula columns, choose Data > Protect sheets and ranges, and limit editing to yourself. Everyone else can still add rows to Stock moves.
Where a tracker like this stops being enough
This layout answers "what do we have" at any moment. It does not stop the questions. The person who owns the sheet still gets asked how many blue mugs are left, several times a day, by people who could look but would rather ask.
There are two ways out. One is to share the sheet read-only and teach everyone to filter it. The other is to let people ask the sheet directly in plain words. That second route is what we are building: the inventory tracker template is this exact layout, and in the live demo you can ask it "what needs reordering?" or tell it "sold 3 blue mugs" and watch the stock number change through the formulas rather than around them.
Either way, the layout comes first. A sheet that stores movements instead of totals is one you can trust, with or without anything on top of it.
Common questions
Should I just type over the stock number when something sells?
No. Once you overwrite a number, you lose the record of why it changed, and two people editing at once will undo each other. Add a row to a movements tab instead and let a formula work out the total.
How do I handle returns or damaged stock?
Record them as movements too. A customer return is a row with type In. Damaged or lost stock is a row with type Out and a note saying why. The note column is what makes the sheet useful six months later.
How many products can a Google Sheets stock tracker handle?
A few hundred products and a few thousand movement rows work comfortably. Google's own limit is 10 million cells per spreadsheet, but sheets full of whole-column formulas get slow long before that. If it starts to lag, archive last year's movements to another file and carry the closing stock forward as opening stock.
Can I use this layout in Excel?
Yes. SUMIFS and IF work the same way in Excel, and the two-tab layout is identical.