Inventory Management in Excel: Full Guide + Free Spreadsheet

Excel is where most small ecommerce teams start tracking inventory. It is free (or already in Microsoft 365), everyone knows how to use it, and it handles basic stock counts fine. Microsoft publishes free inventory templates you can grab straight from the app.

This guide walks through the spreadsheet structure, formulas, and daily habits that keep an Excel inventory system accurate, plus the hard limits where it falls apart. Research by Ray Panko at the University of Hawaii found that about 88% of spreadsheets contain formula errors. Inventory sheets with hundreds of manual entries are especially vulnerable.

Get Excel Spreadsheet

When Excel inventory tracking works

43% of small businesses still track inventory manually or not at all

Excel works as an inventory tool when you stay inside these guardrails:

• Under 200 active SKUs: past this, scroll-and-search slows every lookup to 15-30 seconds
• 1-2 stock editors: shared spreadsheets break the moment two people save at once
• Single location: multi-location tracking needs relational logic Excel does not have
• Under 50 orders/day: manual logging above this volume means your data is always behind by 2-4 hours

A 2024 survey by Wasp Barcode Technologies found that 43% of small businesses still track inventory manually or not at all. If that is you, Excel is a big upgrade over paper or memory.

Threshold	Excel works	Time to switch
Active SKUs	under 200	200+
Locations	1	2+
Daily orders	under 50	50+
Stock editors	1-2	3+

Once you cross those lines, manual entry errors pile up fast. You have no audit trail, no concurrent editing, and no real-time sync with your sales channels.

Spreadsheet structure: 2 sheets

One workbook, two sheets. The Product Master holds your static product data. The Stock Movement Log records every in or out.

Product Master: 12 columns

