BuildQuantify logoBuildQuantify
Excel Tutorials / Budget Alerts with Conditional Formatting
COST TRACKING

Budget Alerts with Conditional Formatting

8 min read · Template referenced below

A budget spreadsheet only helps if you actually notice when something goes over. Conditional formatting turns passive numbers into an at-a-glance warning system.

1
Set up budget vs. actual columns.
Column B: Budgeted Amount. Column C: Actual Spent. Column D: =C2/B2 for percent of budget used.
2
Format column D as a percentage.
Select the column, then Home → Number Format → Percentage, so 1.15 displays as 115% instead of a raw decimal.
3
Add a red alert rule for over-budget items.
Select column D, then Conditional Formatting → New Rule → "Format only cells that contain" → Cell Value → greater than → 1. Set a red fill — anything over 100% of budget lights up immediately.
4
Add a yellow warning rule for items approaching budget.
Add a second rule: "between" 0.85 and 1, yellow fill — flags items closing in on their limit before they actually go over.
5
Optional: add data bars for a visual scan.
Conditional Formatting → Data Bars applies a proportional bar inside each cell — useful for scanning which categories are furthest along at a glance.
Order your conditional formatting rules carefully — Excel applies them top to bottom and stops at the first match unless "Stop If True" is unchecked, so a red rule listed above a yellow rule can silently override it.

Once this is set up, a weekly glance at the sheet tells you exactly which cost categories need attention — no need to manually calculate percentages every time.

Worked example: three cost categories

CategoryBudgetActual% UsedAlert
Concrete$120,000$138,000115%Red (over budget)
Framing$85,000$76,50090%Yellow (approaching)
Electrical$60,000$42,00070%None

With the red rule (greater than 1) and the yellow rule (between 0.85 and 1) in place, Concrete turns red the moment the actual spend crosses the budget, and Framing turns yellow as a warning to the project manager.

Highlight the whole row, not just one cell

Select the full table range, create a rule using a formula, and enter =$D2>1. The dollar sign before D keeps the rule pointed at the percentage column while the row number changes, so the entire row is colored.

Common mistakes

  • #DIV/0! errors. A category with a zero or blank budget breaks the percentage. Use =IF(B2=0,"",C2/B2).
  • Rules in the wrong order. Use Home → Conditional Formatting → Manage Rules to put the red rule above the yellow rule, since the order decides which format wins when more than one rule applies.
  • Percent versus decimal. A cell showing 115% holds the value 1.15, so rules must compare to 1, not 100.
  • Alert thresholds that are too late. Many teams use 85–90% as the warning level so there is still time to act.

Frequently asked questions

Can I get an email when something goes over budget?

Not from conditional formatting alone. Excel can do it with Power Automate or a macro, but for most teams a weekly review of the highlighted rows is enough.

Does conditional formatting slow down large workbooks?

Only when applied to entire columns with complicated formulas. Limit the range to the cells you actually use.

Back to all tutorials →
← Back to all tutorials