NatWest Bank Statement to Excel: Step-by-Step Guide

TL;DR To get a NatWest statement into Excel, save the statement PDFs for the periods you need, convert them so every transaction sits on one row, then fix the two things that trip up most UK conversions: day-first dates and separate money in and money out columns. Combine those into one signed Amount column, check that the opening balance plus all activity equals the closing balance, and only then start categorizing or importing. Use the online banking download for quick recent checks, and the statement PDFs for anything you need to hand to an accountant, a lender or HMRC.

NatWest is part of NatWest Group, alongside Royal Bank of Scotland and Ulster Bank, and a lot of UK households and small businesses bank there. That means a lot of people end up with the same job: taking a pile of NatWest statements and turning them into a spreadsheet they can actually work with. Sole traders want figures for Self Assessment. Limited company directors want to hand clean records to their accountant. Landlords want rent and repairs in one place. Households want to see where the money went last year.

This guide walks through the whole process: choosing between an export and a statement, what a typical statement looks like, three ways to convert it, the UK-specific clean-up steps, how to prove the result is complete, and what to do with the data once it's in Excel. Online banking screens change from time to time, so we describe them in general terms instead of giving exact click-paths that might be out of date by the time you read this.

Download or statement: which one do you need?

Most online banking services give you two different ways to get transactions out.

A transaction download gives you recent activity as a file, often CSV or a format meant for accounting software. It's quick, and for a "what did I spend last month" question it's usually enough. The catch is that downloads are typically limited to a recent window, may treat pending items differently from the statement, and don't carry the official balance brought forward and carried forward for a statement period.

Statement PDFs are the bank's formal record. Each one covers a defined period, shows the starting and ending balances, and lists every transaction that posted in between. When an accountant, mortgage broker, letting agent or visa caseworker asks for "bank statements", this is what they mean. Statements also tend to go back further than downloads.

A simple rule: if you need to reconcile, catch up on bookkeeping, or show your figures to someone else, work from statements. If you just want a quick look at recent spending, the download is fine.

Whatever you use, save each file with a predictable name, such as natwest-current-2026-04.pdf or natwest-business-2026-04.pdf. It sounds fussy, but when you have twenty-four PDFs across two accounts, sorted file names are what stop you from missing a month. If you need statements older than online banking shows, our guide to getting old bank statements covers the usual options.

What a NatWest statement usually looks like

UK current account statements generally follow a running-ledger layout. Details vary between personal and business accounts and change over time, but expect something along these lines:

• Header: account name, sort code, account number and the statement period or statement number.
• Summary: the balance at the start of the period, totals paid in and paid out, and the balance at the end.
• Transaction list: date, a description that often includes a payment type, money out, money in, and a balance column.

A few things are worth knowing before you convert.

The balance column may not be on every line. Some UK statements print the balance after every transaction; others print it only after the last transaction of each day. Either way, it's useful for checking your conversion, but the formula you use differs slightly. We cover both below.

Descriptions can wrap. Long merchant names, references on faster payments, or standing order details sometimes spill onto a second line in the PDF. A good conversion keeps them on the same row as the amount.

Payment types are abbreviated. Direct debits, standing orders, card payments, faster payments, transfers, cash withdrawals and charges are usually shown with short codes or words. There's normally a key on the statement. Keep the type in its own column if you can: it makes categorizing much faster.

Brought forward lines. Statements often start with a "balance brought forward" line and may repeat a similar line at the top of each page. These aren't transactions and should be excluded from totals.

Three ways to convert a NatWest statement

Copy and paste

For a single short page, you can select the text in a PDF viewer and paste it into Excel. In practice, expect columns to shift, wrapped descriptions to land on their own rows, and amounts to arrive as text. It's workable for one page and painful for a year's worth.

If you go this route, paste into a blank sheet first, then use Text to Columns or the TEXTSPLIT function in recent versions of Excel to separate the fields. Check every page; a single shifted column can silently swap money in and money out.

Power Query

Excel's Power Query on Windows can read tables from a PDF. On statements with a clear grid, it often does a decent job. You'll still need to remove repeated header rows, filter out brought forward lines, combine the money columns and set the date type with the UK locale. Our Power Query bank statement guide walks through the steps.

A statement converter

SheetStatement reads the PDF, keeps dates, payment types and descriptions on one row, produces a signed amount, and runs a balance check against the opening and closing balances before you export. You can download Excel or CSV, or files formatted for Xero and QuickBooks. The free plan includes 3 pages a month, which is enough to test a real statement. Starter ($9), Pro ($19) and Business ($39) monthly plans cover larger volumes.

Whichever method you choose, aim for this target layout:

