How to Create a Google Sheets Inventory Template

Instead of wrestling with complex inventory software, you can build a powerful, custom inventory management system with a tool you already know: Google Sheets. From tracking stock levels to automatically flagging items that need to be reordered, a well-built spreadsheet can save you time and prevent costly stockouts. This guide will walk you through creating a dynamic Google Sheets inventory template from scratch, step by step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Use Google Sheets for Inventory Management?

For small businesses, e-commerce stores, and startups, managing inventory in dedicated software can feel like overkill. It’s often expensive, complicated, and loaded with features you'll never use. Google Sheets offers a streamlined, flexible, and free alternative with significant benefits:

• It's free. There are no subscription fees or user limits. All you need is a Google account.
• It's collaborative. Your whole team can access and update the same inventory sheet in real-time, from any device. No more emailing outdated Excel files back and forth.
• It's cloud-based. Your data is automatically saved and accessible anywhere with an internet connection, eliminating the risk of losing your work if a computer crashes.
• It's endlessly customizable. You can tailor your template to the exact needs of your business, adding or removing features as you grow.

The Foundation: Key Columns for Your Inventory Template

Before you start building, you need to decide what information to track. A solid inventory sheet is built on a foundation of clear and consistent data points. While you can customize this, most effective inventory templates include the following columns:

• SKU (Stock Keeping Unit): A unique code for each product variant (e.g., TSHIRT-RED-L). This is the most important field, as it acts as a unique identifier for formulas.
• Item Name: A simple, descriptive name for the product (e.g., "Men's Classic Red T-Shirt").
• Category: Grouping items by category (e.g., "Apparel," "Accessories") makes sorting and analysis much easier.
• Description: Any additional details, like the material or specific variant information.
• Supplier: Who you order the product from. This helps streamline your purchasing process.
• Unit Cost: The cost to purchase one unit of the item from your supplier.
• Current Stock: The number of units you currently have on hand. We'll automate this number later!
• Reorder Level: The minimum stock quantity that should trigger a new purchase order. When "Current Stock" falls to this level, it's time to reorder.
• Total Inventory Value: A calculated field that shows the total value of your on-hand stock for that item (Unit Cost x Current Stock).

Building Your Google Sheets Template: A Step-by-Step Guide

With our columns defined, it’s time to build the template. We’re going to set up a smart system with multiple tabs to keep your main inventory list clean and automated.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Set Up Your Tabs (Sheets)

First, create a new Google Sheet. You’ll see a single tab at the bottom named "Sheet1". We need a few more to organize our data properly. Rename "Sheet1" and create two new tabs by clicking the "+" icon in the bottom-left corner.

You should have three tabs:

1. Inventory List: This will be your main dashboard, showing a real-time overview of all your products and stock levels.
2. Sales Log: Every time you make a sale, you'll record it here.
3. Purchase Log: Whenever you receive new stock, you'll add it here.

Separating sales and purchases from your main list is what enables the automation. Your "Inventory List" will pull data from the logs to calculate the current stock without you having to manually adjust numbers.

Step 2: Format the 'Inventory List' Tab

Navigate to your "Inventory List" tab and set up the headers for the columns we outlined earlier. Add them in the first row:

SKU | Item Name | Category | Current Stock | Reorder Level | Stock Status | Unit Cost | Total Inventory Value | Supplier

To make your sheet easier to navigate, freeze the header row. Click on View > Freeze > 1 row. Now, when you scroll down, your column titles will remain visible.

Step 3: Structure Your 'Sales Log' and 'Purchase Log' Tabs

These two tabs will have a simpler structure. They just need to capture the movement of inventory.

On the 'Sales Log' tab, create these headers:

Date | SKU | Quantity Sold | Order ID (Optional)

On the 'Purchase Log' tab, create these headers:

Date | SKU | Quantity Received | PO Number (Optional)

The "SKU" column is the critical link between these logs and your main inventory list. Being consistent with your SKUs is everything.

Automating Your Inventory Counts with Formulas

