How to track inventory for a small business, in short: give every item a SKU, list each SKU in a spreadsheet with its cost, quantity on hand and reorder point, and log every change (received, sold, returned, damaged) on a second tab so stock updates itself. Count physically on a schedule, fix the differences, and move to inventory software once you sell on several channels or the counts keep drifting.
More on this topic: Selling online, every guide in one place.
Inventory tracking sounds like a warehouse problem, but for a small shop it's what stops you selling something you don't have, running out of a best-seller in your busiest week, or tying up cash in stock that never moves. A spreadsheet works for longer than most people expect, if it's built around movements rather than numbers you overwrite. Below: the columns, formulas, reorder points, counting schedule and the signs it's time for software.
- One row per SKU, one SKU per variant. Every color and size is its own row.
- Never overwrite the stock number. Log each movement on a separate tab and let a formula total it.
- Reorder point = average daily sales × lead time in days + safety stock.
- Count regularly. A full count at least once a year, plus small rolling counts of your fastest sellers.
- Move to software when you sell on two or more channels, oversell, or spend hours a week updating the sheet.
What is the easiest way to track inventory for a small business?
A spreadsheet with two tabs: an item list and a movements log. The item list has one row per SKU with the fixed details (name, cost, supplier, reorder point). The movements log gets a new line every time stock comes in or goes out. A formula adds up the movements for each SKU, so the on-hand number is always calculated rather than typed. Google Sheets or Excel both work, and Google Sheets is easier if more than one person updates it.
What columns should an inventory spreadsheet have?
These are the columns for the item list. You can drop the optional ones while your catalog is small.
| Column | Example | Why it matters |
|---|---|---|
| A. SKU | CDL-SOY-LAV-8OZ | The unique key that links every tab and sales channel |
| B. Product name | Lavender soy candle | Readable name for people |
| C. Variant | 8 oz | Size, color or scent if not obvious from the name |
| D. Unit cost | $4.10 | What one unit costs you, for stock value and margins |
| E. Supplier | Your wax supplier | Who to reorder from (optional for makers) |
| F. Opening stock | 40 | Count on the day you start the sheet |
| G. Received | formula | Total units added from the movements tab |
| H. Sold | formula | Total units sold, all channels |
| I. Adjustments | formula | Returns restocked (+), damaged or lost (−) |
| J. On hand | formula | F + G − H + I: what should be on the shelf |
| K. Reorder point | 35 | The level that triggers a new order |
| L. Reorder flag | formula | Shows REORDER when J falls to K or below |
| M. Location (optional) | Shelf B3 | Where to find it when picking orders |
| N. Stock value | =D2*J2 | Cash tied up in this SKU |
The movements tab needs just five columns: Date, SKU, Type (Received, Sold, Adjustment), Quantity and Note (order number, supplier invoice, reason for an adjustment). Enter sold quantities as positive numbers and adjustments as plus or minus.
How do you set up an inventory spreadsheet step by step?
- Create SKUs for everything you stock. One per variant, in a consistent format. Our guide on how to create SKU numbers gives a format to copy, and the free SKU generator builds every color and size combination at once.
- Make two tabs, Items and Movements, with the columns above. Freeze the header row on both.
- Do an opening count. Count every SKU on one day, ideally before or after trading, and enter it as Opening stock.
- Add the formulas to row 2 of the Items tab and copy them down (shown below).
- Turn the Type column into a dropdown (Data > Data validation in both Google Sheets and Excel) so nobody types "recieved" and breaks the totals.
- Highlight low stock. Add conditional formatting that colors the row when the reorder flag shows REORDER.
- Log movements daily. Paste or type each day's orders and deliveries. Most store platforms can export orders as CSV, which makes the Sold lines quick to add.
The formulas for row 2 of the Items tab, assuming the movements tab has SKU in column B, Type in C and Quantity in D. They work the same in Google Sheets and Excel:
G2 Received =SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "Received")
H2 Sold =SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "Sold")
I2 Adjusted =SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "Adjustment")
J2 On hand =F2 + G2 - H2 + I2
L2 Reorder? =IF(J2 <= K2, "REORDER", "")Typed-over numbers hide mistakes. With a log, every unit has a date and a reason, and your sales history doubles as the data for reorder points.
Why does every item need a SKU?
Because the SKU is what ties one exact item together across your shelf, your spreadsheet, your store and each marketplace. Product names change and get abbreviated differently on each channel; a SKU doesn't. If a T-shirt comes in three colors and four sizes, that's 12 SKUs and 12 rows, because you can run out of the black medium while you have plenty of white large. Print SKUs on shelf and bin labels too, optionally as Code 128 barcodes from our free barcode generator, so counting and picking is a scan instead of a guess. Those barcodes are for your own use; they aren't GS1 product codes (see do I need a UPC barcode?).
How do you know when to reorder stock?
Set a reorder point for each SKU: average daily sales × supplier lead time in days, plus safety stock. When on-hand stock reaches that number, you order. For example, selling 3 candles a day with a 10-day lead time uses 30 while you wait; add 10 as a buffer and the reorder point is 40.
| SKU | Avg daily sales | Lead time (days) | Safety stock | Reorder point |
|---|---|---|---|---|
| CDL-SOY-LAV-8OZ | 3 | 10 | 10 | 40 |
| TS-CREW-BLK-M | 1.5 | 21 | 12 | 44 (rounded up) |
| MUG-CER-11OZ-WHT | 0.5 | 30 | 8 | 23 |
Take average daily sales from your movements tab (units sold in the last 30 or 90 days ÷ days), and count lead time from order to ready-to-sell. Our guide on how to calculate a reorder point covers safety stock and seasonal peaks in detail, and the free reorder point calculator does the arithmetic. Recheck reorder points every month or two; they go stale as sales change.
New SKUs need listing photos. Turn a quick phone shot into a clean white or lifestyle image in the same style as the rest of your catalog.
Make product photos7 days free · 50 credits · No credit cardHow often should a small business count inventory?
Do a full physical count at least once a year, and count your fastest-moving or most valuable items more often, weekly or monthly. Counting a few SKUs at a time on a rolling schedule is called cycle counting, and it catches errors while they're still small and recent enough to explain.
| Item group | Share of SKUs (typical) | Count how often |
|---|---|---|
| A: best-sellers and high-value items | Small share, most of the sales | Weekly or every two weeks |
| B: steady sellers | Middle group | Monthly |
| C: slow movers and cheap items | Largest share, few sales | Quarterly, plus the annual count |
When a count differs from the sheet, don't overwrite the number. Add an Adjustment line on the movements tab with the difference and a reason (damaged, miscount, missing), and count when nothing is being packed.
How do you track inventory across several sales channels?
Keep one stock number per SKU and make every channel draw from it. If you sell the same candle on your own site, Etsy and at markets, every sale comes off the same row and every channel's stock is updated soon after, or two buyers can purchase the last unit. Use the identical SKU everywhere so sales exports match your sheet. Updating several listings by hand after each sale is where spreadsheets start to struggle.
When should you switch from a spreadsheet to inventory software?
Switch when keeping the sheet accurate costs more time or money than software would. These are the usual signs:
- You sell on two or more channels and have oversold at least once.
- Updating the sheet takes more than an hour or two a week, or it's always a few days behind.
- Counts regularly don't match and you can't tell why.
- Several people edit it and changes get overwritten.
- You need more than stock levels: purchase orders, bundles or kits that use component stock, batch or expiry tracking, or several stock locations.
- You have hundreds of SKUs and filtering the sheet is slow.
Before you pay for anything, check what your store platform already includes: most ecommerce platforms track stock per variant and can stop selling at zero, which may be enough if you sell in one place. Either way, clean SKUs and accurate counts are exactly what you'll import.
Common inventory tracking mistakes
| Mistake | What happens | Fix |
|---|---|---|
| One SKU for all variants | You run out of a size without noticing | One SKU per variant |
| Typing over the stock figure | Errors can't be traced | Log movements, total with formulas |
| Forgetting returns and damages | Sheet shows stock you can't sell | Adjustment lines with reasons |
| Reorder points set once and forgotten | Stockouts in busy months | Review monthly and before peaks |
| Counting while orders are packed | Counts are wrong on day one | Count before or after trading |
| No unit cost column | No idea what stock is worth | Track cost; value = cost × on hand |
Once you know what each SKU costs, check that it actually makes money with the free profit margin calculator, and see how to calculate profit margin. Slow movers your sheet flags are worth a look in why are my products not selling.
Frequently asked questions
How do I keep track of inventory for a small business?
Give each item a SKU, list the SKUs in a spreadsheet with cost, stock on hand and a reorder point, and log every delivery, sale, return and write-off on a second tab so the stock figure updates by formula. Count physically on a schedule and record differences as adjustments.
What is the best free way to track inventory?
A Google Sheets or Excel spreadsheet with an item list and a movements log. It's free, flexible and enough for most single-channel shops with up to a few hundred SKUs.
What should an inventory spreadsheet include?
SKU, product name, variant, unit cost, supplier, opening stock, received, sold, adjustments, stock on hand, reorder point and a reorder flag. Location and stock value are useful extras.
How do I calculate stock on hand?
Opening stock plus units received, minus units sold, plus or minus adjustments for returns, damage and count differences.
How often should I do an inventory count?
At least one full count a year, plus rolling cycle counts: best-sellers weekly or every two weeks, steady sellers monthly, slow movers quarterly.
What is the difference between periodic and perpetual inventory?
A perpetual system updates stock with every sale and delivery, so you always know what's on hand. A periodic system only updates when you count. A spreadsheet with a movements log is a simple perpetual system.
When should I switch to inventory software?
When you sell on several channels and risk overselling, when updating the sheet takes hours each week, or when you need purchase orders, bundles, batches or multiple locations.
Give every SKU clean, consistent product photos, from white backgrounds to lifestyle scenes, without booking a shoot.
Start free trialSee pricing7 days free · 50 credits · No credit card. Trial images carry a small “Made with aiarter” mark; paid plans export clean files you can use commercially.
