How do you track inventory in Google Sheets?
An inventory sheet lists each SKU with quantity on hand, location, reorder threshold, and unit cost. Receipts and shipments post as signed adjustments on a Movements log so on-hand balance is calculated, not typed from memory.
When to use it
Use Sheets inventory for small warehouses, event merch, cafe pantries, or ecommerce shops under a few hundred SKUs where barcode scanners and ERP systems are overkill.
When to skip it
Skip Sheets when you need real-time multi-location sync, lot traceability, or concurrent high-speed scanning at scale. Dedicated inventory software or an API-backed system fits better.
How it works
-
1
{:"Create an Items tab"=>"SKU, name, category, unit cost, reorder point, supplier."}
-
2
{:"Add Movements tab"=>"date, SKU, qty in or out, reference (PO, order id), user."}
-
3
Calculate on-hand with SUMIF on movements per SKU, or opening balance plus net movements.
-
4
Highlight low stock with conditional formatting when on-hand is below reorder point.
-
5
Use data validation on SKU column in movement forms to prevent typos.
-
6
Reconcile physically each period and post an adjustment row when counts disagree.
Examples in Google Sheets
Ecommerce SKU list
Shopify export imports nightly; Movements tab logs picks; dashboard flags SKUs under seven days of cover.
Office supplies closet
Simple IN/OUT dropdown per item with monthly snapshot tab pasted as values for accounting.
Event booth inventory
Each show starts with a transfer OUT row; returns post IN rows so central warehouse totals stay true.
Better Sheets resources
Common mistakes
-
Editing on-hand quantities directly instead of logging movements, losing audit trail.
-
Duplicate SKUs with slightly different spellings splitting quantity across two rows.
-
Negative stock without investigation because returns were never recorded.
-
Sorting Movements tab and breaking running balance formulas tied to row order.
-
Sharing edit link publicly so anyone can change stock without identification.
Frequently asked questions
- Barcodes with Google Sheets?
- Store barcode numbers as text. Mobile scanner keyboards type into a cell; lookup SKU with VLOOKUP.
- Multi-location inventory?
- Add Location column on Movements and pivot or SUMIFS on-hand per location.
- FIFO in Sheets?
- Possible with careful lot tabs and scripting. Simple average cost is easier in pure formulas.
- Link to Shopify or WooCommerce?
- Export or API pull into a tab on schedule. Sheets is rarely the live stock master at high volume.
- Alert low stock?
- Conditional formatting plus optional MailApp email from a daily trigger when any SKU is below threshold.
- Cycle count workflow?
- Count sheet compares physical qty to formula qty; post adjustment movements for variances only.
- Protect SKU master?
- Protect Items tab; allow edits only on Movements or via a form that appends rows.
- Units of measure?
- Document base unit per SKU and convert cases to each with a multiplier column to avoid silent errors.
Related Tutorials
Watch how inventory tracking in Sheets works
Simple Inventory Management Automations
Related blog posts
Guides that explain inventory tracking in Sheets in more depth.
Sell a Wholesaler Resources Database Built in Google Sheets
Build a wholesaler resource database in Google Sheets with order management, pricing calculations, and supplier comparisons, and sell it to new wholesalers.
Read post →Sell a Physical Product Flipping Calculator in Google Sheets
Build a Google Sheets calculator and inventory tracker for product flippers that shows real profit after fees, shipping, and storage, and flags stale stock.
Read post →Automated Project Management in Google Sheets
When you have multiple people working on multiple tasks, and you want everyone to be on the page, what do you do? You can use a Google Sheet. But managing projects can be an intimidating task in Google Sheets. It's a lot of data entry, right? Especially when you have multiple people working on different tasks with different statuses across different projects. Data entry for multiple projects, with different people, tasks, and statuses – it gets messy fast! I'm no stranger to these issues...
Read post →Related terms
trackers
A tracker is a living list you update over time: tasks, leads, inventory, habits, or bugs. Good trackers use one row per item, clear statuses, and columns that sort and filter without cleanup scripts. They often feed a dashboard tab when the team outgrows scanning raw rows.
Read guide →normalizing data in Sheets
Normalizing means making inconsistent inputs follow one reliable shape: same date format, trimmed text, unique keys, one fact per row, and separate columns for categories that were crammed into one field. Clean data makes VLOOKUP, QUERY, and dashboards dependable.
Read guide →conditional formatting
Conditional formatting changes how cells look when they meet rules you define, such as values above a target or dates in the past. It helps scanners spot problems fast without writing values into extra helper columns. Rules can be simple comparisons or custom formulas that reference other sheets.
Read guide →reports from Google Sheets
A report turns raw data into a readable snapshot: summary metrics, charts, and tables formatted for an audience. Separate source data tabs from a Report tab that uses QUERY, pivot tables, or SUMIF so you refresh inputs without rebuilding layout every week.
Read guide →Done reading about inventory tracking in Sheets?
Membership unlocks 600+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.