Excel

How to Build an Inventory Tracker in Excel That Stays Maintainable

Q: How do I add a reorder alert?

Add a calculated column in the Dashboard table: =IF([@Current Stock]<=[@Reorder Level], "REORDER", "OK"). Then apply conditional formatting (Home → Conditional Formatting → Highlight Cells → Text that contains → REORDER) to flag low stock in red. A formula version that also shows days-until-stockout is =[@Current Stock]/[@Avg Daily Sales].

Q: Should I use VLOOKUP or SUMIFS for the stock calculation?

SUMIFS, always. VLOOKUP only returns a single matching row — it cannot aggregate. SUMIFS sums every matching movement, which is what a stock balance is. XLOOKUP is fine for joining Item details from the Items table into the Dashboard (=XLOOKUP([@SKU], Items[SKU], Items[Name])), but never for the stock total.

Q: When does Excel stop being the right inventory tool?

Move off Excel when you need multi-user concurrent editing (Excel locks the file), real-time barcode scanning, automatic supplier ordering, or audit trails that regulators accept. Typical thresholds: above 2,000 active SKUs, above 500 daily movements, or any need for API integrations with shipping/POS. Options: Zoho Inventory, Katana, TradeGecko, or QuickBooks Commerce.

By Sagnik Bhattacharya 22 Feb 2026 4 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for building an inventory tracker in Excel, with stock and movement visuals.

Most inventory spreadsheets start as a single sheet with one editable "Stock" column that gets manually overwritten every time something moves in or out. Three weeks in, the totals are wrong, nobody remembers which adjustment came from which order, and the only audit trail is the office manager's memory. This tutorial shows the alternative.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

The pattern is simple and well-understood outside Excel — it is how every database-backed inventory system works. Keep an Items table (what you sell), a Movements table (every IN and OUT that ever happened), and a Dashboard that derives current stock from those two. You never manually edit a stock level. Excel calculates it from the full transaction history, so every number is traceable.

This tutorial works on Excel 365, 2021, 2019, and Excel on the web (SUMIFS and conditional formatting are available everywhere; XLOOKUP requires 365/2021). For the broader Excel learning path see the Excel Formulas Guide hub.

Step 1 — Set up the three-sheet architecture

Every sheet is an Excel Table (Ctrl + T) so formulas auto-extend when you add rows. The Table names matter — formulas reference them by name, not cell range.

Sheet	Table name	Purpose
Items	tblItems	Item master — one row per SKU, rarely changes
Movements	tblMovements	Transaction log — append-only, never edit
Dashboard	tblDashboard	Derived view — stock, reorder status, value

Rule. If the Movements table is append-only, your full stock history is recoverable at any point in time. If you let people edit old rows, you lose that property and the tracker becomes unverifiable.

Step 2 — Build the Items master sheet

One row per SKU. This sheet changes when you add a new product, update a reorder level, or change a unit cost. Columns:

Column	Example	Why
SKU	APL-RED-500	The unique key that joins every other sheet. Never reuse.
Name	Red Apples 500g	Human-readable — looked up from SKU, never typed into Movements
Category	Produce	For grouping and filters
Unit	bag	Unit of measure; prevents the "is 30 a crate or a bag?" bug
Unit Cost	1.20	Drives the stock value calculation
Reorder Level	50	Threshold below which the Dashboard flags REORDER
Supplier	Greenfield Co.	Who to call when REORDER triggers

Pitfall. Naming items "Red Apples" in one row and "Red apples" in another — different to SUMIFS, identical to humans. Enforce the SKU as the single source of truth and look up Name from there. Use Data Validation to drive SKU entry from a dropdown on Movements.

Step 3 — Build the Movements transaction log

One row per event. Append-only. Columns:

Column	Example	Rule
Date	2026-04-15	Real date cell, not text
SKU	APL-RED-500	Dropdown sourced from tblItems[SKU]
Type	IN	IN / OUT / ADJUSTMENT — dropdown
Qty	60	Signed convention OR always positive (paired with Type column)
Reference	PO-2026-041	PO number / customer order / adjustment reason
Note	Monday delivery	Optional free text for context

