A provisions shop owner knows the feeling: a customer asks for a carton of noodles, you say it's available, and then you find the last one sold on Tuesday. A stock tracker in Google Sheets fixes that without any paid software. It works on a phone, saves automatically, and you can build it in an evening. It's also a service you can sell to other shop owners.
This guide builds a three-tab tracker: a product list, a log of stock coming in and going out, and a summary that tells you what to reorder. Everything uses basic formulas.
What the tracker must answer
Before you type anything, decide what questions the sheet should answer. For a small shop, they are usually these:
- How many of each item do I have right now?
- Which items are almost finished and need reordering?
- What is my stock worth at cost price?
- Which items sold most this week?
If a column doesn't help answer one of these, leave it out. Overbuilt sheets get abandoned in two weeks.
Step 1: Create the Products tab
Make a new Google Sheet and rename the first tab Products. Put these headings in row 1:
- A: Item code (short and unique, like NOO-IND-70 for a 70g noodle pack)
- B: Item name
- C: Unit (piece, carton, bag, bottle)
- D: Cost price (what you pay per unit)
- E: Selling price
- F: Reorder level (the quantity at which you buy more)
- G: Current stock (a formula we add in Step 3)
- H: Status (formula)
Pick one unit per item and stick to it. If you buy cartons but sell singles, either track in singles or make two rows. Mixing units is the most common reason stock counts go wrong.
Step 2: Create the Log tab
Add a second tab called Log. Every movement of stock gets one row:
- A: Date
- B: Item code
- C: Type (IN or OUT)
- D: Quantity
- E: Note (supplier name, "sold", "damaged", "taken home")
Use data validation so people can't mistype. Select column C, go to Data, then Data validation, add a dropdown with IN and OUT. Do the same for column B, using the item codes from the Products tab as the range. A dropdown of codes means "NOO-IND-70" can never become "noodle 70" in one row and "Indomie" in another.
Record opening stock first. Count every item on the shelf and in the store, then enter each as an IN row with the note "opening count".
Step 3: Add the formulas
In Products, cell G2, enter:
=SUMIFS(Log!D:D, Log!B:B, A2, Log!C:C, "IN") - SUMIFS(Log!D:D, Log!B:B, A2, Log!C:C, "OUT")
This adds all IN quantities for that item code and subtracts all OUT quantities. Drag it down the column.
In H2, enter:
=IF(G2<=F2, "REORDER", "OK")
Now add conditional formatting: select column H, go to Format, Conditional formatting, and colour any cell that says REORDER red. You will spot problems at a glance, even on a small phone screen.
For stock value at cost, add column I with =G2*D2. Sum the column at the top to see how much money is sitting on your shelves.
Step 4: A worked example
Here is a hypothetical case. Ada runs a small shop and tracks a 5kg bag of rice. She buys at ₦1,000 less than she sells, but the numbers below are made up for illustration.
| Date | Item | Type | Qty | Note |
|---|---|---|---|---|
| 1 Nov | RICE-5KG | IN | 20 | Opening count |
| 3 Nov | RICE-5KG | OUT | 6 | Sold |
| 5 Nov | RICE-5KG | OUT | 9 | Sold |
| 6 Nov | RICE-5KG | OUT | 1 | Damaged bag |
Current stock: 20 − 6 − 9 − 1 = 4. If her reorder level is 5, the status shows REORDER. If it takes her supplier three days to deliver and she sells about 3 bags a day, her reorder level should be at least 9, not 5. A good rule: reorder level equals daily sales times days to restock, plus a small buffer.
Step 5: Build a weekly summary
Add a third tab called Summary. Two useful blocks:
- Reorder list: =FILTER(Products!B2:B, Products!H2:H="REORDER") lists every item that needs buying.
- Weekly units sold: =SUMIFS(Log!D:D, Log!B:B, "RICE-5KG", Log!C:C, "OUT", Log!A:A, ">="&TODAY()-7) shows units sold in the last seven days for that item.
Copy the second formula for your top ten items. Skip charts at first. A sorted list of top sellers is more useful than a pie chart. If you later want visuals, a simple bar chart of the top ten is enough.
Making it work in daily shop life
The formulas are easy. The habit is hard. These practices keep the sheet honest:
- Log sales in batches. At closing time, write the day's sales from your notebook or receipts into the Log. Don't try to type during busy hours.
- Record losses. Damaged, expired and personal-use items must be logged as OUT, or your count will drift from the shelf.
- Count one section weekly. Rotate through shelves and compare physical count with the sheet. Fix differences with an adjustment row and a note.
- Use offline mode. Where data or power is unreliable, enable offline access in the Google Sheets or Drive app settings so you can keep logging and let it sync later.
- Limit who can edit. Give staff edit access to the Log tab only, using protected ranges on Products and Summary.
Common mistakes
- Typing the current stock by hand. It breaks the link to the Log. Always let the formula calculate it.
- Blank rows or text in the quantity column. Numbers entered as text, like "5 pcs", are ignored by SUMIFS. Enter 5 only.
- Changing item codes later. Old log rows then stop matching. Add a new item instead.
- Tracking too many items on day one. Start with your 30 fastest sellers and add more as the habit settles.
Using AI to help, safely
A general-purpose AI chat tool can explain a formula or fix an error message if you paste the formula and describe what went wrong. Use made-up sample rows rather than real supplier names, customer details or phone numbers. Always test any suggested formula on three or four rows where you already know the right answer. For more on this, see Using AI at Work Without Leaking Customer Data.
Turning it into a paid service
Many shop owners will pay for someone to set this up and train their staff. Price by the setup work and the number of items, not by hours alone. Our guide on pricing a spreadsheet cleanup job helps with that, and reconciling mobile money and bank transfers is a natural second service once the stock sheet is running.
Frequently asked questions
Can I use Excel instead?
Yes. SUMIFS works the same way. FILTER is available in newer versions of Excel, but Google Sheets is easier to share and use from a phone.
What if I sell the same item at different prices?
Keep the stock log by quantity only. Record price differences in a separate sales sheet, so stock counts stay simple.
How do I handle items bought in cartons but sold singly?
Track in the smallest unit you sell. When you buy a carton of 24, log 24 as IN. Set the cost price per single unit by dividing the carton price by 24.
Is Google Sheets safe for shop records?
It's reasonable for stock records. Use a strong password, turn on two-step verification on your Google account, and don't share the file link with "anyone with the link" access.
Next step: tonight, list your 30 fastest-selling items with their units and prices, and enter an opening count tomorrow morning before you open.
Results from any side hustle or online work vary widely, and nothing here is a promise of income. Be wary of anyone who asks you to pay upfront to start earning.