Select the amounts, go to Home, Conditional Formatting, Highlight Cells Rules, Greater Than, then click a cell that holds your limit, such as H1, and choose a colour. Because the rule reads the limit from that cell, you can change ₹5,000 to ₹10,000 later by typing a new number, not by opening the rule again.
How do you set the rule up?
Say your expense list has amounts in C2:C500 and you keep a label "Flag above" in G1 with the limit itself in H1.
- Type 5000 in H1. This is an invented limit; use whatever suits you.
- Select C2:C500.
- Choose Home, then Conditional Formatting, then Highlight Cells Rules, then Greater Than.
- In the box, click cell H1. Excel writes =$H$1. Keep the dollar signs.
- Pick Light Red Fill with Dark Red Text, or choose Custom Format. Press OK.
Now change H1 to 8000 and watch the highlights shrink. Nothing else has to be touched.
You can go one step further and use two limits. Keep an amber limit of ₹5,000 in H1 and a red limit of ₹20,000 in H2, create one Greater Than rule for each, and open Manage Rules. Put the red rule on top and tick Stop If True on it. A ₹25,000 payment then turns red only, and a ₹7,000 payment turns amber. Without the stop, a big amount satisfies both rules and the colour you see depends on rule order alone.
Why must the limit reference be absolute?
The rule is stored relative to the first selected cell. If the reference is =H1 without dollar signs, the cell below compares against H2, the next against H3, and so on, and most of your list is checked against blanks. With =$H$1 every amount is compared to the same cell. Excel normally adds the dollar signs when you click the cell in this dialog, but a limit you typed in by hand might not have them.
How do you highlight the whole row, or include the limit itself?
The Greater Than rule only colours the cells you selected, and it excludes exactly equal amounts. For the other cases use a formula rule: select A2:E500, choose New Rule, then Use a formula to determine which cells to format.
- Whole row when above the limit: =$C2>$H$1
- Whole row at or above the limit: =$C2>=$H$1
- Only positive spending, ignoring refunds recorded as negatives: =AND($C2>0,$C2>$H$1)
Is there a way to flag the biggest few without a limit?
Yes. Select C2:C500, then Conditional Formatting, then Top/Bottom Rules, then Top 10 Items, and change 10 to 5 to flag the five largest transactions. This always follows the current data, so it is a good companion rule beside a fixed limit: one tells you what crossed your line, the other tells you what stands out this month.
| Amount | Limit in H1 | Highlighted? |
|---|---|---|
| ₹4,999 | ₹5,000 | No |
| ₹5,000 | ₹5,000 | No, equal is not greater |
| ₹5,001 | ₹5,000 | Yes |
| ₹7,500 | ₹8,000 | No after you change the limit |
Housekeeping
- To remove a rule, select the range and choose Conditional Formatting, then Clear Rules, then Clear Rules from Selected Cells.
- To see every rule on the sheet, use Manage Rules and choose This Worksheet.
- Label the limit cell clearly so that a future you knows what it controls.
If the list is long, combine the highlight with a filter: choose Filter by Color in the heading drop-down and show only the highlighted rows. That turns the colour into a worklist of large payments to check against your statement.
Large transactions are also easy to find outside a sheet. SpendixAI's report library and Report builder let you filter and group imported transactions, and download the result as CSV, which you can bring back here if you like.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


