Select the amount column, open Data, Data Validation, set Allow to Decimal and Data to greater than, type 0 as the minimum, and add a Stop error message. Excel then refuses text, zero and negative figures in that column. A second, formula-based rule can stop a row being started without an amount.
What are the steps for a numbers-only amount column?
- Select the amount cells, for example D2:D500.
- Choose Data, Data Validation. On Settings, set Allow to Decimal and Data to greater than.
- In Minimum type 0.
- On Input Message you can add a prompt such as Enter the amount in rupees, numbers only.
- On Error Alert, keep Style as Stop, title it Amount not accepted and write something specific, such as Type only a number above zero, without the rupee sign or words.
Decimal allows paise, so 450.50 passes. Whole Number would reject it.
Why does 1,200 rs get blocked?
Excel reads "1,200 rs" as text because of the letters after the number. A Decimal rule only accepts true numbers, so the entry is refused and your message appears. Plain 1,200 is accepted as 1200, so commas are fine. This blocking is useful because text amounts are invisible to SUM and SUMIFS, which skip them quietly and leave you with totals that look right and are wrong.
Should the rule cap the size of an amount?
Between works better than greater than if you want a guard against an extra zero. Set Data to between, Minimum 0.01 and Maximum 500000 (an invented ceiling; choose one that suits your life). Then ₹4,500 typed as 45000 is still fine, but ₹4,500 typed as 4500000 by mistake bounces. Write the message to say what the ceiling is, so that a real large payment, such as an annual fee, does not leave the person stuck.
| Typed entry | Result with greater than 0 | Why |
|---|---|---|
| 450.50 | Accepted | A decimal above zero |
| 1,200 | Accepted | Read as 1200 |
| 1,200 rs | Rejected | Contains letters, so it is text |
| 0 | Rejected | Not greater than zero |
| -300 | Rejected | Negative |
How do you stop rows with no amount?
Validation fires only when a cell is edited, so an empty cell is never questioned. You can steer the order of entry instead. Select the date cells, A2:A500, choose Allow Custom, and use the formula:
=AND(ISNUMBER($D2),$D2>0)
With an error message such as Enter the amount first. A date can now only be typed once the amount on that row is a valid number. It forces amount first, which some people find annoying, so use it only if blank amounts are your real problem. For a gentler check, add a flag column beside the table: =IF(AND($B2<>"",$D2=""),"Amount missing","").
What about refunds?
A refund is usually recorded as a negative amount or as a separate income row. If you want negatives, use Decimal not equal to 0, or add a Type column that says Spend or Refund and keep every amount positive. Decide once, and write it on the sheet itself, because mixing both styles produces totals nobody trusts.
Closing checklist
- Test with 450.50, 0, -300 and text.
- Write a message that tells people exactly what to type.
- Run Circle Invalid Data after any big paste.
- Format the column as a number with two decimals.
Finish by formatting the amount column as Number with two decimal places, and right-aligning it. Consistent alignment makes text entries that slipped in earlier stand out immediately, since text sits to the left while real numbers sit to the right.
SpendixAI reads amounts from the statement files you upload and lets you review the rows before they are saved, which removes most hand-typed amount errors for card and bank spending.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