The type codes and figures are illustrative. Use the key on your own statement.

Step 1: fix UK dates

This is where most UK conversions go wrong. A date such as 04/07/2026 means 4 July in the UK and April 7 in the US. If Excel is set to a US locale, it will misread any date where the day is 12 or below and leave the rest as text. You end up with a column that looks plausible but is partly wrong, which is the worst kind of error.

Ways to fix it:

• Power Query: right-click the date column, choose Change Type, then Using Locale, and pick Date with English (United Kingdom).
• Text to Columns: select the column, choose Delimited, click through to step 3, choose Date and set it to DMY.
• A formula, if the dates arrived as text in DD/MM/YYYY form:

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

If the statement prints short dates such as "04 Jul" without a year, take the year from the statement period. Watch statements that run across December and January, because the January lines belong to the following year. Our guide to fixing bank statement dates in Excel has formulas for the awkward cases.

A quick test: sort the table from oldest to newest. The first and last dates should sit inside the statement period. If you see a date in the future or months outside the period, the locale is still wrong.

Step 2: combine money in and money out

Two amount columns make totals, pivot tables and imports harder than they need to be. Create a single signed Amount column where money in is positive and money out is negative:

=N(E2) - N(D2)

Here D is money out and E is money in. N() turns blank cells into zero, so you don't get errors on rows with only one value.

If the amounts came through as text, perhaps with commas or a £ sign, convert them first:

=NUMBERVALUE(SUBSTITUTE(D2,"£",""), ".", ",")

Our guide to separating debit and credit columns covers this in both directions, which helps if a piece of software wants separate columns instead of one signed amount.

Step 3: prove the statement balances

Before you categorize anything, prove the conversion is complete. Turn the data into an Excel table (Ctrl+T) and name it Tx. Then:

=ROUND(OpeningBalance + SUM(Tx[Amount]) - ClosingBalance, 2)

That should return exactly 0. If it doesn't, the size of the difference is a clue:

• Equal to one transaction: a row is missing, often the first or last line on a page.
• Twice one transaction: a sign is wrong. Something that was money in landed in money out, or the reverse.
• A round number like 100 or 1,000: a digit was misread, more likely on a scanned statement.
• A tiny amount: rounding or a stray decimal place.

Line-by-line checks using the balance column

Because UK statements print running balances, you can go further and find exactly where an error starts. Add a column that recalculates the balance from the row above and compares it with the printed balance:

=IF([@Balance]="", "", ROUND(PrevBalance + [@Amount] - [@Balance], 2))

The first non-zero result marks the row where something went wrong. If the statement only prints a balance at the end of each day, compare on the last row for each date instead. Our running balance formula guide shows both versions, and the free balance checker is a quick way to confirm totals without building formulas.

Step 4: put twelve months in one table

Once you have more than a couple of months, keep everything in one table rather than one sheet per month. Add two columns: Statement (for example 2026-04) and Account (for example Current or Business). You can always filter down to a month, but you can't easily summarize across twelve separate sheets.

Two rules make this reliable:

1. Balance each statement before combining. If you combine first and the year is out, you have twelve places to look.
2. Check the joins. Each statement's closing balance should equal the next one's opening balance. A summary sheet with one row per statement and a simple formula does it:

=IF(ROUND(B3 - C2, 2) = 0, "OK", "Gap")

where B is the opening balance and C is the closing balance. A gap usually means a missing statement or one you downloaded twice.

Our guide to combining multiple bank statements in Excel covers Power Query's folder import if you want to automate this.

A worked example: a sole trader's tax year

A self-employed electrician uses one NatWest current account for business and personal spending. His accountant has asked for a breakdown of business income and expenses for the last tax year.

1. Collect. He downloads the statements covering March through April of the following year, so he definitely has every day from 6 April to 5 April.
2. Convert. Each statement becomes a table with date, type, description, money out, money in and balance.
3. Fix dates. His laptop's Excel is set to a US locale, so he converts dates using the English (United Kingdom) locale and checks each table starts and ends on its statement dates.
4. Signed amounts. He adds the Amount column.
5. Balance. Eleven statements balance first time. One is out by £84.00. The line-by-line check shows the error starts on a row where a supplier refund was read as money out. Flipping the sign clears it.
6. Combine and filter. He appends all the tables and filters to 6 April to 5 April.
7. Categorize. Customer payments, materials, van costs, fuel, phone, insurance, personal and transfers each get a category. A short list of keywords in a lookup table handles most of them automatically; see our guide to Excel formulas for categorizing transactions.
8. Summarize. A pivot table gives totals by category. He writes short notes beside a few large deposits, such as a transfer from his savings account, which isn't income.

