A negative stock balance means a missed receipt or a shipment that was not real. Computing the closing balance and flagging negatives catches the problem before it reaches a report.
SKU-200 opens at 10, receives 0 and ships 25, so it flags NEGATIVE at -15.
The example
Three SKUs. One ships more than it had.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Case | Opening | Received | Shipped | Status | Closing |
| 2 | SKU-100 | 50 | 20 | 30 | OK | 40 |
| 3 | SKU-200 | 10 | 0 | 25 | NEGATIVE | -15 |
| 4 | SKU-300 | 0 | 40 | 40 | OK | 0 |
The formula
The balance test sits inside IF:
How it works
Here is the logic, one piece at a time:
B2+C2-D2is the closing balance.<0tests for negative.IFreturns NEGATIVE or OK.- The second formula shows the balance.
Add conditional formatting on the status column to make negatives red.
Try it: interactive demo
Enter opening, received and shipped quantities.
Variations
Count flags
Count negatives.
Low stock
Flag below a reorder point.
Pitfalls & errors
Zero balance is not negative; the flag uses < 0.
Check timing: a receipt posted after the shipment can create a false negative.
Review flags weekly.
Practice workbook
Frequently asked questions
Can a negative be valid?
Fix it?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class