Low-Stock Alert Formula in Excel

Q: How do I handle items with zero reorder level (i.e., I order on demand)?

Set the Reorder Level to -1 for on-demand items. Since closing stock is always ≥ 0, the condition `Closing Stock <= -1` is never true and the alert never fires. Or use a separate column to mark on-demand items and exclude them from the COUNTIF. ---

Last updated: 27 June 2026

A low-stock alert in Excel is a single IF formula — =IF(Closing_Stock <= Reorder_Level, "REORDER NOW", "OK") — paired with conditional formatting to colour the row red. You get an instant visual dashboard that highlights every item below its reorder threshold without opening any separate report. This guide shows the complete setup: the status formula, the conditional formatting rule, a dynamic count of items needing reorder, and an optional email trigger using Outlook.

Key takeaways

The core formula is =IF([@[Closing Stock]] <= [@[Reorder Level]], "REORDER NOW", "OK") — one column added to your stock summary.
Conditional formatting highlights the entire row red when stock is at or below the reorder level — visible at a glance.
A COUNTIF counter at the top of the sheet shows the total number of items currently below reorder level.
For a more granular alert, add a second tier: "CRITICAL" when stock is zero, "REORDER" when stock is at or below reorder level.
Link the reorder level to your actual ROP formula (lead time × daily usage + safety stock) rather than a static number.
Excel's built-in macro recorder can trigger an Outlook email when a critical item is flagged — useful for purchase teams.

Fact box. Stockouts cost Indian SMBs in two ways: direct lost sales and indirect costs (emergency purchasing at higher prices, express freight, loss of goodwill). A low-stock alert fires before the stockout, not after. Even a static reorder level — set by judgment rather than formula — is far better than no alert at all.

How do I write the low-stock alert formula?

Basic status formula

Assuming your stock summary sheet has these columns as part of an Excel Table named Stock:

Column: Closing Stock (live quantity from SUMIFS)
Column: Reorder Level (the threshold you set per SKU)
Column: Status (the alert output)

In the Status column, enter:

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

This fills down automatically for every row in the table.

Two-tier alert

For items where you need to distinguish "running low" from "completely out":

=IF([@[Closing Stock]]=0, "OUT OF STOCK",
  IF([@[Closing Stock]]<=[@[Reorder Level]], "REORDER NOW", "OK"))

This gives three clear states:

OUT OF STOCK — zero units; urgent.
REORDER NOW — below threshold but not zero; order today.
OK — adequate stock.

Three-tier alert with a warning band

Some businesses want a "low" warning before hitting the hard reorder level. Use a "Warning Level" column (e.g. 1.5× the reorder level):

=IF([@[Closing Stock]]=0, "OUT OF STOCK",
  IF([@[Closing Stock]]<=[@[Reorder Level]], "REORDER NOW",
    IF([@[Closing Stock]]<=[@[Warning Level]], "RUNNING LOW", "OK")))

How do I apply conditional formatting for visual alerts?

Rule 1 — Red for OUT OF STOCK

Select the entire data range (e.g. A2:J200, excluding headers).
Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format".
Formula: =$J2="OUT OF STOCK" (replace J with the column letter of your Status column).
Format: Fill → Red; Font → White, Bold. Click OK.

Rule 2 — Orange for REORDER NOW

Repeat the process with:

Formula: =$J2="REORDER NOW"
Format: Fill → Orange; Font → Black, Bold.

Rule 3 — Yellow for RUNNING LOW

Formula: =$J2="RUNNING LOW"
Format: Fill → Yellow; Font → Black.

Important: In the Conditional Formatting Rules Manager (Home → Conditional Formatting → Manage Rules), ensure "OUT OF STOCK" rule sits above "REORDER NOW" which sits above "RUNNING LOW". Rules apply top-to-bottom; the first match wins.

How do I count how many items need reordering?

Add a summary block at the top of your sheet (above the table, in rows 1–4):

Total items below reorder level:

=COUNTIF(Stock[Status], "REORDER NOW") + COUNTIF(Stock[Status], "OUT OF STOCK")

