Operations & Systems

Inventory Management with Google Sheets for small traders

This guide will show you how to set up a simple yet effective Google Sheets inventory system. It will help you track your unique products and keep your small business inventory organized.

EKD

30 May 2025 • 7 min read

Photo by Fatos Bytyqi / Unsplash

You might think inventory management sounds complicated or requires fancy software. But the good news is, you don't need expensive tools. This guide will show you how to set up a simple yet effective Google Sheets inventory system. It will help you track your unique products and keep your small business inventory organized.

Step 1: Set Up Your Basic Inventory Sheet

First, you need a clean sheet to list all your items. Think of it as your digital shelf.

Open Google Sheets:
- Go to sheets. new in your web browser. This creates a brand-new blank spreadsheet.
- Give your sheet a clear name, like "My Store Inventory" or "Artisan Stock Tracker." Click on "Untitled spreadsheet" at the top left to change its name.
Create Your Columns (Headings):
- These columns will help you organize your inventory management data. Type these titles into the first row (Row 1) of your sheet:
  - A1: Item Name: What is the product called? (e.g., "Handmade Ceramic Mug - Ocean Blue," "Vintage Leather Belt - Brown L")
  - B1: Item ID / SKU: A unique code for each item. This helps you find things fast, especially as you grow. You can make these up! (e.g., "CM-OB001," "VLB-005"). For unique, one-off thrift items, you might just use a simple sequential number or the date.
  - C1: Description: A short detail about the item. (e.g., "Large size, dishwasher safe," "Genuine leather, minor scuff marks")
  - D1: Category: What type of product is it? (e.g., "Pottery," "Jewelry," "Apparel," "Books") This helps you sort later.
  - E1: Cost Per Unit: How much did it cost you to make or buy this one item? (e.g., $8.50)
  - F1: Sale Price: How much do you plan to sell it for? (e.g., $25.00)
  - G1: Quantity In Stock: How many do you have right now? This is the most important column for tracking. (e.g., 12, 1)
  - H1: Date Added: When did you add this item to your inventory management system? (e.g., 2023-10-26)
  - I1: Supplier / Source: Where did you get the item? (e.g., "Local pottery supply," "Estate sale," "Bulk fabric vendor")
  - J1: Notes: Any other important details. (e.g., "Seasonal item," "Needs minor repair," "Good for holiday markets")
Freeze Header Row:
- To always see your column names as you scroll down, click on "View" in the top menu.
- Go to "Freeze" and select "1 row." Now, Row 1 will always stay at the top.

Step 2: Add Your Current Inventory Items

Now it's time to populate your sheet with the items you currently have. Be careful and consistent with your data entry.

Start Adding Data:
- Go to Row 2. Under each column heading, type in the details for one of your inventory items.
- Example for an Artisan:
  - A2: Handmade Ceramic Mug - Ocean Blue
  - B2: CM-OB001
  - C2: Large size, food safe glaze
  - D2: Pottery
  - E2: $8.50
  - F2: $25.00
  - G2: 12
  - H2: 2023-10-26
  - I2: My studio
  - J2: Best seller, always keep in stock
- Example for a Thrift Trader:
  - A3: Vintage Denim Jacket
  - B3: VDJ-2023-01
  - C3: Levi's, good condition, faded blue
  - D3: Apparel
  - E3: $15.00
  - F3: $45.00
  - G3: 1
  - H3: 2023-10-20
  - I3: Flea market
  - J3: Minor fraying on cuff
Continue for All Items: Repeat this process for every item you have in stock. It takes time upfront, but it's worth it! This detailed listing becomes your core spreadsheet inventory tool.

Step 3: Track Sales and New Stock

This is where your inventory management system truly becomes active. Every time an item sells or you get new materials or thrift finds, you need to update your sheet.

Updating When an Item Sells:
- Find the row for the item that just sold.
- Go to the "Quantity In Stock" column (G).
- Reduce the number by the quantity sold.
- Example: If you sold one "Handmade Ceramic Mug - Ocean Blue," change G2 from 12 to 11.
- If a thrift item sells and you only had one (Quantity 1), change the quantity to 0. You might also highlight the row or move it to a "Sold Items" tab if you want to keep a history of individual sales.
Updating When You Add New Stock:
- For New Items (different than what you currently stock): Add a completely new row following the steps in "Step 2."
- For Restocked Items (items you already list): Find the row for that item. Go to the "Quantity In Stock" column (G). Add the number of new units you've received.
- Example: You made 5 more "Handmade Ceramic Mug - Ocean Blue." If G2 was 11, change it to 16 (11 + 5).
Consistency is Key: Get into the habit of updating your sheet as soon as sales or additions happen. This ensures your Google Sheets inventory is always accurate. Regular updates help you track inventory precisely and avoid missing items.

