Small businesses and side-hustlers pay $20–40/month for inventory SaaS. If your catalog is under a few hundred SKUs, that's $300+/year for what is essentially two formulas and a conditional format.
I build Excel automation tools for a living, so I put together an inventory tracker that does the essentials — live stock levels, low-stock alerts, a full movement log, and a revenue dashboard — in a single .xlsx file. No account, no subscription, no cloud sync to break. Here's the design, and the actual formulas behind it.
The 4-sheet architecture
1. Inventory — one row per SKU: SKU | Name | Category | Unit Cost | Unit Price | Reorder Point | Current Stock | Status
Current Stock is never typed by hand. It is computed from the movement log:
=SUMIF('Stock Log'!A:A, A2, 'Stock Log'!D:D)
Column D in the log holds signed quantities (+50 for a purchase, -3 for a sale). Because stock is derived, the log becomes an audit trail — you can always answer "when did we lose 12 units of SKU-041?"
2. Stock Log — append-only: Date | SKU | Type | Qty | Note. A data-validation dropdown keeps Type to Purchase / Sale / Adjustment / Return, which prevents the typos that silently corrupt SUMIF totals.
3. Dashboard — the money view: total SKUs, units on hand, stock value at cost, potential revenue at price, and a count of items below reorder point:
=COUNTIF(Inventory!H:H, "LOW")
4. Settings — category list and threshold defaults, so non-technical users can reconfigure without touching formulas.
The low-stock alert trick
The Status column is dead simple but it's what makes people actually use the sheet:
=IF(G2<=F2, "LOW", "OK")
Then a conditional-formatting rule paints LOW rows red. Open the file, red rows = what to reorder today. Zero thinking required.
One gotcha: conditional formatting evaluates per-cell, so anchor your formula to the first row of the range ($G2) or the highlighting will drift one row off. This cost me an embarrassing demo once.
Why not Google Sheets?
Honestly, for some people Sheets is the right answer — if you need multi-user editing, use it. But a file-based tracker wins when:
- You're solo or one-person-ops (Etsy sellers, small warehouses, field service vans)
- You want it to work offline, forever, with no vendor
- You want to own the file and back it up by dragging it into a folder
Generating it with Python
If you want to build your own variant, openpyxl does the whole thing — sheets, formulas, conditional formatting, data validation:
from openpyxl import Workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.worksheet.datavalidation import DataValidation
wb = Workbook()
inv = wb.active
inv.title = "Inventory"
inv.append(["SKU","Name","Category","Unit Cost","Unit Price",
"Reorder Point","Current Stock","Status"])
# Stock derived from the log, status from reorder point
inv["G2"] = "=SUMIF('Stock Log'!A:A,A2,'Stock Log'!D:D)"
inv["H2"] = '=IF(G2<=F2,"LOW","OK")'
# Red highlight for low-stock rows
inv.conditional_formatting.add(
"A2:H200",
FormulaRule(formula=['$H2="LOW"'],
fill={"fill_type": "solid", "start_color": "FFC7CE"}))
# Dropdown for movement types
log = wb.create_sheet("Stock Log")
dv = DataValidation(type="list",
formula1='"Purchase,Sale,Adjustment,Return"')
log.add_data_validation(dv)
dv.add("C2:C1000")
wb.save("inventory_tracker.xlsx")
The shortcut
If you'd rather skip the build, I packaged the production version — 200-SKU inventory, 1000-row movement log, dashboard with revenue rollups, and the alert system pre-wired — as Inventory Tracker Pro ($19, one-time, instant download): https://qiliang.gumroad.com/l/hexog
It opened in Excel, LibreOffice, and WPS in my tests; formulas verified against a real dataset before shipping.
Either way: stop renting your inventory spreadsheet. Build it once, own it forever.
What's your threshold for ditching a SaaS subscription and going back to files? Curious where others draw the line.
