If you have a statement from NatWest in CSV form, open it through Excel's Data, From Text or CSV option or Google Sheets' File, Import, rather than double-clicking. That lets you set the separator and column types, which stops dates flipping and long numbers turning into scientific notation. Then check signs and totals before you rely on the file. Look in your bank's own help for how to export.
Open the file the safe way
- In Excel, use Data, then From Text or CSV, and pick the file.
- Check the preview: columns should be split, with a header row.
- Set the date column to the format the file uses, and the description column to text.
- In Google Sheets, use File, Import, Upload, and choose to replace the sheet.
- Save a copy as a normal workbook before editing.
Common problems and fixes
| Problem | Why | Fix |
|---|---|---|
| Everything in one column | Separator is not a comma | Choose the correct delimiter in the import dialog |
| Dates swapped | Day and month read the wrong way round | Set the date format when importing |
| Reference shows 1.23E+11 | Long number treated as a figure | Import that column as text |
| Spending shown positive | Debits and credits use different columns | Combine them into one signed column |
Debit and credit in two columns
Some exports have separate columns for money out and money in. To make one signed amount, put =IF(D2<>"",-D2,E2) in a new column, assuming debits are in D and credits in E. Fill it down and check a few rows against the statement.
Worked example in pounds
Say a week has these lines: Grocery £48.60, Fuel £55.00, Salary credit £1,850.00, Coffee £3.40, Grocery £22.15. Spending is 48.60 + 55.00 + 3.40 + 22.15 = £129.15. Money in is £1,850.00. Net change is 1,850.00 - 129.15 = £1,720.85. To total only groceries with descriptions in B2:B6 and signed amounts in F2:F6, use =SUMIFS($F$2:$F$6,$B$2:$B$6,"Grocery"), which returns -70.75 because 48.60 + 22.15 = 70.75 and debits are negative.
What should I check about my own bank's file?
- The date range really covers the period you need.
- The column names, since layouts differ between banks and over time.
- The closing balance, if the file shows one, against your own arithmetic.
- Whether pending items are included.
A note on tools
SpendixAI is built around India and INR, so it is meant for rupee accounts rather than a UK one. The opening, cleaning and checking steps above still apply to any CSV you work with in a spreadsheet.
If you work with several months, stack the files in one sheet rather than juggling them. Copy the rows from each file under the previous one, keep a single header row, then sort by date. Check the join between two files: the closing balance of the first should equal the opening balance of the second. If it does not, a day of transactions is missing, or the files overlap.
Add a Month column with =TEXT(A2,"yyyy-mm") so you can group by month in a pivot table or with SUMIFS. A pivot with Month down the side and Category across the top gives you a grid you can read at a glance, and it takes only a couple of minutes once the categories exist.
Keep a small notes tab in the workbook describing where the file came from: the date you downloaded it, the date range it covers, the account it belongs to, and any changes you made, such as removing a header block. Six months from now, that note answers the question of which file is which. It also helps if you hand the sheet to an accountant or a family member, because they can see what has been done to the raw data.
Closing checklist
- Opened through the import route, not double-click.
- Dates and long references checked.
- Signs made consistent in one amount column.
- Totals checked against known figures.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

