Low-Stock Alerts for a Small Shop Using Google Sheets

Running out of a popular product costs you sales and frustrates customers who came specifically for that item. You can set up inventory alerts in Google Sheets that flag low-stock items automatically and even email you when something needs reordering. The whole setup takes about an hour and costs nothing.

Why Stock-Outs Hurt More Than You Think

When a customer walks into your shop in Gikomba, Nyamakima, or your local town and asks for a product you have run out of, two things happen. First, you lose that sale. Second, there is a real chance the customer goes to a competitor and discovers they prefer shopping there. Stock-outs do not just cost one transaction; they can cost a customer permanently.

Most small shop owners in Kenya track inventory one of two ways: by memory ("I know we are running low on cooking oil") or by eyeballing the shelves ("that shelf looks empty, time to order"). Both methods work until they do not. You forget, you get busy, or you miscount. The result is the same: you discover something is out of stock only when a customer asks for it.

A simple Google Sheets inventory tracker with automatic alerts catches low stock before it becomes no stock. You do not need special software. You do not need a barcode scanner. You need a spreadsheet, discipline in updating it, and a few formulas.

What You Are Building

The system has three parts.

Part 1: Inventory sheet. A list of every product you stock, with columns for current quantity, reorder point (the number at which you should reorder), and supplier info.

Part 2: Visual alerts. Conditional formatting that turns cells red when stock drops below the reorder point. You open the sheet and immediately see what needs attention.

Part 3: Email alerts (optional). A Google Apps Script that checks stock levels once a day and emails you a list of items that need reordering.

Step 1: Create Your Inventory Sheet

Open Google Sheets and create a new spreadsheet called "Shop Inventory."

Set up these columns:

Product Name	Category	Unit	Current Stock	Reorder Point	Supplier	Supplier Phone	Unit Cost (KES)	Status
Cooking Oil (2L)	Groceries	Bottle	12	10	Kapa Industries	0712 XXX	380	OK
Maize Flour (2kg)	Groceries	Packet	3	15	Distributor A	0723 XXX	180	LOW
Phone Cases (Assorted)	Accessories	Piece	25	10	Supplier B	0733 XXX	150	OK

Column explanations:

• Product Name: Be specific. "Cooking Oil" is not enough if you stock three brands. Use "Kimbo 2L," "Fresh Fri 1L," etc.
• Category: Group similar products. This helps when you are scanning the sheet.
• Unit: What counts as one item. Bottles, packets, pieces, kilograms.
• Current Stock: The number you update regularly (more on this below).
• Reorder Point: The minimum quantity before you need to reorder. This varies by product. A fast-selling item like bread might have a reorder point of 20. A slow-moving item like a specific phone accessory might have a reorder point of 3.
• Supplier and Supplier Phone: So you can call immediately when an alert fires.
• Unit Cost: Helps you calculate the reorder value.
• Status: We will fill this automatically with a formula.

Step 2: Add the Status Formula

In the Status column, enter this formula for the first product row and drag it down:

=IF(D2="","NO DATA",IF(D2<=E2,"LOW STOCK","OK"))

This checks: if Current Stock (D2) is at or below the Reorder Point (E2), it shows "LOW STOCK." Otherwise, it shows "OK." If the cell is empty, it shows "NO DATA" so you know the count needs updating.

Step 3: Add Conditional Formatting

Select the Status column. Go to Format, then Conditional formatting.

Rule 1: Text is exactly "LOW STOCK." Set background to red and text to white. Rule 2: Text is exactly "OK." Set background to green and text to white. Rule 3: Text is exactly "NO DATA." Set background to yellow and text to black.

Now your sheet is a visual dashboard. Open it on your phone at any time and red cells jump out immediately.

Extra visual cue: Apply the same conditional formatting to the Current Stock column. Set it to highlight red when the value is less than or equal to the value in the Reorder Point column (use a custom formula: =$D2<=$E2). This makes low numbers visible even when scrolling through a long product list.

Step 4: Set Reorder Points Intelligently

The reorder point is the most important number in this system. Set it wrong and you get either constant false alarms or alerts that come too late.

How to calculate reorder points for a Kenyan shop:

Think about two things: how fast the product sells and how long it takes to restock.