Step 4: Set Up Low Stock Alerts (Using Conditional Formatting)

Knowing when you're running low on popular items is crucial. Google Sheets can automatically highlight items that need your attention.

Select Your Quantity Column:
- Click on the letter "G" at the very top of your sheet. This selects the entire "Quantity In Stock" column.
Open Conditional Formatting:
- Go to "Format" in the top menu.
- Click on "Conditional formatting." A sidebar will appear on the right.
Set the Rule:
- Under "Format rules," ensure "Apply to range" says G:G.
- Under "Format rules," click the "Format cells if..." dropdown.
- Choose "Less than or equal to."
- In the "Value or formula" box, type the number that signals "low stock" for you (e.g., 5 for artisan products, 1 for unique thrift items, or 2 if you want a buffer). For unique thrift items where quantity is usually 1, you might choose 0 to show what is sold, or just leave it for now. Let's use 5 as an example for artisan items or any item you sell more of.
- Under "Formatting style," click the paint bucket icon. Choose a bright color like Red or Orange. This will make it easy to spot items needing restocked.
Confirm: Click "Done." Now, any cell in column G that has a number of 5 or less (for our example) will turn red. This visual cue helps you manage your stock control efficiently. This simple alert system is a core part of an effective simple inventory system.

Step 5: Simple Analysis and Filtering Your Data

Your spreadsheet holds valuable information. Google Sheets can help you quickly find what you need.

Filter Your Data:
- Click anywhere in your header row (Row 1).
- Go to "Data" in the top menu.
- Click "Create a filter." Small funnel icons will appear in each header cell.
- Now, click on the funnel icon in the "Category" column (D1). You can uncheck "Select all" and choose only "Pottery" to see just your pottery items. Click "OK."
- To remove the filter, click the funnel icon again and choose "Select all," then "OK." This is great for managing different types of thrift store inventory or artisan creations.
Sort Your Data:
- You can also sort your whole inventory list.
- Click the funnel icon in the "Quantity In Stock" column (G1).
- Choose "Sort A-Z" to see items with low quantities first, or "Sort Z-A" to see your most stocked items first. This helps you quickly see what you have plenty of and what's running low. This is a practical way to manage stock.
Basic Summaries (Optional, but useful!):
- You can quickly see how much value you have in stock or total items.
- At the bottom of your sheet, below your last item, you could add:
  - A: Total Items
  - B: =SUM(G:G) (This formula adds up all numbers in column G, giving you the total number of items across all products.)
  - C: Total Stock Value
  - D: =SUM(ARRAYFORMULA(E:E * G:G)) (This formula multiplies your cost by quantity for each item, then adds them all up. This helps you understand the total value of your stock control at cost.)

Step 6: Regular Review and Backup

Your inventory management system is alive, so give it regular attention.

Schedule Regular Reviews:
- Set aside 15-30 minutes each week or every two weeks.
- Review your low-stock alerts.
- Check items that haven't sold in a while.
- Make sure all new purchases or creations have been added. This helps you track inventory more consistently.
Benefit from Google Sheets Autosave:
- One of the best things about Google Sheets is that it saves your changes automatically. You don't need to click a "Save" button!
- Your Google Sheets inventory is always safe in the cloud. You can access it from your computer, tablet, or phone, anywhere you have internet. This accessibility makes it an ideal simple inventory system for busy artisans and thrift traders.

Conclusion: Take Control of Your Inventory

Setting up effective inventory management with Google Sheets is a game-changer for artisans and thrift traders. It's a free, straightforward, and powerful way to bring order to your stock. You will:

Avoid Stockouts: Always know when to make more or buy more.
Save Money: Stop buying things you don't need or having dead stock.
Know Your Business: Understand what sells well and what doesn't.
Save Time: Find items quickly and track sales with ease.

Action builds business. Start small, start smart—then scale.

This content is AI-assisted and reviewed for accuracy, but errors may occur. Always consult a legal/financial professional before making business decisions. nrold.com is not liable for any actions taken based on this information.