Spreadsheets

Conditional formatting that flags overspending in your budget sheet

Colour your budget sheet amber at 80 percent and red at 100 percent of each category budget with two formula-based conditional formatting rules.

Editorial illustration for Conditional formatting that flags overspending in your budget sheet

To flag overspending, add two formula-based conditional formatting rules to your budget grid. Amber fires when a category has used 80 percent or more of its budget, red fires when it has used all of it. Both rules compare the Spent column with the Budget column, so the colours update by themselves as you add expenses.

What should the grid look like first?

Use a simple layout: Category in A, Budget in B, Spent in C (a SUMIFS from your expense log), and Used in D. Put the amber trigger, 0.8, in a cell on the same sheet, such as F1 with a label Amber at in E1, so you can change it later. Keep it on this sheet because Google Sheets rules cannot read cells on another tab directly. Here is a hypothetical month.

CategoryBudget (₹)Spent (₹)UsedColour
Groceries12,0009,00075%None
Fuel4,0003,40085%Amber
Eating out3,0003,450115%Red
Electricity2,5002,500100%Red
Mobile and internet1,2001,10092%Amber

The D column formula is =IF(B2>0, C2/B2, ""). The guard stops a divide-by-zero error for a category with no budget.

Add a total row at the bottom with =SUM(B2:B6) and =SUM(C2:C6), and extend the same two rules to cover it, so the whole month gets an overall colour as well. If you want a quick visual instead of colours, Excel also offers data bars and icon sets from the same Conditional Formatting menu. They work well for a glance, but their thresholds are less flexible than a formula, which is why the formula rules are used here.

How do I set the two rules?

In Google Sheets select A2:D10, open Format, Conditional formatting, choose Custom formula is, and enter the rule. In Excel select the same range, then Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format. The dollar sign before the column letters matters, because it keeps every cell in the row looking at columns B and C.

  • Red rule: =AND($B2>0, $C2>=$B2). Fill a soft red.
  • Amber rule: =AND($B2>0, $C2>=$F$1*$B2, $C2<$B2). Fill a soft amber.
  • Optional green for safe rows: =AND($B2>0, $C2<$F$1*$B2).

Because the amber rule excludes the red zone, the two never both apply, so rule order does not matter. Anchoring F1 with dollar signs keeps every row pointing at the same trigger cell.

Is 80 percent too late on the 10th of the month?

It can be. A category at 85 percent on the 28th is fine; the same figure on the 10th is a problem. Add a pace rule that compares usage with how far the month has gone: =AND($B2>0, $C2/$B2 > DAY(TODAY())/DAY(EOMONTH(TODAY(),0))). On the 10th of a 30-day month it compares against 33 percent. Use it as a third colour, maybe a pale yellow, and treat it as a nudge rather than an alarm.

Why is nothing changing colour?

  1. Check that the Spent cells are numbers, not text. Left-aligned amounts are a warning sign.
  2. Confirm the rule range covers every row you want, and that it starts at row 2 with $B2, not $B$2.
  3. Look for two rules fighting. In Excel open Manage Rules and look at the order; in Sheets the top rule wins.
  4. Make sure the amber trigger cell F1 holds 0.8 or 80%, not the number 80.

A short checklist to finish

Set a Budget for every line, including a small one for Other. Test the rules by typing a large amount into a Spent cell and watch it turn red, then undo. Review the amber level after two months; if everything is always amber, your budgets are probably too tight rather than your spending too high. If your expenses come from imported statements, SpendixAI also has budgets of its own on Pro, so you can compare how each approach feels.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports