To track inventory in Google Sheets, separate the product catalog, a log of movements (stock in, stock out and adjustments) and a stock sheet calculated with formulas, without ever editing quantities by hand. Add dropdown lists, protected ranges and conditional formatting to flag minimum levels. When you need to scan codes with a phone or have several people entering data, connect the sheet to a lightweight app.
- Never edit stock levels by hand: calculate them from the movements.
- Use three sheets: catalog, movements and stock.
- Dropdown lists keep the same product from showing up under three names.
- Conditional formatting turns anything at its minimum red.
- To scan with a phone, connect the sheet to an app like AppSheet or a microapp.
- When you need lots, expiration dates or exact costing, it's time for a system.
Why most spreadsheet inventories fail
The typical spreadsheet inventory is a list of products with a "stock" column that someone updates by hand: merchandise arrives, add; something sells, subtract. It works for the first week. Then the usual happens:
- Two people update the same product and one overwrites the other.
- Someone types "Screw 1/4", someone else "screw 1/4 in" and another "SCRW 1/4", and now there are three products.
- Nobody knows why stock dropped from 40 to 12: there's no history.
- The physical count doesn't match and it gets "adjusted" with no explanation.
The fix isn't changing tools; it's changing the method. A reliable inventory records movements, not stock levels. Stock is a result you calculate, just like a bank account balance is calculated from deposits and withdrawals.
The structure: three sheets that don't mix
| Sheet | What it contains | Who edits it |
|---|---|---|
| Catalog | SKU, name, category, unit, location, minimum, supplier, barcode | Only the person in charge of inventory |
| Movements | Date, SKU, type (in, out, adjustment, transfer), quantity, location, who logged it, reference (invoice, receipt, order) | Whoever receives, sells or ships; they only add rows, never delete |
| Stock | SKU, name and stock calculated by formula, with status against the minimum | Nobody: it's read-only |
This separation fixes all four problems above: the name comes from the catalog, every change stays in the movement history and stock can't be "massaged" by hand.
If you have several locations or warehouses, add a location column to movements and calculate stock by SKU and location. A transfer is two movements: one out at the origin and one in at the destination, with the same reference.
How to get started if you have nothing today
Kickoff is where most inventories fail, because people try to enter everything in one weekend. Do it this way instead:
- Build the catalog first. Start with the products you sell most or that represent the most money; in many businesses, a small fraction of the catalog accounts for most of the sales. Define short, consistent SKUs, for example category plus number.
- Do an initial count by zone. Aisle by aisle or shelf by shelf, and log each count as a dated "opening inventory" movement.
- Set realistic minimums. Think about how much you sell in the time it takes your supplier to restock, plus a safety margin. Adjust them after a month with real data.
- Start logging movements from day one. Even if the catalog isn't complete, whatever is already set up should only change through logged movements.
- Add the rest of the catalog in stages. One category a week is a pace a small team can sustain.
In two or three weeks you'll have a reliable inventory of what matters most, instead of a complete one nobody believes. If it helps to prioritize the kickoff tasks, try the Eisenhower matrix.
The formulas that do the work
The simplest approach is to store quantities with a sign: positive for stock in, negative for stock out. In movements, add a "signed quantity" column that multiplies by negative one when the type is out. Then, in the stock sheet:
=SUMIF(Movements!B:B, A2, Movements!G:G)
Here column B of movements is the SKU, A2 is the product's SKU in the stock sheet and G is the signed quantity. If your Sheets is set to Spanish, the function is called SUMAR.SI. For stock by location, use SUMIFS (SUMAR.SI.CONJUNTO) with a second criterion.
Other useful pieces:
- VLOOKUP or XLOOKUP to pull the name and minimum from the catalog.
- A status column that says "Reorder" when stock is less than or equal to the minimum.
- QUERY to build reports, for example the month's best sellers or the movements for a given supplier.
- Pivot tables to see stock out by week or by location.
Safeguards that prevent errors
- Data validation. In movements, the SKU must be picked from a dropdown fed by the catalog. The type, from a fixed list: in, out, adjustment, transfer.
- Protected ranges. Catalog and stock protected so only the person in charge can edit them. Formulas locked.
- Conditional formatting. Red when a product is at or below its minimum; yellow when it's within 20% of it.
- Adjustments with a required reason. Shrinkage, expiration, theft, data-entry error. No reason, no adjustment.
- Version history. Sheets keeps previous versions; use it to see who changed what and recover something deleted.
- Cycle counting. Instead of an annual physical inventory that brings the business to a halt, count a different category each week and log the differences as adjustments.
Entering data from your phone and scanning codes
Typing into a spreadsheet on a phone is awkward and invites mistakes. There are three ways to solve it:
- Google Forms. One form per movement type that writes to the movements sheet. Easy, but it doesn't scan codes.
- AppSheet. Google's no-code tool builds an app on top of your sheet, with barcode scanning through the camera. A good option if someone on your team has time to set it up.
- A ready-made microapp. MonkeyInventario scans barcodes and QR codes with the phone's camera, saves everything to a Google Sheet that belongs to you and sends a WhatsApp alert when something hits its minimum.
All three keep the same movement logic; only the data-entry convenience changes. If you're interested in the web vs. installed app debate, we cover it in the guide to native apps vs. PWAs.
Automatic low-stock alerts
An inventory nobody checks is useless. What helps is having the sheet alert you. Options from least to most effort:
- Google Sheets change notifications by email: not very precise, but immediate.
- Apps Script with a time-driven trigger: every morning it checks the status column and emails the list of products to reorder.
- Automation with n8n, Make or Zapier that reads the sheet and sends a WhatsApp message to the purchasing manager or the supplier. We compare those platforms in the guide to n8n vs. Zapier vs. Make.
For a kitchen in Torreón, for example, a 7:00 a.m. alert listing ingredients below minimum lets you order from the supplier before the lunch service, instead of discovering at 2:00 p.m. that you're out of avocado.
Example: an auto parts store with two locations
An auto parts store in Torreón and Gómez Palacio kept one Excel file per location, and the two were emailed back and forth every Friday. Salespeople called the other location to ask whether a part was in stock, and sometimes the answer was wrong.
We moved everything to a single Google Sheet with the three-sheet structure, a location column and phone-based data entry by scanning each part's code. The result: stock for both locations available on the spot, transfers logged with a reference and a daily restock list. They didn't need an ERP; they needed a method. What the salespeople appreciated most was no longer calling the other store: now they check stock on their phone while serving the customer at the counter, and if the part is at the other location, they log the transfer right then.
Simple projects like this are part of what we do in automation, and they're often the first step before a bigger system.
When Google Sheets isn't enough anymore
Sheets holds up well, but it has limits. Consider a system when:
- You manage lots, serial numbers or expiration dates and need to ship whatever expires first.
- You need exact costing (weighted average or FIFO) for your accounting.
- Inventory has to sync in real time with your point of sale and your online store.
- Movements reach hundreds of thousands of rows and the sheet slows down. Sheets has a limit of 10 million cells per file, but performance drops well before that.
- Many people enter data at the same time and you need granular permissions by location.
At that point, read the guide to custom software vs. Excel or check out our software development service. The good news: if your sheet is already organized by movements, migrating to a system is much easier. And don't forget to back up the sheet; even though it lives in the cloud, a mass deletion syncs just the same, as we explain in the guide to backups.
