Apparel Inventory Tracking Spreadsheet Guide (2026)

Here's how to build one that doesn't.

Why Apparel Inventory Is Harder Than Generic Inventory

Most inventory spreadsheet templates treat SKU as the atomic unit. Apparel doesn't work that way. You're tracking style → colorway → size as a 3-level hierarchy, and every aggregation question lives at a different level: How much of the Monaco Blazer do we have left? How much of it in navy? How much in navy, size 10?

That hierarchy has to be baked into your data structure from row 1, or you'll be writing increasingly tortured VLOOKUP chains by week 3.

As of May 2026, Google Sheets caps at 10 million cells per spreadsheet. A catalog of 8,000 SKUs tracked across 52 weeks with 15 columns of attributes will push 6.2 million cells. That's not theoretical — it's the arithmetic of a mid-size DTC brand's seasonal tracking sheet. You'll hit performance issues well before the cell cap, usually around 50,000 rows of transaction history.

The Core Sheet Structure

You need at minimum 3 tabs: Inventory (the source of truth), Movements (receipts and shipments), and Dashboard (what your ops director actually looks at). Optional but useful: Reorder Watch and Aging Stock.

Inventory tab — minimum columns:

The SKU column is your join key. Everything downstream — movements, POs, sales data — joins on SKU. If your ERP exports style codes in one format and your 3PL exports them in another, every formula breaks here. (It will. Handle it before it handles you — more on that below.)

Key Formulas for the Inventory Tab

Calculating on-hand from a movements log — if you're pulling movements from an ERP or WMS export rather than editing on-hand directly:

=SUMIF(Movements!$B:$B, A2, Movements!$E:$E)

Where column B in Movements is SKU and column E is quantity (positive for receipts, negative for shipments). This works at 5,000 movement rows. Above 40,000 rows, SUMIF across an open-ended range slows noticeably. Switch to:

=QUERY(Movements!A:F, "SELECT SUM(E) WHERE B = '"&A2&"' LABEL SUM(E) ''", 0)

QUERY handles 80,000-row movements exports from most 3PLs without choking. The tradeoff is that QUERY returns errors instead of zeros when there are no matches — wrap with IFERROR(..., 0).

Reorder alert flag:

=IF((C2+D2) <= E2, "REORDER", "")

On Hand + On Order vs. Reorder Point. Simple, but the +D2 matters — triggering a reorder when you already have 500 units on a PO is how you end up with 18 months of inventory in a colorway nobody wants.

Aging stock flag — units that haven't moved in 90+ days:

=IF(AND(C2>0, TODAY()-L2>=90), "AGING", "")

This requires your Last Updated column to be a real date, not a text string. It usually isn't, because something in your ERP exports dates as "05/07/2026" and your 3PL exports "2026-05-07" and they've been coexisting in column L since Q1. Fix with:

=IFERROR(DATEVALUE(TEXT(L2,"YYYY-MM-DD")), IFERROR(DATEVALUE(L2), "BAD DATE"))

That wrapper survives most mixed-format date columns — ISO 8601, US short date, and Excel serial numbers. It won't survive "07 May 2026" text strings without more surgery, but those are rarer in ERP exports.

The Style-Level Rollup Problem

Your ops director doesn't want to see 30 rows for the Monaco Blazer. They want 1 row: total units, total value, status. That rollup is where most apparel tracking sheets fall apart.

The cleanest solution is a separate Style Summary range using SUMIF against the Style column:

=SUMIF(Inventory!$B:$B, A2, Inventory!$F:$F)

Style in column B, On Hand in column F, style name in A2 of your summary. This gives you total on-hand across all colors and sizes for that style.

For colorway-level rollups, you need SUMIFS:

=SUMIFS(Inventory!$F:$F, Inventory!$B:$B, A2, Inventory!$C:$C, B2)

