One of the most common problems in inventory management is finding out you've run out of stock after a customer orders it. A low stock alert in Excel solves this by automatically flagging items that are approaching their reorder point — before you run out.
In this guide you'll build a low stock alert system using Excel's IF formula and conditional formatting. No VBA, no macros — just formulas that work in Excel 2016 and later.
What You'll Need
Your spreadsheet needs these columns to make this work:
| Column | What It Contains | Example |
|---|---|---|
| SKU | Item identifier | SKU-001 |
| Item Name | Product description | Widget A |
| Qty on Hand | Current stock level | 18 |
| Reorder Point | Minimum stock level before ordering | 30 |
| Status | Alert column (formula goes here) | Low Stock |
Step 1 — Build the Status Formula
In your Status column (let's say column E, row 2), enter this formula:
This formula does three things:
- If Qty on Hand is 0 → shows "Out of Stock"
- If Qty on Hand is less than Reorder Point → shows "Low Stock"
- If Qty on Hand is at or above Reorder Point → shows "OK"
Drag this formula down to cover all your items.
=IF([@Qty]=0,"Out of Stock",IF([@Qty]<[@ReorderPoint],"Low Stock","OK")) — and it will automatically apply to new rows.Step 2 — Add Conditional Formatting
Now make the Status column color-coded so you can see alerts at a glance.
Select your Status column
Highlight all cells in the Status column that contain your formula (e.g. E2:E100).
Open Conditional Formatting
Go to Home → Conditional Formatting → New Rule → "Format cells that contain".
Set up the Out of Stock rule
Cell value → equal to → "Out of Stock". Set fill color to red, font color to dark red. Click OK.
Set up the Low Stock rule
Add another rule: Cell value → equal to → "Low Stock". Set fill to amber/yellow, font to dark orange. Click OK.
Set up the OK rule
Add a third rule: Cell value → equal to → "OK". Set fill to light green, font to dark green.
Step 3 — Add a Days of Stock Column (Optional but Useful)
This tells you how many days before you run out, so you can prioritize which items to order first.
Where F2 is your Average Daily Sales column. This gives you a number like "6 days" — meaning you have 6 days of stock left at current sales pace.
Step 4 — Filter to See Only Problem Items
Once your alerts are set up, use Excel's filter to see only the items that need attention:
- Click any cell in your data → Data → Filter
- Click the dropdown arrow on the Status column
- Uncheck "OK", leave only "Low Stock" and "Out of Stock" checked
- Now you see only the items that need to be ordered
📦 Get the Pre-Built Inventory Dashboard Template
All formulas and conditional formatting already set up. Download free.
⬇ Download Free TemplateCommon Mistakes to Avoid
How to Turn This Into a Dashboard
Once your low stock alerts are working, you can build a summary dashboard on a separate sheet:
- Count of low stock items:
=COUNTIF(Sheet1!E:E,"Low Stock") - Count of out of stock items:
=COUNTIF(Sheet1!E:E,"Out of Stock") - Total items OK:
=COUNTIF(Sheet1!E:E,"OK")
Display these as KPI cards at the top of your dashboard for an instant overview every morning.