Items completely out of stock:

=COUNTIF(Stock[Status], "OUT OF STOCK")

Items running low (warning tier):

=COUNTIF(Stock[Status], "RUNNING LOW")

Apply conditional formatting to the count cells themselves: make the "out of stock" count red if it is > 0, and the "reorder now" count orange if it is > 0.

Fact box. Freeze the top rows (View → Freeze Panes → Freeze Top Row, or freeze after row 4 if you have a summary block) so the reorder counts are always visible when you scroll through hundreds of SKUs. A buyer opening the file first sees "Items to reorder: 7 | Out of stock: 2" before reading a single row.

How do I filter to see only items that need reordering?

Excel's built-in filter is the fastest option:

Click any cell in your table header.
Data → Filter (or Ctrl+Shift+L).
Click the dropdown on the Status column.
Uncheck "OK" and "RUNNING LOW" — leave "REORDER NOW" and "OUT OF STOCK" checked.
The table now shows only actionable items.

For a permanently visible reorder-only view, create a second sheet and use a dynamic formula:

=FILTER(Stock, (Stock[Status]="REORDER NOW")+(Stock[Status]="OUT OF STOCK"), "No items to reorder")

This requires Excel 365 or Excel 2019+. The FILTER function shows only the rows matching the condition and updates automatically.

How do I link the reorder level to a calculated ROP?

A static reorder level quickly becomes stale. Link it to your ROP calculator (see Reorder Point & EOQ Calculator ») so the threshold updates when usage or lead time changes.

On your ROP sheet, build a table with Item Code and Calculated ROP. On your stock summary, use VLOOKUP to pull the ROP:

=IFERROR(VLOOKUP([@[Item Code]], ROP_Table[Item Code]:ROP_Table[ROP], 2, 0), 0)

Now when you update daily usage or lead time in your ROP table, the reorder alerts on the stock summary update automatically.

Free low-stock alert template

The free Excel template includes:

Stock Summary sheet with Status formula (three-tier alert).
Conditional formatting rules pre-applied.
Summary count block at the top (items out of stock, items to reorder, items running low).
Filter-ready setup.
FILTER-based reorder list sheet (for Excel 365/2019+).

Free to download, no sign-up.

Download the Low-Stock Alert Excel Template »

How Ankeshan helps: Ankeshan shows a live reorder dashboard inside Excel — items below their ROP are flagged automatically from transaction data, with one-click access to raise a purchase order template. It's launching soon; join the waitlist.

Frequently asked questions

Can I get an email alert when an item hits the reorder level? Yes, with a VBA macro. The macro checks the Status column when the workbook opens or on a button click and sends an Outlook email listing items flagged "REORDER NOW". This requires Outlook installed on the same PC. Without VBA, you can instead share the file via OneDrive and set a daily reminder to check the count cells.

Why does my conditional formatting not cover the whole row? The formula in the rule must use an absolute column reference with a relative row reference: =$J2="REORDER NOW" (dollar sign before J only, not before 2). If you write =$J$2, every row checks row 2 and the formatting applies incorrectly.

Should my reorder level include safety stock? Yes. The reorder point (which you set as the reorder level) should already include safety stock — it equals (average daily usage × lead time) + safety stock. Do not set the reorder level to just average demand without safety stock, or you will run out before the order arrives.

My closing stock formula sometimes goes negative — is that a problem? A negative balance usually means an OUT transaction was recorded without a corresponding IN, or the opening stock was not entered. It is not a formula error — it is a data problem. Add a column =IF([@[Closing Stock]]<0,"CHECK DATA","") to flag these rows and investigate each one.

How do I handle items with zero reorder level (i.e., I order on demand)? Set the Reorder Level to -1 for on-demand items. Since closing stock is always ≥ 0, the condition Closing Stock <= -1 is never true and the alert never fires. Or use a separate column to mark on-demand items and exclude them from the COUNTIF.

Sources

CBIC GST rate notifications effective 22 September 2025 (56th GST Council).
CGST Rules 2017, Rule 56 — Accounts and Records (cbic.gov.in).

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.