Fast-moving products (bread, milk, airtime, popular snacks): These sell daily and suppliers deliver within a day. Reorder point = 1 day of sales. If you sell 20 loaves of bread a day and the baker delivers every morning, a reorder point of 10 (half a day's buffer) works.

Medium-moving products (cooking oil, sugar, soap, phone accessories): These sell every few days and restocking takes one to three days (you need to visit the wholesaler or wait for delivery). Reorder point = 3-5 days of sales.

Slow-moving products (specialty items, seasonal goods): These sell weekly or less. Restocking might take a week or more. Reorder point = 2 weeks of sales.

If you are unsure, start with a reorder point that gives you three days of runway. After a month, adjust based on how often you actually ran out.

Step 5: Set Up Email Alerts

This step is optional but useful if you do not check the sheet daily.

Open the inventory sheet, go to Extensions, then Apps Script. Create a script that:

1. Reads every row in the sheet
2. Checks if the Status column says "LOW STOCK"
3. Compiles a list of low-stock items
4. Sends you an email with the list

The email subject would be: "Low Stock Alert: [number] items need reordering."

The email body would list each low-stock item with its current stock, reorder point, supplier name, and phone number, so you can call the supplier directly from the email.

Set a daily trigger to run this script every morning at 7am. You start your day knowing exactly what needs ordering.

Important detail: Add a check so the script only sends an email if there are actually low-stock items. You do not want a daily "everything is fine" email cluttering your inbox.

Step 6: Update Stock Counts Regularly

The system is only as good as the data. If you do not update stock counts, the alerts are useless.

When to update:

• After receiving stock: When a delivery arrives, add the quantity to the Current Stock column immediately.
• At close of business: Do a quick count of fast-moving items and update the sheet. This takes 10-15 minutes.
• Weekly full count: Once a week (Sunday evening works for many shops), count everything and update all rows.

Making updates easy:

Open the Google Sheets app on your phone. Pin the inventory sheet to your home screen. Updating a number takes seconds.

If you have an assistant or shop attendant, share the sheet with them as an editor. Train them to update the count when restocking shelves.

Dealing with discrepancies: If your count does not match the sheet, investigate. Common causes: theft, unrecorded sales, miscounting during restocking, or items damaged and discarded without updating the sheet. A regular counting habit surfaces these issues early.

A Copyable Sheet Layout

Here is the complete layout you can reproduce. Create these sheets within the same spreadsheet.

Sheet 1: Inventory (main tracker)

Columns: Product Name | Category | Unit | Current Stock | Reorder Point | Supplier | Supplier Phone | Unit Cost (KES) | Status | Last Updated

Sheet 2: Reorder History

Track what you ordered and when, so you can spot patterns.

Date	Product	Quantity Ordered	Supplier	Cost (KES)	Received Date	Notes

Sheet 3: Dashboard

Summary formulas:

• Total products tracked: =COUNTA(Inventory!A2:A)
• Items currently low: =COUNTIF(Inventory!I2:I,"LOW STOCK")
• Items OK: =COUNTIF(Inventory!I2:I,"OK")
• Total inventory value: =SUMPRODUCT(Inventory!D2:D100,Inventory!H2:H100)
• Items not updated in 7+ days: count rows where Last Updated is more than 7 days ago

Tips for Different Types of Shops

Grocery shops and dukas: Focus on tracking your top 20-30 products first. These are the ones that generate most of your revenue and the ones customers will leave for if you are out. You can add the rest over time.

Phone accessory shops: Stock changes fast. Track by model (not just "phone cases" but "iPhone 15 case, Samsung A54 case"). Set low reorder points since these items take time to restock from suppliers in Nairobi CBD.

Hardware shops: Many items are bulky and slow-moving. Weekly counts are sufficient. Focus reorder alerts on cement, paint, and other items with reliable weekly demand.

Cosmetics and beauty shops: Products have expiry dates. Add an "Expiry Date" column and set up a separate conditional formatting rule to flag items expiring within 30 days. This prevents both stock-outs and waste.

Scaling Up

Once this system is working, you can extend it.

Add a sales tracking sheet. Log daily sales for each product. After a month, you have real data on sales velocity, which lets you set reorder points precisely instead of guessing.

Connect to ordering. When you get a low-stock email, it could include a pre-written order message for each supplier. Copy the message, paste it into WhatsApp, and send. This cuts ordering time from minutes to seconds.

Graduate to dedicated software. If your shop grows beyond 200 products, consider tools like Sauti (Kenyan inventory management app), Tally, or even a simple POS system. The principles stay the same; the tool just handles more volume. Our AI and Automation for Beginners course covers these progression paths.

FAQ

How many products should I track to start?

Start with your top 20-30 products by sales volume. These are the items whose absence hurts the most. Once you are comfortable updating 20-30 items regularly, expand to the next 30. Trying to track everything at once leads to overwhelm and abandoned spreadsheets.

What if I do not have a smartphone to access Google Sheets?

If you have a basic phone and a computer at home or access to one at a cyber cafe, you can update the sheet on the computer. For daily quick updates, ask an assistant with a smartphone to help. Alternatively, keep a paper tally during the day and update the sheet once in the evening when you have access.

My stock changes many times a day. How do I keep up?

For very high-turnover items (like airtime or bread), do not try to update after every sale. Instead, count remaining stock at close of business and update once. The alert system is designed to catch you before you run out, not to give real-time stock counts. If you need real-time tracking, you need a POS system.

Can I share this sheet with my supplier so they know when to deliver?

You can, but we recommend against giving suppliers edit access to your inventory data. Instead, set up the email alert to also send a notification to your supplier when specific items are low. Or simply forward the morning low-stock email to the relevant supplier with a message: "Please deliver the following items."