When a statement shows withdrawals in one column and deposits in another, combine them with =E2-D2, credit minus debit, so money out is negative and money in positive. It works if blanks are truly empty. If your cells hold text or empty strings, use =SUM(E2)-SUM(D2) instead and add a check for text.
Which formula should you use?
Take withdrawals in D, deposits in E and the new net amount in F.
- =E2-D2 is the plain version. A truly empty cell counts as zero in arithmetic, so a row with only a withdrawal gives a negative number.
- =SUM(E2)-SUM(D2) never shows #VALUE!, because SUM skips text in a reference.
- =N(E2)-N(D2) also never errors, since N turns text into 0.
Why is SUM safer than subtraction on text cells?
Say D3 contains a dash or an apostrophe-prefixed number copied from a PDF. =E3-D3 returns #VALUE! and the error spreads into every total that depends on column F. =SUM(E3)-SUM(D3) ignores the text and returns the deposit alone.
That safety has a catch. The text is treated as zero without a warning, so a real withdrawal stored as text vanishes from the net amount. The cure is to count text cells next to the data:
- =SUMPRODUCT(--ISTEXT(D2:E500)) should return 0.
- If it does not, find the rows with a filter, convert them to numbers and then go back to the plain formula.
| Row | Debit (D) | Credit (E) | Net (F) |
|---|---|---|---|
| Rent | 18,000 | blank | -18,000 |
| Salary | blank | 52,000 | 52,000 |
| Groceries | 2,350 | blank | -2,350 |
| Refund | blank | 1,299 | 1,299 |
Do you keep the original debit and credit columns?
Yes. Leave them exactly as imported and put your net column beside them, perhaps shaded. When a total looks wrong months from now, you will want to see what the bank actually printed in each cell. A net column built from the originals can be rebuilt at any time; an overwritten original cannot.
How do you check the combined column?
Two quick tests show whether the combination lost anything.
- =ROUND(SUM(F2:F500)-(SUM(E2:E500)-SUM(D2:D500)),2) should be 0. It compares the sum of nets with total credits minus total debits.
- =SUM(F2:F500) should equal the closing balance minus the opening balance on the statement.
Worked example with hypothetical figures
Say a month has debits of 18,000, 2,350 and 500, and credits of 52,000 and 1,299. Total debits are 20,850 and total credits are 53,299, so the net is 53,299 minus 20,850, which is 32,449. If you add up column F row by row you should land on 32,449 too. An answer of 31,150 would mean the 1,299 refund was missed, perhaps because it was stored as text.
What about rows where both are blank?
Headers, subtotals and blank lines will produce a net of 0, which is harmless. Page-break rows in PDF conversions sometimes hold stray text such as "Continued". Those rows will not affect SUM, but they make the column look as if it contains data. Filter for zeros occasionally and delete rows that are not real transactions.
What if the columns sit in a different order?
The formula follows the cells, not the headings, so check which column is which before you fill down. Leave any balance column out of the formula altogether. If your data is an Excel Table, you can write the same thing with column names, =[@Deposit]-[@Withdrawal], which stays correct even if someone moves the columns around later.
Final checklist
- Original columns kept as imported.
- Net formula filled down.
- Text count shows 0.
- Net total matches closing minus opening balance.
SpendixAI saves you a column here, since statements uploaded as PDF, CSV or Excel are reviewed row by row before saving; the layout of your bank's file may still need a careful look.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


