If a refund turns up as spending after you import a file, the amount column probably lost its direction on the way in. Many statement files carry money in and money out in separate columns, and an importer that reads both as plain positive numbers cannot tell a refund from a purchase.
The two common file layouts
| Layout | How a £39.99 refund appears | Risk |
|---|---|---|
| One Amount column, negative for money out | 39.99 | Usually read correctly |
| Two columns, Paid out and Paid in | 39.99 under Paid in, blank under Paid out | Both can be read as a positive amount |
| One Amount column, all positive, plus a Type column | 39.99 with Type 'Credit' | The Type is ignored and the row is a spend |
Open the file in Excel or Google Sheets before importing, and look at the layout. Your bank's file may have different headings, so go by what you see.
Build one signed column
Suppose column B is Paid out, column C is Paid in, and your data starts on row 2. In a new column D, enter this formula and copy it down:
- =N(C2)-N(B2)
- This gives a positive number for money in and a negative number for money out, even if one of the cells is blank.
- Check the total of the column with =SUM($D$2:$D$500) and compare it with the net movement on the statement.
Save a copy as CSV with the date, description and this signed column, and import that. N() turns text and empty cells into zero in Excel, so a stray blank will not break the sum.
A worked check in pounds
Say the statement shows money out of £1,284.70, money in of £2,150.00 including a £39.99 refund, and net movement of +£865.30. If the importer treats the refund as a purchase, your tracker shows money out as 1,284.70 + 39.99 = £1,324.69 and money in as 2,150.00 - 39.99 = £2,110.01. The net is 785.32, not 865.30, and the gap of £79.98 is exactly twice the refund. That doubling is the fingerprint.
How do I fix it after the import?
- Compare totals of money in and money out with the statement.
- If the gap equals twice an amount, search for that amount.
- Flip the row so it is money in, and give it the category of the original purchase.
- If many rows are wrong, delete the import, correct the file and import again.
What if we cannot edit the file?
Then fix the rows one at a time after importing, but search the amounts first so you do not miss one. Rules about how your bank labels reversals and refunds differ, so read the description on each row. A refund description often mentions the shop and the word refund or credit, but not always.
If you cannot edit the file, you can still check the signs without a formula. Sort the original by the Paid in column, and look at the top rows; each should be money you know you received. Then sort by Paid out and confirm the largest items are purchases. A few seconds of sorting often reveals a refund sitting in the wrong column.
Be careful when you save the edited file: keep the original untouched, and name the corrected copy clearly, such as the month followed by 'signed'. If something goes wrong later, you can return to the bank's own file and start again.
A short routine
Each month, sum the signed column, compare it with the net movement printed on the statement, and only then import. It takes two minutes and keeps every refund on the right side.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