Two quantity conventions — pick one, not both. Option A: all quantities positive, Type column distinguishes IN vs OUT. Option B: IN is positive, OUT is negative, Type column is optional. Option A is easier for humans to enter; Option B is easier for formulas. Choose one and document it in a cell above the table.

Real-world scenario. A 22-SKU bakery supplier tracked 480 movements a month. Before this pattern, staff typed item names into a stock sheet and corrected the total by hand after every delivery. After: they append one row per delivery or sale, with a SKU dropdown. Time per entry dropped from 45 seconds to 8, and stock reconciliation at month-end went from a 90-minute fight to a 10-minute check.

Step 4 — Build the Dashboard with derived formulas

The Dashboard has one row per SKU, copied from the Items master, then six formula columns. Nothing on this sheet is hand-edited. Add a new SKU in Items and append its row here; everything else is derived.

Current Stock

Using Option A (positive quantities + Type column):

=SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU], tblMovements[Type], "IN")
 - SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU], tblMovements[Type], "OUT")
 + SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU], tblMovements[Type], "ADJUSTMENT")

Using Option B (signed quantities):

=SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU])

Option B is one line; Option A is three but more readable when staff enter data. Either way, the stock total is traceable to the transaction history.

Unit Cost and Name lookup

=XLOOKUP([@SKU], tblItems[SKU], tblItems[Name])
=XLOOKUP([@SKU], tblItems[SKU], tblItems[Unit Cost])
=XLOOKUP([@SKU], tblItems[SKU], tblItems[Reorder Level])

For Excel 2019 use INDEX/MATCH: =INDEX(tblItems[Name], MATCH([@SKU], tblItems[SKU], 0)).

Stock Value

=[@Current Stock] * [@Unit Cost]

Reorder Status

=IF([@Current Stock]<=[@Reorder Level], "REORDER", "OK")

Then Home → Conditional Formatting → Highlight Cells Rules → Text that Contains → "REORDER" → red fill. The whole row can be highlighted using a formula rule: =$G2="REORDER" applied to =$A$2:$H$500.

Average Daily Sales (trailing 30 days)

=SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU],
        tblMovements[Type], "OUT",
        tblMovements[Date], ">="&TODAY()-30) / 30

Days of Stock

=IF([@Avg Daily Sales]=0, "∞", ROUND([@Current Stock]/[@Avg Daily Sales], 0))

This is the formula most Excel inventory tutorials skip. It tells you how many days you can keep selling before you run out. Combine with Reorder Level to order before the shelf goes empty, not after.

Worked example — 25-SKU produce supplier

A small-business supplier tracks 25 SKUs across two product lines (Produce, Dry Goods) with ~40 movements per day.

Items sample (4 rows of 25):

SKU            Name            Category  Unit  Unit Cost  Reorder  Supplier
APL-RED-500    Red Apples 500g Produce   bag   1.20       50       Greenfield Co.
APL-GRN-500    Green Apples 500g Produce bag   1.15       40       Greenfield Co.
RICE-WHT-1KG   White Rice 1kg  Dry Goods bag   2.50       30       Oakmill Ltd.
PAST-PEN-500   Penne 500g      Dry Goods box   1.10       40       Oakmill Ltd.

Movements sample (a typical day):

Date         SKU            Type  Qty  Reference      Note
2026-04-15   APL-RED-500    IN    60   PO-2026-041    Monday delivery
2026-04-15   APL-GRN-500    IN    40   PO-2026-041
2026-04-15   APL-RED-500    OUT   18   INV-3201       Customer: Deli no. 4
2026-04-15   RICE-WHT-1KG   OUT   6    INV-3202
2026-04-16   APL-RED-500    OUT   22   INV-3211
2026-04-16   PAST-PEN-500   ADJUSTMENT 2  STK-0416   Damaged stock write-off

Dashboard result for APL-RED-500 after those 6 rows (assuming opening stock from a previous IN of 50): 50 + 60 - 18 - 22 = 70 units in stock. Value: 70 × £1.20 = £84. Reorder Level 50 → status OK. With average daily sales of 20, days-of-stock = 70/20 = 3.5 days.

