Digital Skills Hub
Menu

How to Track Small-Shop Inventory with Google Sheets

Small shop manager tracking inventory on a laptop among stock shelves

A small shop does not always need expensive inventory software. A carefully designed spreadsheet can answer three daily questions: What is in stock? What needs reordering? And which products are moving fastest? Google Sheets can be a practical starting point for a stationery shop, kiosk, school supply room, or other small operation.

The key is to record transactions consistently. A colorful dashboard is not useful if sales and deliveries are missing from the underlying records.

Design your system around real shop activity

Before building formulas, list the events that change stock: a purchase from a supplier, a sale to a customer, a return, damaged goods, and stock adjustments after physical counting. Each event should have a date, an item code, a quantity, and a brief reason.

Important distinction: Products and units are separate information. For example, A4 paper is a product, while ream is a unit; photocopying is a service, not a stock item measured in reams. This prevents confusing reports.

Step 1: Create three sheets

Open Google Sheets and create tabs named Products, Transactions, and Dashboard. Use one row per product in the Products sheet.

Item code Product Unit Opening stock Reorder level Unit cost
ST001 A4 paper Ream 20 5 8.00
ST002 Blue pen Piece 100 25 0.30
ST003 Stapler Piece 12 4 3.50

These prices are illustrative, not market quotations. Use your local currency and actual supplier records when creating a real sheet.

Step 2: Record movements rather than overwriting quantities

In Transactions, create Date, Item code, Type, Quantity, Reference, Notes. For example, buying ten reams of paper becomes an IN movement of 10; selling three becomes an OUT movement of 3. Record customer returns and damaged stock using consistently defined movement types.

Why keep a transaction history? If a quantity looks wrong, you can investigate the entries rather than simply guessing what happened. For a busy shop, record transactions each day instead of relying on memory at month's end.

Step 3: Calculate current stock

Suppose Transactions column B contains item codes, column C contains IN or OUT, and column D contains quantities. In Products, where A2 contains the product code and D2 contains opening stock, this formula calculates stock on hand:

=D2 + SUMIFS(Transactions!$D:$D,Transactions!$B:$B,A2,Transactions!$C:$C,"IN") - SUMIFS(Transactions!$D:$D,Transactions!$B:$B,A2,Transactions!$C:$C,"OUT")

The idea is simple: Opening stock + received units − issued units. If you add other types such as DAMAGED, decide whether they count as stock leaving and add them to the formula or classify them as OUT with a reason.

Copy the formula down for each product, checking that item codes are unique and spelled consistently.

Step 4: Add a reorder warning

If current stock is in G2 and reorder level in E2, use:

=IF(G2<=E2,"REORDER","OK")

This label is useful but should not be treated as a purchase order. Consider supplier lead time, expected demand, cash available, and seasonal sales before buying new stock.

Step 5: Build a useful dashboard

A beginner dashboard can show total product lines, number of items below reorder level, and the value of stock at cost. For example, if current stock is in column G and unit cost is in F, a stock-value formula for a row is =G2*F2.

To count products marked REORDER in H2:H100, use =COUNTIF(H2:H100,"REORDER"). Keep dashboard numbers clearly labeled, including the currency used. Do not confuse stock value at cost with projected selling revenue.

Google's Sheets SUMIF documentation explains conditional totals; the same principle supports stock calculations and sales summaries.

Step 6: Protect the spreadsheet from accidental edits

Give staff access only when necessary and explain which columns they should edit. Keep formula columns separate from daily-entry columns. Use data validation for movement types and product codes so that IN, In, and Incoming do not become three incompatible categories.

For important records, maintain periodic backups or exports. If several people edit simultaneously, review the version history after major changes.

Step 7: Count real stock regularly

No spreadsheet can correct a transaction that was never entered. Carry out a physical count on a defined schedule, investigate differences, and enter an adjustment with a reason. Do not silently overwrite balances because this hides errors and possible losses.

For an item sold by the box but issued by the piece, decide on one base unit and define conversions clearly. An ambiguous unit is a common source of stock errors.

Add services without mixing them with products

A stationery business may offer photocopying, printing, binding, and laminating. Put services in a separate Services sheet or sales register. You may track quantities of copies or pages for billing, but those counts should not be confused with physical stock of paper and toner.

You can still record materials consumed by services. For example, using ten sheets for printing should reduce paper stock if your system records sheets as its base unit.

Frequent mistakes to avoid

Duplicate item codes cause transactions to combine incorrectly. Missing units make calculations meaningless. Unrecorded returns make stock counts look too low. Formula cells edited accidentally break summaries. Counting sale price as inventory cost inflates asset values. Sharing the sheet publicly can expose financial and supplier information.

Frequently asked questions

Is Google Sheets suitable for every shop?

It works well for straightforward operations with manageable transaction volume. A larger shop with barcode integrations, complex taxation, or many concurrent users may eventually need dedicated software.

What should I do when stock reaches zero?

Check transactions and confirm the physical count. If you allow sales below zero in the sheet, investigate immediately; negative stock commonly indicates missing or incorrect entries.

How often should I update the sheet?

As close to the transaction as practical. A daily reconciliation is better than waiting until the end of the week.

Conclusion

A reliable inventory system begins with clear product codes, consistent units, complete movements, and regular physical counts. Start small, test your formulas with a few sample transactions, and expand only when the basic records are trustworthy.

Related Digital Skills Hub guide: Excel Formulas for Beginners.

Reference: Google Sheets SUMIF help.