Low-stock alerts in a spreadsheet: conditional formatting on days of cover

25 days. That's the number your low-stock alert should actually watch — not units left. Suppose your supplier takes 18 days from PO to sellable stock and you review the sheet every Monday: any SKU with fewer than 25 days of cover is already late, even if the shelf holds hundreds of units. "48 left" means nothing until you know this SKU sells 6 a day.

This piece is the alert layer for your spreadsheet — the companion to the reorder sheet that decides how much to buy. Here it's only about when the sheet starts shouting: one metric, two thresholds, a flag column, and color rules in Excel or Google Sheets. Ground truth first, because it's why this layer is on you: as of November 2026, the core Shopify admin has no built-in low-stock email alert — what you get natively is saved low-stock views and Shopify Flow recipes (verified against Shopify's Help Center, October 2026). If you want the full native picture, the low-stock alerts on Shopify guide owns it; this article owns the spreadsheet.

The one metric: days of cover

Days of cover = units on hand ÷ average daily units sold. A phone stand with 96 on hand that sold 126 in the last 30 days has 96 ÷ (126/30) = 96 ÷ 4.2 = 22.9 days of cover. Units-left numbers can't be compared across SKUs; days of cover can, which is what makes a single alert rule possible across a whole catalog. The metric's quirks and readings are covered in the days-of-cover guide — for the alert layer you only need the formula and one honest caveat: if the SKU was out of stock for part of your measurement window, its true daily demand is understated, so the alert will fire late exactly for the products that most need to fire early. Refresh the export weekly and keep the window short (30 days).

Set the two thresholds from your own numbers

Both thresholds come from your operation, not from a benchmark someone published:

• Red — "ORDER NOW": days of cover < supplier lead time + your review gap. Lead time 18 days, you act on the sheet Mondays (up to 7 days until the next action) → red at < 25 days. Below this line, even an order placed today arrives after you go dark unless nothing goes wrong.
• Amber — "This week": red threshold + a padding of about 7 days → < 32 days. Order it on this week's buying pass and you never see red.

When your lead time changes or you move to a new supplier, re-derive the thresholds — they're arithmetic, not settings to set-and-forget.

The formulas (Google Sheets and Excel)

Layout, with settings at the top right so thresholds live in cells, not in formulas:

Then per row, with on hand in C and 30-day units sold in D:

• E2 — average daily sales: =D2/30
• F2 — days of cover: =IF(E2=0, 999, ROUND(C2/E2, 1))
• G2 — alert flag: =IF(F2<$I$1, "ORDER NOW", IF(F2<$I$2, "This week", "OK"))

The 999 cap matters more than it looks: SKUs with zero sales in the window (archived, seasonal, pre-launch) would otherwise divide by zero or, worse, hover at a technically infinite number of days. Capping them at 999 means they never alert, on purpose.

Conditional formatting, Google Sheets: select your data range (A2:G200) → Format → Conditional formatting → "Custom formula is" → =$G2="ORDER NOW" → red fill. Add a second rule with =$G2="This week" → amber fill.

Conditional formatting, Excel: Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format" → the same two formulas → Format → Fill. (A 3-color scale on column F — red at the low end — is a decent quick alternative, but the flag-based rules survive sorting and filtering better.)

A worked pass with real numbers

Four rows from a stand-and-pin store, thresholds 25/32:

Walnut stands: 96 ÷ 4.2 = 22.9 days — under the 25-day red line even though "96 units" sounds comfortable. Batch 04 is the dramatic one: 44 units is 6.3 days of cover; at 7 units a day this is dark by the weekend, which is exactly the kind of situation a units-left column buries. The discontinued pin stays quiet forever, as it should.

The Monday digest

The sheet won't email you by itself — Google Sheets' native notification rules email you when the file changes, not when a cell crosses a threshold, and Excel has no equivalent. Two honest options:

1. A filtered tab. On a second tab: =QUERY(A:G, "select A, B, F where G = 'ORDER NOW'", 1), amber-padded items on a second query. Your Monday routine becomes opening one tab — ten minutes, same slot as the buying pass.
2. Email from the platform side. If you want the email without opening anything, that's the native-tools territory: a saved low-stock view in the admin (see the saved views guide) puts the same list one click away inside Shopify, and Shopify Flow recipes can push inventory-triggered notifications to email or Slack.

Maintenance rules

• Same weekday, same export. The alert is only as fresh as the last export; a Friday sheet read on Thursday is lying to you.
• Exclude what shouldn't alert: archived products, preorders, and anything tracked as unavailable — query "available" quantity only.
• Watch the dark-day distortion. A SKU that sold nothing because it was out of stock shows a shrinking ADS and a growing cover number — the one systematic way this alert fails. A weekly glance at SKUs sitting at 0 on hand is the manual patch.
• Re-derive thresholds after lead-time changes. New supplier, new route, new season — recompute I1 and I2.

Common questions

Does Shopify have a built-in low-stock alert? No email alert in the core admin as of November 2026. What Shopify gives you natively is saved low-stock views on the Products page and Shopify Flow recipes that can send notifications on inventory triggers. An email alert on custom thresholds requires either Flow, a third-party app, or — as here — a spreadsheet you check on a schedule.

What formula should I use for a low-stock alert? Days of cover, not a fixed quantity: on hand ÷ average daily units sold, compared against thresholds derived from your lead time and review cadence. A fixed "alert under 50 units" rule fires constantly on fast movers and never on slow ones, which trains you to ignore it within a month.

What threshold should I set? Red at supplier lead time plus your review gap — 18-day lead time with Monday reviews means under 25 days of cover is already late. Amber about 7 days above that. Derive them from your own numbers rather than copying anyone's defaults, and recompute whenever a supplier or route changes.

Can Google Sheets email me when stock is low? Not on a threshold. Sheets' native notification rules fire when the file changes, not when a value crosses a line. The practical substitutes: a QUERY tab you check Mondays, or Shopify Flow recipes on the platform side, or a dedicated app if your catalog outgrows the manual check.

Why does my alert fire on products that never sell? Because zero sales in the window makes the days-of-cover calculation misbehave, or because archived and discontinued SKUs are still sitting in the export. Cap zero-velocity rows (the 999 trick), filter to active products, and exclude anything you've stopped replenishing from the conditional-formatting range.

Next step

Add the settings block (H1–I2) and the alert column to your existing reorder sheet this week — twenty minutes — then check the flag column every Monday before you build the buying list. When you want the platform-side layer too, compare what Shopify's saved views can and can't do natively, and borrow a Flow recipe for the email push this spreadsheet can't send.