Bank statements

NatWest statement to CSV: open it in Excel or Sheets without scrambled data

Got a NatWest statement as CSV or need one? Open it cleanly in Excel or Google Sheets, fix dates and signs, and total spending by category in pounds.

Editorial illustration for NatWest statement to CSV: open it in Excel or Sheets without scrambled data

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

  1. In Excel, use Data, then From Text or CSV, and pick the file.
  2. Check the preview: columns should be split, with a header row.
  3. Set the date column to the format the file uses, and the description column to text.
  4. In Google Sheets, use File, Import, Upload, and choose to replace the sheet.
  5. Save a copy as a normal workbook before editing.

Common problems and fixes

ProblemWhyFix
Everything in one columnSeparator is not a commaChoose the correct delimiter in the import dialog
Dates swappedDay and month read the wrong way roundSet the date format when importing
Reference shows 1.23E+11Long number treated as a figureImport that column as text
Spending shown positiveDebits and credits use different columnsCombine 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.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports