Put a percent-used column next to your budget, select it, and choose Conditional Formatting, Icon Sets, 3 Traffic Lights. Then open Manage Rules, edit the rule, reverse the icon order and set the thresholds so green means below 80 percent, amber means 80 to 100 percent, and red means over 100. If you prefer only the lights, tick Show Icon Only.
What do you need before adding icons?
Say Category is in A, Budget in B, Actual in C and the new column D shows percent used. In D2 type:
=IF(B2=0,"",C2/B2)
and fill it down. Format the column as a percentage. Excel stores 100 percent as 1, 80 percent as 0.8, and that matters when you set thresholds in a moment. Returning a blank when the budget is zero keeps empty rows from showing a stray icon, since text results are ignored by icon sets.
How do you apply and edit the icon set?
- Select D2:D20.
- Home, Conditional Formatting, Icon Sets, then 3 Traffic Lights (Unrimmed). At this point the icons are the wrong way round for a budget, because the green sits on the highest values.
- Home, Conditional Formatting, Manage Rules, choose the rule and press Edit Rule.
- Click Reverse Icon Order. Now red is the top icon.
- Set the red row to Value greater than 1, Type Number. Set the amber row to Value greater than or equal to 0.8, Type Number.
- Leave the last icon as green. Press OK.
The Type dropdown defaults to Percent, which means percent of the range between the smallest and largest value in the column, not percent used. Changing both rows to Number is the step most people miss, and it is why icons seem random on the first attempt.
What do the lights mean on real figures?
Say a monthly budget of ₹6,000 for dining out, with invented actuals.
| Actual | Percent used | Light |
|---|---|---|
| ₹4,500 | 75% | Green |
| ₹4,800 | 80% | Amber, since 0.8 is included |
| ₹5,400 | 90% | Amber |
| ₹6,300 | 105% | Red |
The arithmetic is 4,500 divided by 6,000 for 75 percent, and 6,300 divided by 6,000 for 105 percent. If the thresholds feel too strict or loose, change the numbers in the rule, not the formula.
How do you show only the icons?
In the Edit Rule box, tick Show Icon Only. The percentages stay in the cells but are hidden, which makes a tidy dashboard row. Keep the numbers visible while you test, and hide them at the end. Widen the column a little if the icons look cramped.
Which icon set is easiest to read?
The plain traffic lights rely on colour, and many people cannot tell red from green. Excel also offers sets that change shape, such as 3 Signs or 3 Symbols (Circled), where the red one is a cross-like mark and the green is a tick. Pick one of those if the sheet is shared, and the thresholds in the steps above stay the same.
Before you share the sheet
- Test one row for each colour using the figures above.
- Check that blank budget rows show no icon.
- Decide whether amber at 80 percent suits the category; a festival fund may need a different line from groceries.
If the lights sit too close to the numbers, set the column alignment to right or centre, and widen the column by a few characters. Icons are drawn at the left edge of the cell by default, so a narrow column can clip the percentage and make the sheet look broken when it is only cramped.
If you would rather get alerts than glance at a sheet, SpendixAI offers budgets on its Pro plan along with notifications and a weekly summary, built from statements you import.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


