How to Track Inventory in Google Sheets (Beginner Guide + Template)
Google Sheets is free, works in a browser, and is surprisingly good at tracking inventory for a small business. You don't need to be a spreadsheet expert. If you can type in a table and copy a formula, you can build a tracker that tells you what is in stock and what to reorder.
This beginner guide walks through it from a blank sheet. You will need a free Google account.
The one idea that makes it work
Many people start by making a single table with a "quantity" column and typing over the number every time something sells. It feels simple, but it fails fast: you can't see what happened, mistakes are invisible, and two people editing at once overwrite each other.
The better approach uses two tabs:
- an Items tab: one row per product, with a starting quantity,
- a Movements tab: one row per change (in or out).
The sheet then calculates stock on hand from the movements. You never type over a total again.
Step 1: Create the sheet
Go to sheets.google.com, start a blank spreadsheet and name it something like "Inventory 2026". Rename the first tab Items and add a second tab called Movements (use the + button at the bottom left).
Step 2: Set up the Items tab
In row 1, type these headers:
| A: Item | B: Category | C: Unit | D: Opening qty | E: Reorder level | F: On hand | G: Status |
|---|
Fill in your products from row 2 down. For the opening quantity, use a real count of the shelf today. For the reorder level, think about how many you sell while waiting for a new delivery.
Tip: freeze the header row with View > Freeze > 1 row, so it stays visible as you scroll.
Step 3: Set up the Movements tab
Headers in row 1:
| A: Date | B: Item | C: Type | D: Qty | E: Note |
|---|
Now add dropdowns so entries stay consistent:
- Select column B, choose Data > Data validation, add a rule "Dropdown (from a range)" and pick Items!A2:A.
- Select column C and add a dropdown with the values In and Out.
Dropdowns are the single best way to stop typos from breaking your totals.
Step 4: Calculate On hand
On the Items tab, in F2, enter:
=D2 + SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "In") - SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "Out")
Copy it down for all items. Each item now shows opening quantity plus everything in, minus everything out.
Step 5: Add a status and colours
In G2:
=IF(F2<0, "NEGATIVE STOCK", IF(F2=0, "OUT OF STOCK", IF(F2<=E2, "REORDER", "OK")))
Then use Format > Conditional formatting on column G: yellow when the text is REORDER, red when it is OUT OF STOCK. Now a quick glance shows what needs attention. Use Data > Create a filter to show only REORDER rows when you are writing a supplier order.
Step 6: Use it every day
- Add an In row when a delivery is unpacked.
- Add Out rows for sales or use, at least once a day.
- Record damaged or lost items as Out with a note.
- Once a month, count a few items and add a correcting row if the shelf disagrees.
Common beginner mistakes
- Typing over totals. Never edit the On hand column; it is a formula.
- Renaming items on one tab only. If you rename an item on the Items tab, older movements still use the old name and stop counting. Rename both, or use item codes.
- Inserting rows inside formula ranges and then wondering why totals changed. Add new items at the bottom.
- Half-filled movement rows. A row with no quantity or no type can silently skew your numbers. Check the last rows daily.
- Sharing with edit access to everyone. Give edit access only to the people who record stock.
What Google Sheets can't do
A spreadsheet tracks quantities well. It is not a till, it won't scan barcodes on its own, and it isn't designed for valuation, profit reports or many warehouses. If you need those, look at inventory or POS software.
Related guides
- Stock in/out Google Sheets template
- How to record stock in and out
- Shop inventory book vs Google Sheet
Quick recap
- Use two tabs: Items and Movements.
- Let formulas calculate On hand; never type over totals.
- Add dropdowns and a REORDER / OUT OF STOCK status.
- Record every movement, including damage, and count the shelf monthly.