The full formula reference

Where	Formula	What it does
Dashboard Current Stock (Option A)	`=SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU], tblMovements[Type], "IN") - SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU], tblMovements[Type], "OUT") + SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU], tblMovements[Type], "ADJUSTMENT")`	Stock from positive quantities split by Type column
Dashboard Current Stock (Option B)	`=SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU])`	Stock from signed quantities (IN positive, OUT negative)
Dashboard Name lookup	`=XLOOKUP([@SKU], tblItems[SKU], tblItems[Name])`	Pulls human-readable name from the Items master
Dashboard Unit Cost lookup	`=XLOOKUP([@SKU], tblItems[SKU], tblItems[Unit Cost])`	Cost per unit for valuation
Dashboard Reorder Level lookup	`=XLOOKUP([@SKU], tblItems[SKU], tblItems[Reorder Level])`	Threshold for the REORDER flag
Dashboard Stock Value	`=[@Current Stock] * [@Unit Cost]`	Closing inventory value per SKU
Dashboard Reorder Status	`=IF([@Current Stock]<=[@Reorder Level], "REORDER", "OK")`	Triggers the highlight rule
Dashboard Avg Daily Sales (30d)	`=SUMIFS(tblMovements[Qty], tblMovements[SKU], [@SKU], tblMovements[Type], "OUT", tblMovements[Date], ">="&TODAY()-30) / 30`	Trailing 30-day demand
Dashboard Days of Stock	`=IF([@Avg Daily Sales]=0, "∞", ROUND([@Current Stock]/[@Avg Daily Sales], 0))`	How long stock lasts at current sell-through
Conditional format (whole row)	`=$G2="REORDER"`	Applied to A:H, highlights REORDER rows red
Excel 2019 fallback (Name lookup)	`=INDEX(tblItems[Name], MATCH([@SKU], tblItems[SKU], 0))`	Pre-XLOOKUP equivalent, kept for older Excel

Variations: customise for your business

The skeleton (Items + Movements + Dashboard) stays identical. What changes is which extra columns you keep on the Items master and which extra metric you add to the Dashboard.

Use case	Extra Items columns	Extra Dashboard metric
Retail / FMCG	Selling Price, Margin %	GMROI = (Stock × Margin) ÷ Avg Stock
Restaurant / café	Yield per unit, Shelf life (days)	Wastage % from ADJUSTMENT vs OUT
Manufacturing (raw materials)	BOM SKU, Lead time (days)	Cover days vs lead time gap
E-commerce	Channel SKUs (Amazon, Etsy, Shopify)	Allocated stock per channel
Pharmacy / regulated	Batch number, Expiry date	Stock expiring within 30 days
Workshop tools / fixed assets	Asset tag, Last service date	Days since last calibration

For pharmacy or food, expiry tracking changes Movements too — add a Batch and Expiry column on each IN row, and the Dashboard FIFO-deducts from the oldest batch first. That is a meaningful step up; consider structured-reference tables before adding it.

Step 5 — Scale to multiple locations (optional)

When the business has more than one warehouse or shop, do not duplicate the tracker per location. Add a Location column to both Items and Movements, and add Location to every SUMIFS criteria set. Your dashboard now has one row per (SKU, Location) or one per SKU with columns per location.

If the source data lives in separate files (each shop emails a daily movements CSV), use Power Query's From Folder import to consolidate them into one Movements table automatically on refresh. The three-sheet architecture does not change — only the data source does.

When Excel stops being the right tool

Above ~2,000 active SKUs. Dropdown data validation and SUMIFS recalc time both become friction.
Above ~500 daily movements. Appending rows manually is too slow; you need scanning or API ingestion.
Multiple concurrent editors. Excel locks the file. Shared-workbook mode has bugs. Use SharePoint/OneDrive co-authoring for 2–3 users max; beyond that, move to a database-backed tool.
Regulated industries. If an audit will be rejected without a tamper-evident trail, Excel's "show me the deleted row" story is not strong enough.
Barcode scanning in real time. Excel does not own the hardware event loop.

