Lloyds Bank Statement to Excel: A UK Guide

TL;DR To get a Lloyds Bank statement into Excel, download the statement PDFs for the months you need, convert them so each transaction is one row, and fix the two things that cause most UK problems: day-first dates (DD/MM/YYYY) and separate "paid in" and "paid out" columns. Combine those into one signed amount, then prove that the opening balance plus all activity equals the closing balance. Use the online banking export for recent activity and the statements for anything official, such as your accountant, a mortgage, or a Self Assessment return.

Lloyds is one of the UK's largest banks, so plenty of people end up needing a Lloyds statement in a spreadsheet: sole traders getting ready for their tax return, landlords tracking rent, small companies handing records to an accountant, people applying for a mortgage or a visa, and households who just want to see where the money goes. The conversion itself isn't hard, but UK statements have a few quirks that catch out spreadsheets set up for US formats.

This guide covers where to get the data, what a typical statement looks like, how to convert it, how to fix UK-specific issues, and how to prove the result is complete. Online banking screens change, so we describe them in general terms rather than exact click-paths.

Export or statement?

There are usually two ways to get transactions out of online banking.

A transaction export downloads recent activity, commonly as a CSV file or in a format for accounting software. It's the quickest route if you only need the last few weeks or months. The limits: exports often cover a restricted date range, may handle pending items differently from the statement, and don't carry the official opening and closing balances for a statement period.

Statement PDFs are the bank's formal record. Each one covers a defined period, shows the balance brought forward and carried forward, and lists every transaction posted in between. Accountants, mortgage lenders, letting agents and visa caseworkers generally want statements, and statements usually go back further than exports.

If you need to reconcile, catch up on bookkeeping, or show the numbers to someone else, work from statements. Save each month with a predictable name, such as lloyds-current-2026-05.pdf. For older periods that online banking no longer shows, see our guide to getting old bank statements.

What a UK current account statement usually looks like

UK statements tend to use a running-ledger layout rather than the sectioned layout common in the US. Expect something like:

• Header: account name, sort code and account number, statement period.
• Summary: balance at the start of the period, total paid in, total paid out, balance at the end.
• Transaction list: date, a payment type code or description, money out, money in, and a running balance after each line or each day.

Payment type codes are common on UK statements: abbreviations for direct debits, standing orders, faster payments, debit card purchases, cash machine withdrawals, transfers and charges. The exact codes vary by bank and over time, and there's usually a key on the statement explaining them. Keep the code in its own column; it's very useful for categorizing later.

That running balance column is a gift. It means you can check your conversion line by line, not just at the end.

Converting a Lloyds statement

Copy and paste

Copying from a PDF viewer into Excel works for a short page. Expect columns to shift, wrapped descriptions to land on separate rows, and the paid in and paid out amounts to arrive as text. It's manageable for one page, tedious for a year.

Power Query

Excel's Power Query can pull tables from PDFs and often does well on statements with a clear grid. You'll still need to remove repeated header rows from each page, combine the money in and money out columns, and set dates to the right locale. Our Power Query bank statement guide covers the steps.

A statement converter

SheetStatement reads the PDF, keeps the payment type and description, combines paid in and paid out into a signed amount, and runs a balance check before export. Download Excel or CSV, or formats for Xero and QuickBooks. The free plan includes 3 pages a month. Starter ($9), Pro ($19) and Business ($39) monthly plans are for larger volumes.

Target layout:

The type codes in the example are illustrative; use the key on your own statement.

Fix UK dates first

The most common problem when a UK statement meets Excel is dates. A date like 03/05/2026 means 3 May in the UK and March 5 in the US. If your copy of Excel is set to a US locale, it will either misread the date or leave it as text when the day is above 12. The result is a column where some dates are wrong and some aren't dates at all, which is worse than either alone because it looks fine at a glance.

Options:

• Power Query: set the column type using "Using Locale" and choose English (United Kingdom).
• Text to Columns: select the column, choose Delimited, skip to step 3, choose Date with format DMY.
• A formula, if the dates are text in DD/MM/YYYY form:

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

Some statements print dates as "03 May 26" or omit the year on each line. In that case, take the year from the statement period. Statements that cross December and January need care: the January lines belong to the next year. Our guide to fixing bank statement dates in Excel has more formulas.

Check the result by sorting oldest to newest. The first and last dates should match the statement period exactly.

Combine paid in and paid out

Two amount columns make totals and imports awkward. Create one signed amount, money in positive and money out negative:

=N(E2) - N(D2)

where D is paid out and E is paid in. N() treats blank cells as zero. If the values are text with commas, convert them first with NUMBERVALUE, which also lets you set the decimal and thousands separators. Our guide to separating debit and credit columns covers this in both directions.

Prove the statement balances

With an Excel table named Tx, check the whole period:

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

That should return 0. Because UK statements print a running balance, you can also check every line. Add a column that recalculates the balance and compares it with the printed one:

=ROUND(G1 + F2 - [@Balance], 2)

Here G1 is the balance on the row above. For the first data row, compare with the opening balance instead. The first row with a non-zero result is where a transaction is missing, duplicated, or has the wrong sign. Statements that only print the balance at the end of each day need a small adjustment: compare on the last transaction of each date. Our guide to the running balance formula shows that version.

What the size of a whole-period difference usually means:

• Equals one transaction: a row is missing, often a line at the top or bottom of a page.
• Twice one transaction: a sign is wrong, usually from a paid in value landing in paid out.
• A round number: a misread digit, more likely on a scan.

The free balance checker is a quick way to confirm totals without formulas.

Using the data

For Self Assessment and your accountant

Sole traders and landlords often convert statements to prepare figures for their tax return or to hand to an accountant. A useful package:

