Google Sheets Inventory for a Small Shop — Free Stock Register With Low-Stock Alerts (Template Inside)

Quick answer: Create one Google Sheet with three tabs — Items (code, name, category, unit, reorder level, cost, MRP), Stock In (date, item code, quantity, supplier, bill number) and Stock Out (date, item code, quantity, reason). On the Items tab, current stock = opening stock + SUMIF of Stock In − SUMIF of Stock Out, and conditional formatting turns rows red when stock falls below the reorder level. Staff enter purchases and sales on their phones with the Google Sheets app; the owner sees stock from anywhere. Move to billing software when you cross a few hundred bills a day or need GST invoices from the same system.

Most small shops in India still track stock in a notebook — or in the owner's memory. That works until a best-seller runs out on a Saturday, money gets stuck in slow items, or a staff member leaves. A free Google Sheet fixes most of this in an afternoon, and it works on any phone.

Why Google Sheets (and not a notebook or Excel file)

Need	Notebook	Excel on one PC	Google Sheets
Works on staff phones	No	No	Yes
Automatic totals	No	Yes	Yes
Owner sees it from home	No	No	Yes
Version history / undo	No	Limited	Yes
Cost	Free	Licence	Free

Google Sheets saves every change with a history, so if someone deletes a row by mistake you can restore it from File → Version history.

Step 1: Create the Items tab

This is your master list. One row per product.

Column	Example	Notes
A: Item code	KR-001	Short, unique; can be the barcode number
B: Item name	Toor Dal 1 kg	As customers ask for it
C: Category	Pulses	Helps filtering
D: Unit	Packet	kg, piece, box, strip
E: Opening stock	40	Count once when you start
F: Stock in	(formula)
G: Stock out	(formula)
H: Current stock	(formula)
I: Reorder level	10	When to order again
J: Cost price	128	Per unit
K: MRP / selling price	150
L: Stock value	(formula)	Current stock × cost

Start with your top 50–100 items — the ones that sell every week. You can add the rest later. Trying to list 2,000 items on day one is why most inventory projects fail.

Step 2: Stock In and Stock Out tabs

Stock In — one row for every purchase:

Date	Item code	Quantity	Supplier	Bill no.	Cost per unit

Stock Out — one row for sales or other removals:

Date	Item code	Quantity	Reason

Reasons can be Sale, Damaged, Expired, Returned to supplier, Free sample. Tracking reasons shows you where money leaks — many shops are surprised how much stock expires.

If you already use billing software that tracks sales, you only need Stock In; export daily sales and paste them in Stock Out once a day.

Step 3: Formulas that do the work

On the Items tab, row 2:

• Stock in (F2): =SUMIF('Stock In'!B:B, A2, 'Stock In'!C:C)
• Stock out (G2): =SUMIF('Stock Out'!B:B, A2, 'Stock Out'!C:C)
• Current stock (H2): =E2+F2-G2
• Stock value (L2): =H2*J2
• Margin % (add column M): =IF(K2>0,(K2-J2)/K2,0) formatted as a percentage

Drag the formulas down for every item. Add a total at the bottom of column L — that's how much money is sitting on your shelves.

Step 4: Low-stock alerts

1. Select columns A to L on the Items tab.
2. Format → Conditional formatting → Custom formula: =$H2<=$I2
3. Choose a red fill.

Now every item at or below its reorder level turns red. Create a filter view called "Order today" that shows only red rows — send a screenshot to your supplier on WhatsApp.

For a shopping list automatically, add a fourth tab with: =FILTER(Items!A:I, Items!H:H<=Items!I:I).

Step 5: Make phone entry easy for staff

• Install the Google Sheets app on staff phones and share the sheet with their Gmail (Editor on Stock In/Out, View on Items).
• Protect the Items tab formulas: Data → Protect sheets and ranges so only the owner can change them.
• Add data validation on the Item code column of Stock In/Out: Data → Data validation → Dropdown from a range → Items!A:A. Staff pick from a list instead of typing.
• Or use a Google Form for entries — staff fill a simple form and responses land in the sheet automatically. See Google Forms for small business.

Barcode scanning from a phone

If your products have barcodes, use the barcode number as the item code. Several free phone keyboard and scanner apps can type a scanned barcode straight into a cell, so staff scan instead of searching. Test with 10 items before rolling it out.

Weekly and monthly routines

Every week (15 minutes):

• Check the "Order today" view and place orders.
• Spot-check 10 random items: does the shelf count match the sheet? Fix differences and note why.

Every month (1 hour):

• Sort by stock value — the top 20 items usually hold most of your money.
• Find dead stock: items with no Stock Out in 60 days. Run a combo offer, return to supplier or stop ordering.
• Adjust reorder levels for seasonal items before festivals, weddings or school reopening.

Reports you can build in minutes

• Pivot table (Insert → Pivot table) on Stock Out: quantity by item by month — your real best-sellers.
• Supplier spend: pivot on Stock In by supplier.
• Expiry and damage loss: filter Stock Out by reason.

These numbers help in supplier negotiations and decide what to promote. Your best-sellers are also perfect subjects for Google Business Profile posts and your WhatsApp catalogue.

Example: a hardware store in Nashik

A two-person hardware shop listed its 120 fastest items, set reorder levels with the supplier's delivery time in mind (3 days), and had the helper enter purchases from bills each evening. In the first month they found ₹38,000 of slow stock in paints that hadn't moved since summer, and stopped running out of PVC fittings — the item customers most often walked out over.

Limits of a spreadsheet

Google Sheets is excellent up to a point. Consider billing and inventory software when:

• You make more than a few hundred bills a day.
• You need GST invoices, e-way bills or e-invoicing linked to stock.
• You have multiple branches transferring stock.
• You need batch and expiry tracking for medicines at scale.

Even then, many owners keep a sheet for quick analysis because it's flexible.

Common mistakes

• Starting with every item instead of the top sellers.
• Letting several people edit formulas.
• Not recording damaged and expired stock.
• Counting stock once and never spot-checking.
• Using different names for the same item ("Toor dal", "Tuvar dal 1kg").

Frequently asked questions

Is Google Sheets safe for business data?

It's protected by your Google account. Turn on two-step verification and share only with staff who need access.

Can it work without internet?

Yes — enable offline mode in Google Sheets on the phone. Changes sync when you're back online.

How many items can a sheet handle?

Thousands of rows comfortably. Very large sheets with many formulas can get slow; split old Stock In/Out rows into a yearly archive sheet.

Can I print a stock report?

Yes — use File → Print or download as PDF from the "Order today" filter view.

Does this connect to my Google Business Profile?

Not directly, but your best-seller list tells you which products to add to your profile's Products section and which to feature in posts.