A simple inventory spreadsheet needs two tabs: a Products tab with one row per item and a current-stock column, and a Movements tab where you log every item that comes in or goes out. A SUMIFS formula adds up the movements for each product, an IF formula flags anything at or below its minimum, and you log each change the moment it happens. That is enough for most small businesses that track anything from a few dozen to a few hundred items.
The Products tab: columns to include
Build it in Google Sheets, which is free and can be updated from your phone (it is part of our list of free tools for small businesses), or in Excel. Name the first tab "Products" and add these columns:
- Code (A) and Product name (B). Pick a pattern for codes, for example TSH-M-BLU for a medium blue T-shirt;
- Category (C), so you can filter and compare by group;
- Unit cost (D), what you pay, and Sale price (E);
- Starting stock (F), the count on day one;
- Total in (G) and Total out (H), both calculated from the Movements tab;
- Current stock (I): starting stock plus in, minus out;
- Minimum stock (J), your reorder point, explained below;
- Status (K) and Stock value (L), both calculated;
- Supplier (M) and Lead time (N), the days between ordering and receiving;
- Expiry date (O), only if you sell food, cosmetics or anything else that expires.
Once the formulas are in place, a few rows look like this (the numbers are examples):
| Code | Product | Unit cost | Start | In | Out | Current | Minimum | Status |
|---|---|---|---|---|---|---|---|---|
| TSH-M-BLU | T-shirt, M, blue | $6.00 | 20 | 30 | 38 | 12 | 10 | OK |
| MUG-WHT | Mug, white | $3.50 | 10 | 0 | 6 | 4 | 6 | Reorder |
| CAN-VAN | Candle, vanilla | $4.00 | 8 | 12 | 9 | 11 | 8 | OK |
The Movements tab: one row per event
Add a second tab called "Movements". Each row is one event, with these columns: Date (A), Product code (B), Type (C, either In or Out), Quantity (D), Reason (E: purchase, sale, loss, giveaway, return) and Note (F). Turn column C into a dropdown list with data validation, so a typo cannot quietly break your totals.
Then let the Products tab do the math. Enter these formulas in row 2 and copy them down. The same ones work in Google Sheets and in Excel:
| Cell (row 2) | Formula | What it does |
|---|---|---|
| G2 | =SUMIFS(Movements!$D:$D, Movements!$B:$B, $A2, Movements!$C:$C, "In") | Adds up everything received for this product code |
| H2 | =SUMIFS(Movements!$D:$D, Movements!$B:$B, $A2, Movements!$C:$C, "Out") | Adds up everything that left |
| I2 | =F2+G2-H2 | Current stock |
| K2 | =IF(I2<=J2,"Reorder","OK") | Shows Reorder once stock reaches the minimum |
| L2 | =I2*D2 | Money sitting on the shelf for this product |
| Any empty cell | =SUM(L2:L500) | Total stock value |
If you copy the Status formula into empty rows, wrap it as =IF(A2="","",IF(I2<=J2,"Reorder","OK")) so blank rows stay blank. Three habits make the whole thing work:
- Log it on the spot. Received a delivery, log it. Made a sale, log it. Do it from your phone;
- Everything goes through the sheet. Giveaways, your own use, breakage and returns to suppliers are outflows too. That is where "missing" stock shows up;
- One person responsible for logging on each shift, if more than one person works in the business.
Minimum stock with an automatic alert
Minimum stock is the level at which you must reorder so you do not run out. A practical way to set it is average daily sales x lead time in days + a safety buffer. If you sell 2 units a day and your supplier delivers in 7 days, the minimum is about 14 plus a buffer, so call it 20.
To make low stock impossible to miss, use conditional formatting. In Google Sheets, select your rows, open Format, then Conditional formatting, choose "Custom formula is" and enter =$I2<=$J2, then pick a red fill. In Excel, go to Home, Conditional Formatting, New Rule, and "Use a formula to determine which cells to format" with the same formula. Google's help page on conditional formatting rules shows each step. Open the sheet every morning: the red rows are your shopping list for the day.
Count the shelves on a schedule
If the sheet says one thing and the shelf says another, only a physical count shows which is right. A routine that works: a full count once a month, outside busy hours, plus a weekly check of your 10 fastest-moving items. Enter any difference as an adjustment in the Movements tab with a reason, then find out what happened. Was it a logging mistake, breakage nobody wrote down, or theft? A difference that keeps showing up on the same product is always a sign to investigate.
What your numbers start to show
After a month or two of data, the spreadsheet starts to tell you things:
- Your best sellers. A few products usually account for most of your sales. They should never run out, so give them roomier minimums;
- Slow movers. An item with no sales in 60 to 90 days is cash sitting on a shelf. Put it on promotion, bundle it into a kit or stop reordering it;
- Stock value. Quantity times cost, added up, shows how much money is on your shelves. You need that number to decide what to buy and to judge any loan or credit line;
- Seasonality. The strong months in each category tell you what to stock for the next busy period and when to promote it, for example with organic Instagram posts.
Mistakes that cost money
- "I'll log it later." The number one killer of any inventory system;
- Not logging losses and giveaways. Stock "disappears" and the numbers never add up;
- Ignoring real cost. Shipping and import duties on a purchase belong in the unit cost. Leave them out and your margin is an illusion;
- Buying on gut feeling. The sheet exists to replace "I think we're running low";
- Keeping separate versions on paper, in a notebook and on your phone. Keep a single source of truth in the cloud, with a backup, as in our guide to backing up your business phone.
When the business grows, with several sales channels, accounting integration and a team, inventory software starts to pay for itself. Until then, a sharp spreadsheet, logging on the spot and counting on schedule are what separate profit from leakage.
Frequently asked questions
Can I track inventory in Google Sheets for free?
Yes. Google Sheets is free and works on your phone. A Products tab plus a Movements tab, with SUMIFS and IF formulas, covers a small business with anything from a few dozen to a few hundred items.
How do I calculate the reorder point?
Multiply your average daily sales by the supplier's lead time in days, then add a safety buffer. At 2 units a day and a 7-day lead time, that is 14 plus a buffer, so about 20.
How often should I count physical stock?
Do a full count once a month and a quick weekly check of your fastest-moving items. A difference caught early is a simple adjustment; one caught late is an unexplained loss.
Sources: Google Docs Editors Help: SUMIFS function · Microsoft Support: SUMIFS function · Google Docs Editors Help: conditional formatting rules in Google Sheets