• All statement PDFs for the tax year (6 April to 5 April for individuals), named consistently.
• One workbook with every transaction, a Category column, and a note on anything unusual.
• A summary by category, built with a pivot table or SUMIFS.

Note that the UK personal tax year doesn't line up with calendar months on statements, so you may need the statements either side of 5 April and filter by date. This is general information, not tax advice; your accountant or HMRC guidance will tell you what's needed. See our guide to bank statements for tax preparation for common requests and categorizing business expenses for a category setup.

For accounting software

Xero, QuickBooks Online and other packages accept CSV imports, and some also accept OFX. Xero's CSV layout is covered in our Xero CSV import format guide, and the QuickBooks route is covered in importing into QuickBooks Online. Make sure the import uses the UK date format. A mismatch is one of the most common causes of rejected files and of transactions landing in the wrong month. If Xero throws an error, see Xero bank statement import errors.

For mortgages, rentals and visas

Applications usually need the original PDFs, not a spreadsheet. The spreadsheet is still useful for your own preparation: filter for large deposits and regular outgoings so you can answer questions before they're asked. Our guides to bank statements for a mortgage application and bank statements for a visa application cover what's commonly reviewed.

A worked example

A self-employed graphic designer keeps one Lloyds current account for everything and wants to separate business income and costs before meeting her accountant.

1. Download. She saves the thirteen statements covering April to April, so she has the full tax year.
2. Convert. Each becomes one table with date, type code, description, paid out, paid in and balance.
3. Dates. Excel on her laptop is set to US format, so she converts the dates with locale set to English (United Kingdom) and confirms each table starts and ends on its statement dates.
4. Signed amount. She creates the Amount column from paid in minus paid out.
5. Balance check. Twelve statements balance. One shows a mismatch on the line-by-line check from 14 August onward; a card refund was read as paid out. Fixing the sign clears it.
6. Filter the tax year. She combines all tables and filters to 6 April through 5 April.
7. Categorize. Client payments, software subscriptions, equipment, phone, personal spending and transfers to savings each get a category. Personal items are marked so they're excluded.
8. Summary. A pivot table gives totals by category, and she adds notes on two large deposits: a client paying three invoices at once and a transfer from her own savings.

Her accountant gets the PDFs, the workbook and the notes, which means fewer questions and less time billed.

Organizing a full year of statements

Once you have more than a couple of months, a little structure saves a lot of scrolling.

One table, extra columns. Append every month into a single table and add a Statement column (for example 2026-05) and an Account column. You can always filter down to one month, but you can't easily summarize across twelve separate sheets. Our guide to combining multiple bank statements in Excel covers the mechanics, including Power Query's folder import.

Balance before you combine. Prove each statement on its own first. If you combine first and the year doesn't balance, you'll have twelve places to look instead of one.

Check the joins. The closing balance of one statement should equal the opening balance of the next. A gap means a missing statement, or a statement period you've downloaded twice. A quick formula on a summary sheet with one row per statement does it:

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

where B is the opening balance and C the closing balance of each statement.

Flag transfers. If you also hold a Lloyds savings account or a credit card, money moving between them shows up on both statements. Mark both sides as transfers so they don't inflate income or spending in your summaries.

Credit card statements

Credit card statements work the other way round from current accounts: purchases increase what you owe and payments reduce it. When you convert them, decide on one convention and stick to it. For a spending analysis, it's usually simplest to show purchases as negative and payments and refunds as positive, the same as your current account, so the two can sit in one table. The balance check then compares the previous balance, the activity and the new balance, with the signs flipped. Our credit card statement converter page explains how SheetStatement handles card statements.

Troubleshooting

Some dates are right-aligned, some left-aligned. The left-aligned ones are text. That's the locale problem; fix the whole column, not just the visible errors.

£ signs or commas in amounts. Remove them, or use NUMBERVALUE(A2, ".", ",") to convert. See cleaning bank statement data.

Header rows repeated through the table. Each page of the PDF repeats the column headings. Filter them out before totalling.

"Balance brought forward" rows. Some statements include a line for the balance carried over from the previous page or period. It isn't a transaction; remove it or exclude it from totals.

Transactions in the wrong month after import. The software read DD/MM as MM/DD. Re-import with the correct date format.

Duplicates. Overlapping exports and statements, or importing the same file twice. See our guide to duplicate transactions after import.

Other UK banks

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

Convert a Lloyds statement

Upload a Lloyds Bank statement PDF to SheetStatement, check the rows and the balance check, and download Excel, CSV, Xero or QuickBooks formats. The free plan covers 3 pages a month, enough to try it on a real statement.

FAQ

Can I export Lloyds transactions as CSV directly?

Online banking typically offers an export of recent transactions. That's convenient for recent activity. For full statement periods, older months, or official opening and closing balances, convert the statement PDFs.

Why are my dates wrong in Excel?

UK statements use day-first dates. If Excel is set to a US locale, it misreads or ignores them. Convert using the English (United Kingdom) locale, or rebuild them with DATE, LEFT, MID and RIGHT.

How do I combine paid in and paid out?

Create a new column equal to paid in minus paid out, using N() so blanks count as zero. Money in becomes positive and money out negative.

How do I know nothing is missing?

Check that the opening balance plus the sum of all amounts equals the closing balance. Because UK statements print running balances, you can also check each line to find exactly where an error starts.

Can I use the spreadsheet for Self Assessment?

It's a useful working file for preparing figures, and many accountants are happy to receive one alongside the PDFs. This is general information only; check with your accountant or HMRC guidance on what to keep and submit.

Does this work for business accounts?

Yes. Business statements follow a similar layout. Import into Xero or QuickBooks one month at a time and reconcile each to the closing balance.

Is it safe to upload statements?

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

How much does it cost?

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