Statements often print a dash or the word NIL in an empty debit or credit cell. To make them zero, select the amount cells, open Go To Special, choose Constants, tick only Text, type 0 and press Ctrl+Enter. First make sure real amounts are already numbers, because this method zeroes every text cell it finds.
Why do dashes and NIL cause trouble?
SUM and SUMIFS skip text, so a dash in a total column does no harm. The damage comes with arithmetic, such as =E2-D2 for credit minus debit: a dash or NIL in either cell returns #VALUE!. Counts also go wrong, because COUNTA treats a dash as a filled cell while COUNT does not, and filters and pivot tables treat the column as mixed text and numbers.
Method 1: Go To Special, then fill with zero
- Copy the sheet first.
- Select the debit and credit data cells only, not the header.
- Press F5, click Special, and choose Constants.
- Untick Numbers, Logicals and Errors so only Text stays ticked, then press OK.
- Type 0 and press Ctrl+Enter, which fills every selected cell at once.
Test the results: =SUMPRODUCT(--ISTEXT(D2:E500)) should now be 0.
Method 2: Find and Replace on whole cells
Press Ctrl+H, type a single dash in Find what and 0 in Replace with. Now click Options and tick Match entire cell contents. Without that tick, Excel replaces the dash inside entries like -1,200 and ruins your negatives. Repeat for NIL, and untick the match-case option so Nil and nil are included.
Method 3: a formula, when you want to keep the original
- =IF(ISNUMBER(D2),D2,IF(OR(TRIM(D2)="-",UPPER(TRIM(D2))="NIL",TRIM(D2)=""),0,VALUE(SUBSTITUTE(D2,",",""))))
- Numbers pass through unchanged.
- A dash, NIL or an empty-looking cell becomes 0.
- Anything else, such as 1,250.00 stored as text, is converted by VALUE, so a real amount is not zeroed by mistake.
- A stray word like N/A makes VALUE fail with #VALUE!, which is what you want: it shows you the oddity.
You may be tempted to use IFERROR(VALUE(D2),0) because it is shorter. It also swallows genuine errors and quietly gives them a zero, so use it only when you have already inspected every non-numeric entry. The N function, =N(D2), is another shortcut, but N("1,250.00") returns 0 for text amounts, which silently loses money.
| Cell in the statement | Go To Special | Formula above |
|---|---|---|
| - | 0 | 0 |
| NIL | 0 | 0 |
| 1,250.00 as text | 0, wrongly | 1250 |
| 1250 as number | unchanged | 1250 |
Do you really need zero rather than blank?
Sometimes a blank is better than a zero. Zeros pull down an average of the column and show up as marks in charts. If you only need SUM and SUMIFS to work, you can leave the dashes in place and let those functions ignore them. Zero is the right choice when you subtract columns or feed the numbers to something that expects every cell to hold a number. To hide zeros visually, apply the custom format #,##0.00;-#,##0.00; which shows nothing for zero.
A worked example
Say a month has five rows, with debits of 2,350, "-", 18,000, "NIL" and 500, and credits of "-", 52,000, "-", "-" and "-". After replacement, debit becomes 2,350, 0, 18,000, 0 and 500, totalling 20,850. Credit becomes 0, 52,000, 0, 0 and 0, totalling 52,000. Credit minus debit is 31,150.
Finish with a checklist
- Convert real text amounts to numbers first.
- Choose the method: Go To Special for speed, formula for safety.
- Run the ISTEXT count to confirm zero text cells.
- Tie SUM of each column to the statement totals.
When the same dashes appear every month, SpendixAI can take the statement as PDF, CSV or Excel and show the rows for review before saving, so you are not tidying columns again.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


