You run out of vanilla extract on a Saturday morning, right when the wedding cake order is due. Nobody caught it because the number was buried in row 47 of a spreadsheet you last checked on Tuesday. Now you’re making a rush trip to the store and paying $6 more per bottle.
Scanning columns of numbers by hand is slow, and it’s easy to miss the one that matters. The fix takes about ten minutes. Excel’s Conditional Formatting feature can color a row red, yellow, or green the moment stock drops below your reorder point, with no manual checking.
This tutorial walks you through it using a simple inventory sheet. You’ll build three alert levels, then learn how to make an entire row light up instead of a single cell.
The Scenario: Sweet Crumb Bakery in Austin, TX
Maria owns a small bakery in Austin. She tracks her key ingredients in Excel and orders new supplies whenever stock runs low. Here’s her inventory sheet, which we’ll use throughout this tutorial.
| Item | Category | In Stock | Reorder Level | Unit Cost | Last Ordered |
|---|---|---|---|---|---|
| All-Purpose Flour (50 lb bag) | Dry Goods | 12 | 8 | $24.50 | 09/15/2026 |
| Unsalted Butter (lb) | Dairy | 6 | 20 | $4.85 | 09/22/2026 |
| Granulated Sugar (25 lb bag) | Dry Goods | 9 | 6 | $18.75 | 09/12/2026 |
| Vanilla Extract (16 oz) | Flavorings | 2 | 4 | $32.00 | 08/30/2026 |
| Large Eggs (dozen) | Dairy | 15 | 24 | $3.90 | 09/25/2026 |
| Semi-Sweet Chocolate Chips (10 lb) | Baking | 5 | 4 | $41.25 | 09/08/2026 |
| Heavy Cream (quart) | Dairy | 8 | 10 | $5.60 | 09/26/2026 |
| Baking Powder (10 oz) | Dry Goods | 3 | 2 | $7.40 | 09/01/2026 |
Type this into a blank worksheet with the headers in row 1 and the data in rows 2 through 9. Column C holds In Stock and column D holds Reorder Level. Every formula below relies on those two columns.
Step 1: Set Up Your Data Range
Conditional Formatting works best on a clean, consistent range. Before adding any rules, make sure your numbers are real numbers and not text.
Click cell C2 and drag down to C9 to select the In Stock column. Look at the Home tab. If the Number Format dropdown says General or Number, you’re set. If it says Text, change it to Number, or the rules won’t fire.
[INSERT SCREENSHOT: Excel worksheet with the bakery inventory table entered, cells C2:C9 highlighted, and the Number Format dropdown on the Home tab showing “Number”]
Why This Matters
Excel compares values mathematically. A number stored as text (often left-aligned with a tiny green triangle in the corner) won’t compare correctly against your reorder level. Fixing it now saves you from confusing results later.
Step 2: Create Your First Alert (Red for Below Reorder Level)
Start with the most urgent rule: anything at or below the reorder level turns red. This is the item Maria needs to order today.
- Select the range A2:F9 (the entire data area, not just one column).
- Click the Home tab, then click Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- In the formula box, type the formula below.
- Click Format, go to the Fill tab, pick a light red, and click OK twice.
=$C2<=$D2
[INSERT SCREENSHOT: The New Formatting Rule dialog box with “Use a formula to determine which cells to format” selected and the formula =$C2<=$D2 typed in the formula field]
How the Formula Works
Excel reads this as a yes-or-no question: “Is the number in column C less than or equal to the number in column D on this row?” If the answer is yes, it applies the red fill.
The dollar sign before each column letter ($C and $D) locks the formula to those columns. The missing dollar sign before the row number (2) lets Excel move down one row at a time, checking each item separately. That’s why the whole row turns red instead of only one cell.
Notice that the rule should catch butter (6 in stock, 20 needed), vanilla (2 vs. 4), eggs (15 vs. 24), and heavy cream (8 vs. 10). Those four rows should now be red.
[INSERT SCREENSHOT: The finished inventory table with the Unsalted Butter, Vanilla Extract, Large Eggs, and Heavy Cream rows filled in light red]
Step 3: Add a Yellow “Running Low” Warning
Red tells you what’s already a problem. Yellow tells you what’s about to become one.
Maria wants a warning when stock is within 25% above the reorder level. That gives her time to order before she runs short. Select A2:F9 again, then go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
=AND($C2>$D2, $C2<=$D2*1.25)
Click Format, choose a light yellow fill, and click OK twice.
How the Formula Works
The AND function means both conditions must be true at once. First, stock must be above the reorder level (otherwise the red rule already handles it). Second, stock must be no more than 25% above the reorder level, which is what $D2*1.25 calculates.
For chocolate chips, the reorder level is 4. Multiply by 1.25 and you get 5. Since stock is exactly 5, that row turns yellow. Baking powder (3 in stock, 2 needed) has a limit of 2.5, so it stays clear.
Want a bigger safety cushion? Change 1.25 to 1.5 for a 50% buffer.
Step 4: Add a Green “Well Stocked” Indicator
Green rows aren’t strictly necessary, but they give you quick confirmation that everything else is fine. This is useful when you’re glancing at the sheet between customers.
Create a third rule with this formula and a light green fill:
=$C2>$D2*1.25
This rule says, “If stock is more than 25% above the reorder level, mark it green.” Flour, sugar, and baking powder should now show green.
Check the Rule Order
Open Conditional Formatting > Manage Rules and confirm all three rules apply to =$A$2:$F$9. Because the three conditions never overlap, the order doesn’t change the result. Still, keeping red at the top is a good habit.
[INSERT SCREENSHOT: The Conditional Formatting Rules Manager dialog showing three formula rules listed with red, yellow, and green fill previews, all applied to =$A$2:$F$9]
Step 5: Test It With Live Changes
A rule you haven’t tested is a rule you can’t trust. Change a value and watch the color respond.
Click cell C4 (Granulated Sugar) and change 9 to 5. The row should switch from green to yellow, because 5 is now within 25% of the reorder level of 6…
Actually, check the math: 6 × 1.25 = 7.5, and 5 is below the reorder level of 6, so the row turns red. Try changing it to 7 instead, and you’ll see yellow. Testing with both values shows you exactly where each color boundary sits.
Pro-Tip: Fix Alerts That Don’t Trigger
If your colors aren’t showing up, run through these common causes.
Numbers stored as text. Select the column and look for a small green triangle. Click the warning icon and choose Convert to Number.
Wrong selection range. Open Conditional Formatting > Manage Rules, change the dropdown to This Worksheet, and check the Applies to column. It should cover every row you want colored.
Missing dollar signs. If only one column changes color, you probably typed =C2<=D2 instead of =$C2<=$D2. Edit the rule and add the dollar signs before the column letters.
Blank cells turning red. An empty cell counts as zero, so it looks “below” the reorder level. Wrap the formula to skip blanks:
=AND($C2<>"", $C2<=$D2)
Bonus: Automatically Include New Rows
If you add new ingredients, the rules won’t cover them unless your range grows too. Select your data and press Ctrl + T to convert it to a table. Rules applied to a table expand automatically as you add rows.
Wrap-Up
You now have a color-coded inventory sheet that flags problems the moment they happen. Red means order now, yellow means order soon, and green means you’re fine. No more surprise shortages on a busy weekend.
Try this on your own inventory today, whether you track flour, printer paper, or client supplies. Adjust the reorder levels and the 25% buffer to match how quickly you go through stock. Once it’s running, you’ll spend less time checking spreadsheets and more time running your business.