Now for the part that turns a simple spreadsheet into a dynamic system. We’ll use formulas to make your sheet do the hard work for you.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Calculating Total Stock In and Out with SUMIF

The single most powerful function for this template is SUMIF. It allows you to add up numbers in a range that meet a specific condition. We’ll use it to calculate the total units sold and purchased for each SKU.

Go back to your 'Inventory List' tab. The "Current Stock" column won't be a number you type in manually. Instead, it will be calculated with this formula: (Total Quantity Received) - (Total Quantity Sold).

Let’s say your first product's SKU is in cell A2. In the "Current Stock" cell for that product (D2), enter the following formula:

=SUMIF('Purchase Log'!B:B, A2, 'Purchase Log'!C:C) - SUMIF('Sales Log'!B:B, A2, 'Sales Log'!C:C)

Here’s what that formula does:

• SUMIF('Purchase Log'!B:B, A2, 'Purchase Log'!C:C): It looks at the entire SKU column (B:B) in your 'Purchase Log'. If it finds a match for the SKU in cell A2 of your 'Inventory List', it adds the corresponding number from the "Quantity Received" column (C:C).
• SUMIF('Sales Log'!B:B, A2, 'Sales Log'!C:C): It does the same thing for your 'Sales Log', adding up all quantities sold for that specific SKU.

The formula then subtracts total sales from total purchases to get your real-time stock level. Simply drag the little blue square in the corner of cell D2 down the rest of your column to apply the formula to all your products.

2. Calculating Total Inventory Value

This one is simple multiplication. In the "Total Inventory Value" column (cell H2), input:

=D2*G2

(Assuming "Current Stock" is in column D and "Unit Cost" is in column G). Drag this formula down the column as well.

3. Creating an Automated "Stock Status" Alert

Instead of manually checking which items are low in stock, let's have Google Sheets tell us. We can use an IF function to create a status alert.

In the "Stock Status" column (cell F2), enter this formula:

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

This formula checks if the "Current Stock" (D2) is less than or equal to the "Reorder Level" (E2). If it is, it displays the text "REORDER", otherwise, it shows "OK."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Bonus: Using Conditional Formatting for Visual Alerts

To make low-stock items really stand out, you can set up a conditional formatting rule.

1. Select the entire "Stock Status" column.
2. Go to Format > Conditional Formatting.
3. Under "Format rules," choose "Text is exactly" and type "REORDER".
4. In the "Formatting style" section, choose a red fill color.
5. Click "Done."

Now, any time a product's status changes to "REORDER," the cell will automatically turn red, making it impossible to miss.

Maintaining and Improving Your Template

Your template is now set up and automated. To make it work in the long run, consistent habits are key.

• Data Entry Discipline: Be diligent about logging every sale and purchase. The system only works if the data is accurate. Consider using a Google Form linked to your sheet for easy data entry from a mobile device.
• Consistent SKUs: Typos in your SKUs will break the formulas. Use the Data > Data validation feature to create dropdown menus from your master 'Inventory List' for the SKU columns in your logs. This ensures you can only select existing, correctly spelled SKUs.
• Regular Audits: Periodically perform a physical stock count (cycle counting) for a few items and compare it to your sheet's numbers. This helps you catch discrepancies caused by things like damage, theft, or data entry errors.

Final Thoughts

Creating an inventory management system in Google Sheets gives you a free, flexible, and powerful tool customized for your exact needs. By separating your data into a master list and logs for sales and purchases, and then linking them with formulas like SUMIF, you can automate your stock counts and gain a clear, real-time view of your business operations.

As your business grows, you might find that tracking inventory, sales, and marketing data across multiple spreadsheets and platforms becomes its own full-time job. This is where moving from manual management to a more automated analytics solution can help. We built Graphed to solve this very problem. You can connect your Google Sheets, Shopify store, marketing platforms, and other data sources, and then use natural language to instantly build live dashboards. It can provide a unified view of your inventory levels alongside sales data and CRM performance, turning hours of report-building into a 30-second task.