Column	Type	Purpose
SKU	Manual	Unique code per variant (e.g. BLK-TEE-M)
Product Name	Manual	Human-readable label
On-Hand Qty	Formula	SUMIFS from movement log: never edit directly
Reorder Point	Manual	Minimum qty before reordering
Safety Stock	Manual	Buffer for demand spikes / supplier delays
Supplier	Manual	Primary vendor name
Lead Time (days)	Manual	Average supplier delivery time
Unit Cost	Manual	Cost per unit ($#,##0.00 format)
Last Count Date	Manual	Date of most recent physical count
Reorder Flag	Formula	”REORDER” when on-hand is at or below reorder point
Inventory Value	Formula	On-Hand Qty x Unit Cost
Days of Stock	Formula	On-hand / (avg daily sold qty over 30 days)

Stock Movement Log: 5 columns

Column	Type	Purpose
Date	Manual	Transaction date (yyyy-mm-dd)
SKU	Dropdown	Validated against Product Master list
Type	Dropdown	Received, Sold, Adjusted, Returned
Qty	Manual	Positive = stock in, negative = stock out
Reference	Manual	PO number, order number, or adjustment reason

Use data validation on the SKU column to restrict entries to Product Master values. This single step prevents typos from creating phantom SKUs, a problem that causes 15-20% of spreadsheet inventory discrepancies.

4 core formulas

What Excel formulas do you need for inventory tracking?

On-hand quantity pulls a live total from all logged transactions using SUMIFS:

=SUMIFS('Stock Movement Log'!D:D, 'Stock Movement Log'!B:B, [@SKU])

Reorder flag triggers an alert when stock hits minimum:

=IF([@[On-Hand Qty]] <= [@[Reorder Point]], "REORDER", "OK")

Inventory value calculates total cost of on-hand stock:

=[@[On-Hand Qty]] * [@[Unit Cost]]

Days of stock remaining estimates sell-through rate:

=IFERROR([@[On-Hand Qty]] / (ABS(SUMIFS('Stock Movement Log'!D:D, 'Stock Movement Log'!B:B, [@SKU], 'Stock Movement Log'!C:C, "Sold")) / 30), "N/A")

These 4 formulas cover 90% of what a small ecommerce operation needs for daily inventory decisions. For setting reorder points with more precision, the reorder point formula guide walks through the math including lead time demand and safety stock buffers.

Reorder alerts with conditional formatting

Apply conditional formatting to the Reorder Flag column: if the cell value equals “REORDER”, set fill to red with white bold text. That way you can spot items that need reordering at a glance without scanning every row.

Run a weekly review cycle:

1. Filter Product Master by reorder status
2. Compare flagged SKUs against open purchase orders
3. Update Last Count Date for any SKUs physically verified
4. Log adjustments in the Stock Movement Log (never overwrite On-Hand Qty directly)

This weekly count is the single most important habit for keeping a spreadsheet inventory accurate. Skip it, and your numbers will drift from reality within 2-3 weeks. A study by DeHoratius and Raman published in Management Science found that 65% of inventory records at a major retailer were inaccurate when physical counts were infrequent.

For operations with safety stock buffers, build those into the reorder point so the formula triggers before you hit true zero.

4 common mistakes that break Excel inventory

65% of inventory records are inaccurate when physical counts are infrequent

• No movement log. Overwriting On-Hand Qty directly kills traceability. When numbers are wrong, you have no way to figure out where they went sideways.
• Duplicate SKUs. “BLK-TEE-M” and “BLK-TEE - M” become 2 phantom products. Data validation dropdowns prevent this. Even 1 duplicate SKU can cause $500-$2,000 in misallocated stock per quarter.
• Stale data. If the spreadsheet is not updated the same day stock moves, every decision downstream runs on wrong numbers. Late updates are the number one source of stockouts in spreadsheet-managed warehouses.
• No ownership. When everyone can edit and nobody owns the data, errors pile up. Assign 1 person as inventory owner who reviews the sheet daily.

Sample workflow: daily and weekly tasks

Task	Frequency	Time estimate	Owner
Log all received stock in movement log	Daily	10-15 min	Receiving staff
Log all shipped orders in movement log	Daily	15-30 min	Shipping staff
Log returns and adjustments	Daily	5-10 min	Inventory owner
Review reorder flags and create POs	Weekly	20-30 min	Inventory owner
Physical spot-check 10-15 SKUs	Weekly	15-20 min	Inventory owner
Full reconciliation of A-class SKUs	Monthly	1-2 hours	Inventory owner

Total daily time: 30-55 minutes. Total weekly time: 35-50 minutes on top of daily tasks. Once daily logging exceeds 60 minutes, your order volume has probably outgrown manual tracking. Time to look at small business inventory management tools.

When to switch to dedicated software

Excel’s limits are structural. No formula trick fixes them:

• No concurrent editing: 2 people saving at the same time overwrites data
• No audit trail: no way to see who changed what and when
• No real-time channel sync: sales channel orders do not auto-deduct stock
• No barcode scanning: every entry is typed by hand, introducing 1-3% error rates per entry session
• No multi-location support: tracking 2+ warehouses means separate workbooks or complex macros

For teams building a full operating model, the ecommerce inventory management guide covers the systems and processes needed beyond spreadsheets. Once these limits start showing up in daily operations, most teams need small business inventory software rather than a more complicated workbook. For Shopify-specific setup, see Shopify inventory management.

Quick Reference

• Excel inventory needs 2 sheets: Product Master (12 columns) and Stock Movement Log (5 columns).
• Use 4 core formulas: SUMIFS for on-hand, IF for reorder flag, multiplication for value, division for days-of-stock.
• Excel works for operations under 200 SKUs, 1 location, 1-2 editors, and 50 orders/day.
• Run cycle counts at least 1x per week to prevent data drift.
• 88% of spreadsheets contain formula errors (Panko research).
• 43% of small businesses still track inventory manually or not at all (Wasp Barcode, 2024).
• Daily inventory logging takes 30-55 minutes for a typical small ecommerce operation.

Threshold	Excel works	Time to switch
Active SKUs	under 200	200+
Locations	1	2+
Daily orders	under 50	50+
Stock editors	1-2	3+
Concurrent editing	not supported	needed
Channel sync	manual	real-time needed