If you are looking for a simple way to track your business inventory without paying for expensive software or monthly subscriptions, Google Sheets is a good place to start.

Imagine a small provision store that receives 50 cartons of noodles on Monday. By Friday, several cartons have been sold, new stock has arrived, and the owner can no longer confidently say how many cartons are left.

This is a common challenge for small businesses that still record sales in notebooks or rely on memory to keep track of their products.

The good news is that you do not need expensive inventory management software to solve this problem. With Google Sheets, you can record your products, monitor stock quantities, track what has been sold, and identify what needs restocking, all for free.

In this guide, we will show you exactly how to track inventory for your small business using Google Sheets, step by step.

Inventory tracking means keeping records of the products entering and leaving your business.

For example, if a skincare vendor starts with 30 bottles of lotion, sells eight and receives another 10, the remaining stock should be 32 bottles.

Tracking these movements helps businesses avoid selling unavailable products, ordering unnecessary stock, or losing track of what has been sold.

Visit ⁠Google Sheets and sign in with your Google Account.

Select a blank spreadsheet and rename it something like My Business Inventory Tracker.

In the first row, create these column headings:

These columns will help you monitor the products you have, what comes in, and what goes out.

Enter your products and their current quantities.

For example, a provision store might record:

Opening stock is the quantity available at the beginning of your tracking period.

Stock added represents new products received, while stock sold represents products customers purchased.

Use separate rows for different product sizes or variations, so your records remain accurate.

Instead of calculating the remaining quantity manually, Google Sheets can do it automatically.

Click cell E2 and enter:

=B2+C2-D2

Press Enter.

This formula adds the opening stock and new stock received, then subtracts the quantity sold.

For the noodles example:

50 + 20 - 12 = 58 cartons remaining.

Copy the formula down column E for your other products.

Whenever you update the stock added or stock sold, Google Sheets recalculates the remaining quantity.

Remember to account for damaged, expired, or missing products. You can add a separate column for these adjustments as your inventory system grows.

A reorder level tells you when to buy more products.

For example, if you do not want your noodle stock to fall below 15 cartons, enter 15 in column F.

Then click cell G2 and enter:

=IF(E2<=F2,"Restock","In Stock")

Google Sheets will display Restock whenever the remaining stock reaches or falls below your reorder level.

Otherwise, it displays In Stock.

You can also use ⁠conditional formatting to highlight products that need restocking.

This makes it easier to identify stock shortages before customers start requesting unavailable products.

Creating the spreadsheet is only the beginning.

For accurate records, update the quantities whenever you receive or sell products.

Also, make it a habit to:

For better recordkeeping, you can maintain a separate sheet showing individual stock movements, including dates and quantities.

Inventory tracking also works better when combined with proper sales records. Our guide on ⁠how to track your business’s monthly revenue explains how to monitor income and expenses to understand your business performance.

As your business grows, you may eventually need dedicated inventory management software to automate more of these activities.

Yes. Google Sheets allows small businesses to record products, calculate remaining stock, and monitor inventory using formulas.

Yes. Google Sheets is available free with a personal Google Account.

Google Sheets automatically recalculates formulas when quantities change. However, sales and stock movements generally need to be entered manually unless you connect an automated system.

Yes, particularly for businesses with relatively few products and straightforward inventory requirements. As operations become more complex, dedicated inventory management software may be more suitable.

Till next time,

Team Thrive

_____________________________________________________

A few lines to keep you motivated, going, and on top of the world