How to Create an Inventory Tracker in Google Sheets

Wrestling with inventory spreadsheets can feel like a never-ending task, especially when you're just trying to figure out what to order and when. The good news is you don't need expensive, complex software to get started. This guide will walk you step-by-step through creating a powerful, automated inventory tracker using a tool you already know and use: Google Sheets.

Why Google Sheets is a Great Choice for Your First Inventory Tracker

Before investing in an expensive, dedicated inventory management system, Google Sheets offers a perfect starting point. It's completely free, which is a huge boon for growing businesses. Since it's cloud-based, you and your team can access and update your inventory list from anywhere, at any time, on any device. Best of all, it's incredibly flexible. You can customize your tracker to fit your specific needs without being locked into the features or limitations of off-the-shelf software.

This tutorial will help you build a system with three core components:

A master list of all your products.
Separate logs for incoming stock (purchases) and outgoing stock (sales).
Automated calculations that update your current stock levels in real-time.

Before You Build: Planning Your Inventory System

A little planning now will save you hours of headaches later. The most important step is to decide exactly what information you need to track about your products. A well-organized structure is the foundation of a tracker that actually works for you, rather than against you.

What Data Do You Need to Track?

Open a blank Google Sheet and think about the essential details for each product you sell. Here's a solid list of headers to start with. Create these as the column headers in your first sheet.

Product Name: The common name for the item.
SKU (Stock Keeping Unit): A unique code you assign to each product variant (e.g., TSHIRT-BLUE-LG). This is the most important field for making automation work. Be consistent!
Description: A brief description for more detail. (e.g., '100% Cotton, Unisex-fit, Royal Blue')
Supplier: Where do you source this item from?
Cost per Item: What you pay for one unit of the product.
Sale Price: What you sell one unit for.
Starting Stock: The initial quantity you have on hand when setting up the sheet.
Reorder Level: The minimum stock quantity that should trigger a new purchase order. When stock is at or below this number, it's time to reorder.

Your spreadsheet should be treated as the single source of truth for your business's inventory operations.

Building Your Google Sheets Inventory Tracker: A Step-by-Step Guide

With your plan in place, it's time to start building. We'll create three separate tabs (sheets) within one Google Sheets file to keep everything organized: Products, Sales, and Purchases.

Step 1: Set Up Your Main 'Products' Sheet

This sheet is your master inventory list. It will contain all the details for every product you carry and, eventually, a live view of your stock levels.

Create a new Google spreadsheet.
Rename the first tab at the bottom from 'Sheet1' to Products.
In the first row, enter the headers you decided on during the planning phase.
We will automatically calculate your stock in a later step, so you don't need those to be filled out immediately. For now, just enter some of the more basic information such as SKU code, price, and item name.

Step 2: Tracking Your Incoming and Outgoing Inventory in Other Sheets

Your 'Products' sheet is only going to do the basic tracking. You need a way to track what's going in and what's going out of your inventory. To accomplish this, you can use separate sheets for tracking inventory movements — one for tracking incoming (i.e., Purchases) and another for outgoing (i.e., Sales).

On the 'Sales' sheet, as well as the 'Purchases' sheet, enter each product that has been purchased and sold. Each row should represent a unique transaction (so if you sell five products of the same item, they should all be entered as five different rows).
For each row, we will be adding in three different data points:
- Date: Date the item was purchased/sold.
- SKU: The specific identifier for the item.
- Number of Items Sold/Purchased: Quantity.

Automating Your Tracking with Formulas

This is where the real fun begins. You now have a sheet that can track all of your important product information and two other sheets which are able to take in new transaction data and get it ready to be compiled and processed.

To do that you must use the SUMIF() function to calculate the total from your incoming Purchases sheet as well as your Sales sheet to get your running total for each product. You need to do this for every single product that you track.

Select a product that you want to set up your automated tracking for and go to the column labeled 'Total Sold'.
Enter the SUMIF() formula in the corresponding cell for the row: =SUMIF(Sales!B:B, Products!A2, Sales!C:C)
Now your current stock inventory is a simple calculation from here:
[Starting Stock] - [Total Sold] + [Total Purchased]

Leveling Up Your Tracker: Adding Advanced Features

Once you have a functional tracker, you can add a few clever features to make it even more powerful and user-friendly. These additions help prevent common manual errors and give you quick, visual insights into your inventory health.

Create Automatic Low Stock Alerts

Instead of manually scanning your list for items that need reordering, you can have Google Sheets highlight them for you automatically using conditional formatting.

In your 'Products' sheet, click and drag to select all the cells in your 'Current Stock' column (e.g., cell G2 to the bottom of your data in column G).
Go to the menu and click Format > Conditional formatting.
Under "Format rules," choose the "Custom formula is" option from the dropdown.
In the value box, enter this formula (adjust the column letters to match your sheet): =G2<=H2
This formula checks if the value in the 'Current Stock' cell (G2) is less than or equal to the value in the 'Reorder Level' cell (H2). It will automatically apply this logic to every row you selected.
Under "Formatting style," choose a prominent background color like light red. Click "Done."

Now, anytime your current stock level for an item drops to or below its reorder level, the cell will instantly turn red, giving you a clear visual cue that it’s time to order more from your supplier.

Use Data Validation to Prevent Errors

The SUMIF formulas we created rely on an exact match of the SKU. A tiny typo when entering a sale (e.g., "TSHIRT-BLU-LG" instead of "TSHIRT-BLUE-LG") will cause the formula to fail, quietly making your inventory counts inaccurate. We can prevent this by using data validation to create a dropdown menu of your existing SKUs.

Click on your 'Sales' tab.
Select the entire column where you enter SKUs by clicking the column letter at the top (e.g., column B).
Go to the menu and click Data > Data validation.
Next to "Criteria," select "List from a range."
Click the small grid icon to select your SKU range.
Click "Save". Now, when you try to enter a transaction for sales or inventory purchases, a dropdown menu will appear.

Final Thoughts

Creating an inventory tracker in Google Sheets puts powerful, customized control over your stock right at your fingertips. By separating your master product data, sales, and purchases — and then using simple formulas to tie it all together — you have a system that is both easy to maintain and powerful.

While this method gives you control at little cost, in some cases, it’s not the most efficient or robust way to handle larger amounts of data. As your business grows, manually logging every sale and purchase can become time-consuming. When you connect data sources like Shopify, Google Analytics, or your CRM to Graphed, we automate the end-to-end reporting process. Instead of building formulas, you can simply ask questions in plain English like, "What were my top-selling products last month?" or "Show me a chart of inventory value vs. sales revenue." We instantly build a real-time dashboard for you, so you can stop wrestling with spreadsheets and spend more time acting on the insights that grow your business. You can sign up for free and connect your data in just a few clicks with Graphed.