Style match on column B, color match on column C. Works fine at 10,000 SKU rows. Above that, SUMIFS on full-column references starts adding seconds to every recalc. Lock your ranges to actual data bounds (e.g., $F$2:$F$8001) and set calculation to manual if the sheet is used as a reference file rather than a live dashboard.

Dashboard Tab: What Directors Actually Ask

Three questions come up in every ops standup about apparel inventory:

1. What's our total on-hand value by category?
2. What's aging and needs marking down?
3. What needs to be reordered before next season?

Total on-hand value by category — assuming you have a Category column:

=QUERY(Inventory!A:K, "SELECT B, SUM(F*J) WHERE F > 0 GROUP BY B LABEL SUM(F*J) 'Total Value'", 1)

This groups by Style and multiplies On Hand (col F) by Cost (col J). Dumps directly into your dashboard tab as a live pivot. Formatting the output as a table with conditional formatting on the Total Value column gives you the visual scan your director wants in 10 seconds.

Aging inventory table — FILTER pulls the relevant rows:

=FILTER(Inventory!A:L, (Inventory!F:F>0)*(TODAY()-Inventory!L:L>=90))

At 8,000 SKU rows, this recalculates in under 2 seconds. At 50,000+ rows, use QUERY instead:

=QUERY(Inventory!A:L, "SELECT * WHERE F > 0 AND L <= date '"&TEXT(TODAY()-90,"YYYY-MM-DD")&"'", 1)

Reorder watch — same pattern, filter where (On Hand + On Order) <= Reorder Point and On Hand > 0 (no point reordering a discontinued SKU).

The SKU Mismatch Problem

Here's what actually kills apparel inventory sheets: your ERP exports SKU as BLZ-MON-NVY-10 and your 3PL exports it as BLZMON-NVY-10 (no hyphen after the style code, because someone configured it differently in 2019). Every SUMIF returns zero. Every join silently fails.

Before building any formula layer, audit your SKU formats with:

=LEN(A2)

Run that down your entire SKU column and sort by length. Outliers almost always flag a formatting inconsistency. Then use SUBSTITUTE to normalize:

=SUBSTITUTE(TRIM(UPPER(A2)), " ", "-")

UPPER handles case mismatches. TRIM kills leading/trailing spaces (invisible, always present in CSV imports). SUBSTITUTE normalizes space-delimited SKUs to hyphen-delimited. Do this in a helper column and join against the cleaned version, not the raw import.

Where a Spreadsheet Stops Being Enough

Spreadsheets handle apparel inventory well up to roughly 3 warehouses, 10,000 active SKUs, and weekly movement volumes under 50,000 rows. Past those thresholds, recalculation time and manual data refresh start creating real operational risk — you're looking at 30-second formula recalcs and inventory data that's 24-48 hours stale because nobody wants to refresh the sheet.

Multi-warehouse allocation math — distributing available units across fulfillment locations based on demand signals — is also where spreadsheet logic gets fragile. You can build it with nested IFs and ARRAYFORMULA, but a schema change in your WMS export will break it silently in ways you won't catch until a customer calls.

That said, for single-warehouse operations under 10,000 SKUs, a well-structured Google Sheet outperforms most inventory software implementations by weeks of setup time and thousands in licensing costs.

Keeping It from Breaking on Monday

The 2 things that kill apparel tracking sheets are schema drift (your ERP adds a column and shifts everything right) and date format rot (someone exports from a new system). Both are fixable with named ranges instead of column letter references, and with IFERROR wrappers on every date calculation.

For the named ranges: select your SKU column, use Data → Named ranges, call it inv_sku. Every formula that references Inventory!$A:$A becomes inv_sku. When the schema shifts, you update the named range definition in one place.

If you're running ModelMonkey in your Google Sheet, you can ask it to audit your formula references after any schema change — it'll flag broken ranges and suggest updated references without you having to trace each formula manually. That's the part that takes 45 minutes on a Monday morning and takes about 2 minutes with an AI assistant in the sidebar. Try ModelMonkey free for 14 days — it works in both Google Sheets and Excel.