His accountant receives the original PDFs, the workbook and the notes. That's general bookkeeping practice rather than tax advice; what you need to keep and report depends on your situation, so follow your accountant's or HMRC's guidance.

Using NatWest data in accounting software

Xero

Xero accepts CSV statement imports, and many UK small businesses use it. Make sure the date format in the file matches what you choose during import. Our Xero CSV import format guide shows the column layout, and Xero bank statement import errors covers the usual rejections.

QuickBooks Online

QuickBooks Online also accepts CSV uploads for bank accounts. You can map either a single amount column or separate money in and money out columns. See importing a bank statement into QuickBooks Online and, if the upload fails, QuickBooks Online CSV import errors.

FreeAgent and others

FreeAgent, Sage and other packages accept statement files in their own formats. The principles are the same: one row per transaction, consistent dates, and a signed amount or clearly labelled money columns. Import one month at a time and reconcile each month to the statement's closing balance before moving on.

Business account statements

NatWest business statements follow a similar structure to personal ones, but a few things come up more often:

• Bank charges may appear as a single monthly line. Categorize them as bank fees rather than leaving them uncategorized.
• Card settlement deposits from a card terminal provider often arrive as one lump sum per day, net of fees. If you need gross sales, you'll need the provider's reports as well; the bank statement only shows what landed.
• Transfers between accounts appear on both statements. Mark both sides as transfers so they don't inflate income or costs.
• Loan repayments should be split into interest and capital in your books. The statement just shows the total payment.

For a repeatable month-end routine, our month-end close checklist is a good starting point.

Credit card statements

NatWest credit card statements work the other way around from a current account. Purchases increase what you owe and payments reduce it. When converting, pick one sign convention and stick to it. For spending analysis, it's usually easiest to show purchases as negative and payments and refunds as positive, matching your current account, so both can live in one table.

The balance check then becomes: previous balance, plus purchases and charges, minus payments and credits, equals the new balance. Our credit card statement converter page explains how SheetStatement handles card statements, and our guide to credit card reconciliation in Excel has a template.

Troubleshooting

Some dates are left-aligned and some right-aligned. The left-aligned ones are text. Fix the whole column using the UK locale rather than correcting individual cells.

Amounts won't sum. They're text. Remove £ signs and commas, or use NUMBERVALUE. Our guide to cleaning bank statement data covers the common culprits, including non-breaking spaces.

Header rows in the middle of the data. Each PDF page repeats the column headings. Filter them out before totalling.

The statement balances but the year doesn't. A statement is missing or duplicated. Use the joins check above.

Transactions land in the wrong month after import. The software read DD/MM as MM/DD. Re-import with the right date format, and delete the first import to avoid duplicates. See duplicate transactions after import.

A scanned statement gives odd numbers. Scans are harder to read than digital PDFs. Check totals carefully; our guide to OCR accuracy on scanned statements explains what to look for.

Other UK banks

The same workflow applies to other UK banks. Layouts differ, but day-first dates, separate money columns and running balances are common across them. See our guides for Lloyds statements to Excel, Barclays statements to CSV and HSBC statements to Excel.

Convert a NatWest statement now

Upload a NatWest statement PDF to SheetStatement, review the rows and the balance check, and download Excel, CSV, Xero or QuickBooks files. The free plan includes 3 pages a month, so you can test it on a real statement before deciding on a plan.

FAQ

Can I download NatWest transactions as a CSV?

Online banking usually offers a download of recent transactions. That's handy for recent activity. For full statement periods, older months, or official opening and closing balances, convert the statement PDFs instead.

Why does Excel show the wrong dates?

NatWest statements use UK day-first dates. If Excel uses a US locale, it swaps day and month or leaves dates as text. Convert the column using the English (United Kingdom) locale, or rebuild dates with DATE, LEFT, MID and RIGHT.

How do I combine money in and money out?

Add a column equal to money in minus money out, wrapping each in N() so blanks count as zero. Money in becomes positive and money out negative.

How can I be sure nothing is missing?

Check that the opening balance plus the sum of all amounts equals the closing balance. Then use the printed balance column to check each line, which shows exactly where a mistake starts.

Does this work for Royal Bank of Scotland or Ulster Bank statements?

The same approach works for any statement with dates, descriptions, amounts and balances. Layouts differ between banks, so check the column order and the balance check on each new layout.

Can I use the spreadsheet for my tax return?

It's a useful working file for preparing figures, and many accountants are happy to receive one alongside the original PDFs. This is general information, not tax advice; check with your accountant or HMRC guidance.

Is it safe to upload bank statements?

See the security page for how SheetStatement processes and stores files, and read any service's policy before uploading financial documents.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39 for higher volumes.