Graduation path: Zoho Inventory, Katana, QuickBooks Commerce, or — if inventory feeds into accounting — Xero/QuickBooks with an inventory module. Keep the Excel tracker as the validation tool for the first month of migration.

Troubleshooting

Current Stock shows 0 for a SKU that definitely has movements. SUMIFS is case-insensitive but whitespace-sensitive. Trailing space in the Dashboard SKU or the Movements SKU breaks the match silently. Fix by wrapping both sides in TRIM — or better, enforce SKU entry through Data Validation dropdowns sourced from tblItems[SKU] so free-typed spaces cannot get in.
Current Stock is wildly negative. Option A was used but every row was entered as Type = OUT. Filter the Movements Type column for "OUT" and check whether incoming deliveries were tagged wrongly. Do not fix by editing the total — add a new ADJUSTMENT row with a Reference cell explaining the recount, so the audit trail stays intact.
New movements do not appear in Dashboard totals. The Movements sheet is not an Excel Table. A plain range stops at the original row count; SUMIFS cannot see new rows. Convert it to a Table (Ctrl + T) and reference by Table name (tblMovements[Qty]) instead of cell range (A2:A500).
REORDER flag is stuck on items that are clearly overstocked. The Reorder Level column is text ("50" as a string), not a number. Type mismatches in IF comparisons silently produce wrong results. Select the column → Data → Text to Columns → Finish with General format to re-cast as numbers.
Days of Stock shows #DIV/0! even though Avg Daily Sales has a value. The IF check is comparing a formula result to 0, but trailing 30-day sales is technically zero when no sales occurred in that window. The ∞ fallback in the Days of Stock formula handles this — if you see #DIV/0!, check that the IF wrap is in place.

Common mistakes

Editing the Current Stock cell manually instead of adding a Movement.
Typing SKUs instead of picking from a Data Validation dropdown.
Using a single stock number with no movement history ("save space").
Deleting old Movements rows for aesthetic reasons — destroys the audit trail.
Over-engineering the Dashboard with PivotTable dashboards before the base formulas are stable.

Frequently asked questions

What is the simplest Excel setup for an inventory tracker?

Three Excel Tables on three sheets: Items (item master with SKU, name, unit cost, reorder level), Movements (transaction log with date, SKU, IN/OUT type, quantity), and Dashboard (formula-driven view showing current stock, reorder status, stock value, days of stock). Keep all three as proper Excel Tables (Ctrl + T) so formulas auto-extend when you add rows.

What formula calculates current stock in Excel?

Use SUMIFS against a signed Movements table. If receipts are recorded as positive quantities and sales/adjustments as negative, current stock is =SUMIFS(Movements[Qty], Movements[SKU], [@SKU]). If you store IN and OUT as separate types, subtract: =SUMIFS(...,"IN") - SUMIFS(...,"OUT").

How do I add a reorder alert?

Add a calculated column in the Dashboard: =IF([@Current Stock]<=[@Reorder Level], "REORDER", "OK"). Then apply conditional formatting (Home → Conditional Formatting → Highlight Cells → Text that contains → REORDER) to flag low stock in red. A days-until-stockout version is =[@Current Stock]/[@Avg Daily Sales].

Should I use VLOOKUP or SUMIFS for the stock calculation?

SUMIFS, always. VLOOKUP only returns a single matching row — it cannot aggregate. SUMIFS sums every matching movement, which is what a stock balance is. XLOOKUP is fine for joining Item details from the Items table into the Dashboard (=XLOOKUP([@SKU], Items[SKU], Items[Name])), but never for the stock total.

When does Excel stop being the right inventory tool?

Move off Excel when you need multi-user concurrent editing (Excel locks the file), real-time barcode scanning, automatic supplier ordering, or audit trails that regulators accept. Typical thresholds: above 2,000 active SKUs, above 500 daily movements, or any need for API integrations. Options: Zoho Inventory, Katana, QuickBooks Commerce.