Low-Stock Alerts That Catch Your Eye
Okay. Last lesson we set up our first color rule and I typed it backwards live and turned everything red, which, sooo, that happens. This lesson we're doing the version that actually matters for your business: making a cell yell at you when you're running low on something.
This is the whole reason I got into conditional formatting in the first place. It's not the pretty part. It's the useful part.
Why this beats a chart every time
I'll say this again because I believe it: everybody wants a fancy dashboard with a pie chart. But a chart just sits there looking nice. A cell that turns red when you've got two left of something does actual work. You glance at your sheet, red jumps out, you know to reorder. No reading required. No remembering to check a report. Just color doing the noticing for you.
That's the whole opinion. Color that means something beats a picture that's just pretty.
Setting it up
You're gonna use the same conditional formatting menu from last lesson, we're just writing a smarter rule.
- Select your stock quantity column. Not the whole sheet, just that column, header down to your last row.
- Format, then Conditional formatting. Same spot as before.
- Under "Format cells if," choose "Less than or equal to."
- Type in your threshold number. If you reorder when you hit 5 units, type 5.
- Pick a format. I go bold red text on a light red background. You want it obvious, not subtle. This isn't the moment for a tasteful pastel.
- Hit Done.
Now any cell at or below your number lights up on its own. You don't touch it again unless the number changes.
The part people skip: your threshold isn't the same for everything
Here's where it gets a little more real. Your reorder point for something you sell three of a month is not the same as something you sell thirty of a week. If you set one flat number for your whole column, you're gonna get it wrong for half your products.
So either:
- Set a separate rule for a product that needs a different threshold, or
- Add a column called "Reorder Point" where you type a custom number per row, then write your conditional formatting rule to compare the stock column against that column instead of a flat number.
The second one's more setup up front but it's the one I'd actually use if you've got more than a handful of products. It's more accurate and future-you will thank you, because future-you won't remember why item 12 has a different rule than item 4.
A quick story about keeping it simple
My husband Daniel fixes up bikes on the side. For about a year he refused to track any of it. None. Just vibes and a shoebox of receipts.
I built him an invoice sheet. One page. No lookups, no fancy stuff, nothing that would make him close the laptop and go back to the shoebox. Just what he sold, what he charged, and a total.
He uses it now. Won't admit I was right, but he uses it.
The point is, if I'd handed him a color-coded low-stock alert system with custom reorder points and three tabs, he'd have never opened it again. Match the tool to the person actually using it. Sometimes that person is you at 9pm and you do not want to think hard.
A real caution here
Don't set your threshold at zero. I know that sounds obvious, but I've seen people do it because zero feels like "the truthful moment I'm actually out." Problem is, by the time you hit zero you've already had a day, maybe a week, where a customer asked for something you didn't have and you found out the hard way. Set the number high enough that it catches you while you still have time to reorder and it actually arrives. Think about how long your supplier takes, and set the number with that in mind, not with how it feels emotionally satisfying to see a "0."
Double-check it against reality
Once your rule's live, walk over and actually count a couple of the items that are flagged red. Make sure the number in your sheet matches what's really on the shelf. I say this in basically every lesson because it's saved me more than once. A spreadsheet will happily show you a confident red alert on a number that's wrong. Trust the color, but verify the count.
Before next time: pick three products you always seem to run out of at the worst moment, and set a real reorder threshold for each one. Not a guess, an actual number based on how fast they sell.