How to clean a UK bank CSV and lay it out for import in Excel™

A step-by-step run through the add-in, from install to a converted sheet. Scanning, every explanation, the ambiguity report, the space fix and Undo are free and unlimited. The other fixes, saved settings and the exportable report are paid, and each is marked Pro where it appears.

What the problem is

A bank export arrives as text: dates Excel cannot sort, amounts with £ signs and commas, paid in and paid out in separate columns, a date and time in one cell, a total row nobody asked for. And a file built for upload fails in Xero™, QuickBooks™ or Sage™ for a heading it does not recognise, a date that reads two ways, or a zero in the wrong place.

This add-in reads the bank export, explains every finding in plain English — what is wrong, and why it matters to the import — and can lay the result out as a new sheet in the package's layout. It edits the workbook only and never connects to a bank or an accounting package.

Packages it lays a file out for

• Xero: the bank statement import layout.
• QuickBooks Online (UK): the 3-column form (Date, Description, Amount) and the 4-column form (Date, Description, Credit, Debit).
• Sage Accounting: the 3-column and 4-column bank import templates.

Before you start

• Excel for Windows, Excel for Mac or Excel on the web. The add-in is not offered on iPad or iPhone.
• The add-in installed — see Step 1.
• A workbook with at least one sheet of bank data on it. Import tip: bring a bank CSV in with Data › From Text/CSV, then Transform Data, and set the date and amount columns to Text before loading. Opening the CSV straight into Excel parses dates using your computer’s regional settings — a US-locale machine silently turns a UK 06/03/2026 into 3 June — and turns text amounts into numbers before the scan can see what was there.
• Nothing leaves your computer. The rules run in the task pane, on your machine, against the workbook. The add-in’s only network traffic is to the licence server, and only ever a licence key, an anonymous install id and which host this is. The privacy notice says so in full.

Step 1 — Install

Not yet on Microsoft AppSource: the listing is in preparation. When it is listed, install it from AppSource, or Home › Add-ins › Get Add-ins in Excel and search for “UK Bank CSV Cleaner”.

Step 2 — Open the task pane

Choose UK Bank CSV Cleaner on the Home tab. A task pane opens on the right.

Step 3 — Pick a direction and scan

Press Clean a bank CSV or Check before uploading. The direction is asked rather than guessed. To clean, choose a package under Lay it out for, or Just clean it, no conversion. To check, pick the package under Which package is this file for? or let it detect the package from the headings; the QuickBooks and Sage 3-column files share headings, so it asks which. The task pane also says which bank it thinks the file came from — the file is never rewritten on a guess about the bank.

A sheet with nothing wrong says so, rather than showing an empty list.

Step 4 — Read the findings

Every finding names its rule code, what it found, and why it matters. The codes BK-01 to BK-20 are listed on the home page; the ones people meet first are:

Two notes are help text rather than findings: an unsigned amount with a debit/credit indicator (BK-11) and the report's formula-injection prefix (BK-18).

Step 5 — Convert to a new sheet

Create the new sheet on the BK-19 finding builds the package's layout in a new sheet. The preview lists the exact rows and any warnings first. Your original sheet is never written: an unreadable date is left blank in the new sheet and counted, never guessed.

Step 6 — Fix it Pro

Each fix shows a preview of the exact cell changes before anything is applied. The preview is built from the same plan the button executes, never from a second description of it.

• One-click fixes are safe unattended. The space fix (BK-20) is free.
• Guided fixes — dates, combining paid in and paid out — ask you to confirm before the write, with the ambiguity report open above the button for dates.

The remaining findings are explained but not applied. A value the parser cannot read is never rewritten, and a guess never overwrites its source — a combined signed amount is written into a new column, leaving the originals alone.

Step 7 — Undo, or keep the changes

Every fix first saves a restore point before it changes anything. Undo in the task pane puts the sheet back — free in every tier, always. After a conversion, Undo deletes the new sheet. The restore point is kept with the workbook for as long as it is small enough to store, and for the rest of the session otherwise.

Step 8 — The exported report Pro

Export report saves the full finding list as a CSV or a Markdown summary, a file on your machine that stays readable without the add-in.

Step 9 — Entering a licence key

Expand Enter a licence key at the bottom of the task pane, paste the key from the end of checkout, and press Activate. One key has 3 activations and takes effect immediately. Checkout is not open yet; see Prices.

Limits

• Not on iPad or iPhone. The add-in runs in Excel for Windows, Excel for Mac and Excel on the web.
• Sheet size. A sheet is read in bands, so a large export is fine. A used range over 4.9 million cells is declined with a message naming the size, before anything is read. On Excel on the web a very large write may be split into smaller ones.
• Bank layouts. No bank's export layout has yet been confirmed against a real file from that bank, so today every rule that depends on a bank's layout explains and warns but never rewrites your cells, and the conversion to a new sheet is held back with a message saying why. The fixes that act on what a cell itself says (text dates and amounts, whole-pound amounts, stray spaces) are not held back. Layouts switch on one bank at a time as real exports are confirmed.
• Package rules change. The package checks follow the published UK help pages of the packages as at the build date. Several behaviours could not yet be confirmed, so those rules explain and warn but never rewrite your cells. Banks also change their exports without notice.

FAQ

Does my data leave my computer? No. The rules run in the task pane, on your machine. The only network call this add-in makes is to the licence server, for a licence key, an anonymous install id and which host this is.

How should I bring a bank CSV into Excel? Data › From Text/CSV, then Transform Data, with the date and amount columns set to Text.

Does the same licence key work in Google Sheets? Yes. UK Bank CSV Cleaner is also being prepared as a Google Sheets add-on, and one key will work in both.

Is this affiliated with any bank or accounting package? No. It is an independent add-in by AppGarden. The bank and package names are trademarks of their owners and are used only to describe compatible file formats.

Also for Google Sheets™: UK Bank CSV Cleaner for Google Sheets. The same licence key works in Excel™ and Google Sheets.

Support

Email support@appgarden.co.uk, and say which finding looked wrong. A workbook that produced a wrong finding — or missed a real one — is the most useful thing you can send. Remove names and account numbers first, only send one you are content to share, and read the privacy notice.

Microsoft and Excel are trademarks of the Microsoft group of companies. Not affiliated with, endorsed by or sponsored by Barclays, HSBC, Lloyds Banking Group, Halifax, Bank of Scotland, Starling Bank, Monzo, Xero Limited, Intuit Inc. or The Sage Group plc. Barclays, HSBC, Lloyds, Halifax, Bank of Scotland, Starling, Monzo, Xero, QuickBooks and Sage are trademarks of their respective owners and are used only to describe compatible file formats. UK Bank CSV Cleaner is not affiliated with or endorsed by Microsoft.