Bank CSV with separate Paid in and Paid out columns: one signed amount
Your bank puts money in and money out in two columns, so there is no single Amount to total, chart or upload. Accounting packages that take one Amount column want money in as positive and money out as negative.
The problem
With two columns, a running total needs a formula that subtracts one from the other, a filter by direction needs two filters, and a 3-column bank import wants one signed Amount. The same file can also have a row with a value in both columns, which cannot be turned into a single sign without a decision from you.
Check by hand
Add a column with =IF(D2="",0,D2)-IF(E2="",0,E2), adjusted for which column is in and which is out, fill it down, and paste the values over the top. Scan for any row where both columns have a number before you trust the result.
What the add-on does
It flags a paid in and paid out pair (BK-10). The fix writes a new Signed amount column, in positive and out negative, and leaves your original columns untouched. A guess never overwrites its source. If any row has a value on both sides, the fix is refused and the finding names the problem instead.
This fix rests on the bank's layout, so it is only offered for a bank whose layout the add-on has confirmed. Where a layout has not been confirmed against a real export, the finding explains what it found and leaves your cells alone, and the guide says what is covered now. Applying the fix is a Pro feature. The scan, the explanation and Undo are free.
If your file has one unsigned amount and a debit/credit indicator, or reversed card signs, that is explained (BK-11) but not changed.
Check the file before you trust it
UK Bank CSV Cleaner scans a UK bank CSV, or a sheet you are about to upload, and explains every finding. The full scan, every explanation and Undo are free, and your data stays in your Google™ account. It edits the spreadsheet only and never connects to your bank or to an accounting package.
About UK Bank CSV Cleaner — not yet on the Google Workspace™ Marketplace.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. 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 Google.