#VALUE! means a formula is being asked to do arithmetic on something that is not a number, or two ranges are different sizes where they must match. In a sheet built from bank statements, the usual culprits are text sitting in the Amount column, hidden spaces copied in with the figures, and dates stored as text.
Why does a statement-based sheet get #VALUE!?
When you paste a statement, figures can arrive as text. An amount like "1,200 Dr" or "₹1,200" with a stray space looks like a number but is not one. Excel left-aligns it, and any formula that adds it with + returns #VALUE!. SUM does not complain: it silently ignores text, so your total comes out too low with no error to warn you.
What are the common causes?
- Text in a number cell: "1,200 Dr", "NA", a dash, or an amount with a rupee sign typed as text.
- Hidden spaces: a normal space at the end, or a non-breaking space, character 160, copied from a PDF or web page.
- Dates stored as text: 15/03/2026 pasted so it sits left in the cell and will not sort by date.
- Mismatched ranges: SUMPRODUCT with $C$2:$C$500 and $B$2:$B$400, or SUMIFS whose ranges have different sizes.
- Text in the middle of arithmetic: =B2*C2 when C2 holds the word "Pending".
How do I find the offending cell?
- In a spare column write =ISTEXT(C2) next to the first amount and fill it down. Filter for TRUE.
- Compare =COUNT($C$2:$C$500) with =COUNTA($C$2:$C$500). COUNT counts numbers; COUNTA counts everything filled. If they differ, the difference is the number of text cells.
- Use =LEN(C2) against what you can see. A figure like 1200 should have a length of 4; if it says 5 there is a hidden space.
- Select the column, go to Home, Find and Select, Go To Special, choose Formulas and untick everything except Errors. This jumps to the cells showing an error.
How do I turn text into numbers?
In a helper column write:
- =VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(C2,CHAR(160)," "),",","")))
- Strips non-breaking spaces and commas, trims spaces and converts the result to a number. If the cell also holds "Dr" or "Cr", remove the letters first with another SUBSTITUTE.
Then copy the helper column, paste as values over the original and delete the helper. Alternatively select the text numbers, choose Data, Text to Columns and press Finish; Excel re-reads the cells as numbers. Another quick trick: type 1 in a blank cell, copy it, select the amounts, Paste Special, Multiply.
What about dates stored as text?
Test with =ISNUMBER(A2). If it returns FALSE, the date is text. If your regional settings are day-first, =DATEVALUE(A2) turns "15/03/2026" into a real date; check the first few results, since a day-first date read as month-first is wrong or errors out. Format the column as a date afterwards.
| Symptom | Likely cause | Test |
|---|---|---|
| Number sits on the left of the cell | Text | =ISTEXT(C2) |
| SUM is lower than expected | Text cells ignored | COUNT versus COUNTA |
| #VALUE! after + or * | Text in the arithmetic | Go To Special, Errors |
| #VALUE! in SUMIFS or SUMPRODUCT | Ranges of different sizes | Check every range has the same rows |
| Date will not sort | Date stored as text | =ISNUMBER(A2) |
Can a different tool avoid the clean-up?
Some of this work is unavoidable in a sheet. SpendixAI takes PDF, CSV or Excel statements and shows the rows for you to review before saving, so amounts arrive as amounts. A sheet can then be kept for your own calculations.
Checklist
- Filter the amount column with ISTEXT.
- Compare COUNT and COUNTA.
- Clean with SUBSTITUTE, TRIM and VALUE.
- Check every range in SUMIFS and SUMPRODUCT is the same size.
- Convert text dates and test with ISNUMBER.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


