Continue to WhatsApp?

The chat opens in WhatsApp, in the app or in a new tab. You leave our website, and data such as your IP address is sent to WhatsApp (Meta).

DATA AND IMPORTS

Convert a bank statement to Excel: import it cleanly, check the balance, analyse it

The month is closed and accounts are waiting for the bank transactions. You download the export from online banking, double click it, and Excel shows “Überweisung Müller GmbH”. Five lines of account number and period sit above the table, and you are not sure Excel has taken every amount as a number.

Desk with printed lists of figures, a binder, glasses and a calculator, as in the monthly reconciliation of bank transactions
Photo: Cht Gsml on Unsplash

Tidying it up costs time every month. A bigger cost is an amount that quietly sits in the column as text: Excel leaves it out of every total without an error message, and the match against open invoices no longer adds up. Reaching for a free online converter instead means uploading the names and IBANs of customers and suppliers to an unknown provider.

This guide shows how to convert a bank statement to Excel from CSV or PDF, how a balance check proves that no amount is missing, and how to analyse the transactions afterwards.

Quick answer: how to convert a bank statement to Excel

The safe route is your bank's CSV export, imported with Data > From Text/CSV instead of a double click. There you set the character set, delimiter and number format before Excel reinterprets anything. A balance check at the end proves that every transaction arrived as a number.

  1. Export as CSV

    Download the transactions from online banking as a CSV file. A CSV file is plain text with one transaction per line and can be read without going through a page layout.

  2. Import through the Data tab

    Open the file with Data > From Text/CSV. The preview shows how Excel reads the file before anything lands in the sheet.

  3. Check character set and delimiter

    If special characters are garbled, set File Origin to 65001: Unicode (UTF-8). As delimiter, choose the character that splits the columns correctly in the preview.

  4. Make sure amounts are numbers

    Convert amount and date with the right locale, then use COUNTA and COUNT to check that every amount is a number.

  5. Run a balance check

    Opening balance plus the sum of all transactions must equal the closing balance on the statement. Only then does the table go to accounts.

Why a bank statement looks broken in Excel

Your bank's data is correct. The statement only breaks when it is opened, because a CSV file has no fixed rules for character set, delimiter and number format, and Excel guesses on a double click. You can check these causes in your own file:

  • Character set: If the file is saved as UTF-8 without the invisible marker at the start (BOM), Excel reads it in the old Windows character set on a double click. ü becomes ü and Ü becomes Ü.
  • Delimiter: Excel set to German or Austrian expects a semicolon in CSV files, because the comma is the decimal sign. Excel set to English expects a comma. If the file uses the other one, everything lands in column A.
  • Number format: If the export uses a point as the decimal sign, such as 12.05, Excel set to German turns it into a date, 12 May. The other way round, 1.234,56 stays text in Excel set to English.
  • Sign: Depending on the export, the minus sits after the amount, such as 1.250,00-, or debit and credit are in a separate column. Excel then lacks the sign it needs to calculate.
  • Header block: If account number, period or balance sit above the actual table, Excel takes the first of these lines as the header.
  • Line breaks: A payment reference that contains a line break can split one transaction across two rows.

Import a bank statement into Excel: step by step with Power Query

A double click makes Excel guess. Importing through Power Query, the tool behind Data > From Text/CSV, shows you every decision and remembers your settings for next month. Here is how:

  1. Choose the file

    Click Data > From Text/CSV, select the export and click Import.

  2. Set the file origin

    Check the special characters in the preview. If they are wrong, choose 65001: Unicode (UTF-8) under File Origin. According to Microsoft, Power Query only detects UTF-8 by itself when the file starts with a BOM.

  3. Choose the delimiter

    Under Delimiter, choose the character that puts date, text and amount in separate columns, then click Transform Data.

  4. Remove the header block

    If a header block sits above the table, choose Home > Remove Rows > Remove Top Rows and enter the number of those lines. Then click Use First Row as Headers.

  5. Convert amount and date

    Right click the Amount column header, choose Change Type > Using Locale, then Decimal Number and German (Austria) or German (Germany) if the export uses a comma as the decimal sign. For a point, choose English (United States). Convert the date the same way.

  6. Load

    Click Close & Load. The transactions appear as a table on a new sheet.

If Power Query cannot convert an amount, the cell shows an error instead of a wrong value. That is intended: Home > Keep Rows > Keep Errors shows you exactly those rows. If a transaction is split across two rows, open the settings of the Source step under Applied Steps and set Line breaks to ignore quoted line breaks.

Next month you skip all of this: save the new export under the same name in the same place and click Data > Refresh All or press Ctrl+Alt+F5. Power Query repeats the import with your settings.

Quick fix when everything is in column A

For a one off look, the Text to Columns wizard is often enough. Select column A, choose Data > Text to Columns > Delimited, tick the file's delimiter and click Finish. The wizard does not repair garbled characters, and Excel interprets the amounts by its own settings. For the monthly reconciliation, Power Query is the safer route.

Balance check: make sure no amount is missing

The most dangerous problem is invisible. According to Microsoft, SUM ignores text values and adds only the numbers. If a single amount sits in a cell as text, it is missing from every total, and Excel says nothing. Two checks rule this out, in the example with the amounts in D2 to D413:

  1. Number check: =COUNTA(D2:D413)-COUNT(D2:D413) counts the filled cells that are not numbers. COUNTA counts every cell that is not empty, COUNT only numbers. The result must be 0.
  2. Balance check: The opening balance on the statement plus =SUM(D2:D413) must equal the closing balance on the statement. Any difference is a missing, duplicated or wrongly imported amount.

Convert amounts stored as text with NUMBERVALUE: =NUMBERVALUE(D2,",",".") reads the comma as the decimal sign and the point as the thousands separator, whatever your Excel settings are. If the minus sits after the amount, this formula checks the last character first: =IF(RIGHT(D2,1)="-",-NUMBERVALUE(LEFT(D2,LEN(D2)-1),",","."),NUMBERVALUE(D2,",",".")). That turns 1.250,00- into -1250. If the export keeps debit and credit in a separate column, say D and C in column E, set the sign with =IF(E2="D",-D2,D2).

Person at a desk typing on a keyboard next to a printed report on a clipboard, as when checking the balance against the total
Photo: Vitaly Gariev on Unsplash

Convert a PDF bank statement to Excel

Sometimes there is only the PDF, for older months or for an account you do not run yourself. First check in online banking whether the transactions for the same period can be downloaded as CSV. That is the cleaner source, because the values do not have to be read from a page layout.

If not, Excel for Microsoft 365 on Windows reads tables from PDF files: Data > Get Data > From File > From PDF. In the Navigator, choose the detected table and then Transform Data. By default, Power Query combines similar tables on consecutive pages into one table. Then remove in between rows such as page headers or carried forward totals, convert the data types as described above and run the balance check. A scanned statement without text recognition contains only an image, and Excel cannot read a table from it.

Free online converters upload the file to an unknown provider's server. A business account shows names and IBANs of customers, suppliers and staff, which is personal data. If a provider processes such data on your behalf, Article 28 of the GDPR requires a data processing agreement. Settle that before you upload a statement.

Example: a wholesaler's monthly statement

Balance check in the example, amounts in euros
CheckFormulaResult
Opening balance on the statement—18,420.35
Sum of transactions=SUM(D2:D413)8,655.68
Calculated closing balanceopening balance plus sum27,076.03
Closing balance on the statement—25,826.03
Differencecalculated minus statement1,250.00
Number check=COUNTA(D2:D413)-COUNT(D2:D413)1
Sum after the correction=SUM(D2:D413)7,405.68
Calculated closing balance after the correctionopening balance plus sum25,826.03, difference 0.00

Analyse and categorise bank transactions in Excel

Once the statement is clean in Excel, you usually want to know where the money went: rent, suppliers, salaries, customer payments. Assigning every row by hand takes time. A small keyword table that you set up once and reuse every month is faster.

  1. Set up a keyword table

    Put keywords that appear in the payment reference in F2 to F6, such as rent, salary or a supplier's name, and the matching category in G2 to G6. Fill every row, because an empty row can match any text.

  2. Find the category with a formula

    With the payment reference in column C, enter in E2: =XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$6,C2)),$G$2:$G$6,"Open"). SEARCH finds the keyword anywhere in the text and ignores upper and lower case. XLOOKUP returns the category of the first matching keyword, and Open where nothing matches.

  3. Review open rows

    Filter for Open and assign those transactions by hand. If a payee shows up every month, add it to the keyword table.

  4. Build totals

    The total per category comes from =SUMIF($E$2:$E$413,"Rent",$D$2:$D$413). For an overview of all categories and months, use a PivotTable.

XLOOKUP is available in Microsoft 365 and in Excel 2021 or newer. Because the first matching keyword wins, put specific keywords at the top and general ones at the bottom. The category is a suggestion for your analysis and does not replace the posting in your accounting software.

Common mistakes with bank statements in Excel

  • Overwriting the export: Keep the bank's original file unchanged and save your work in a separate Excel file. That way you can import again at any time.
  • Replacing special characters by hand: It takes time and always misses one. Set the character set correctly on import or repair the file first.
  • Trusting the total blindly: Without the number check you will not notice that an amount is stored as text and missing from the total.
  • Amounts turning into dates: An export with a point as decimal sign, double clicked in Excel set to German, turns 12.05 into 12 May. Import with a locale instead.
  • Forcing categories: A formula without an Open result pushes unclear transactions into some category. Keep unclear cases visible.
  • Uploading statements to online converters: Without a data processing agreement you pass personal data to a third party.

Effort, limits and the route that lasts

Once set up, the Power Query import does most of the work with one click on Refresh All. Work remains, though: fetching exports, checking categories, the balance check and the handover to accounts, for every account. A conservative estimate: 2 hours a month are 24 hours a year. At an assumed internal cost of €38 an hour, wages plus overheads as preset in the cost calculator, that is €912 a year for one account. Run your own figures in the office work cost calculator.

First ask whether your accounting software can read the bank file directly. Then the posting needs no detour through Excel, and Excel stays for analysis and reconciliation. If the file has to be in a particular form for that, Repair CSV brings character set, delimiter and decimal sign into shape. How to move data cleanly from one program to the next in general is covered in transfer data between programs.

An office helper, a small program we set up for exactly this task, fetches the export, cleans it, assigns transactions by your rules, runs the balance check and puts unclear transactions in front of your team, flagged. The output is an import file for BMD or DATEV or your analysis in Excel. You keep your programs and approve before anything moves on. More on the automate data transfer page.

If several bank accounts pass through Excel every month, tell us in the free workflow check which banks, which programs and which analysis you need. You get a personal, free assessment within 1–2 working days. We deliver small projects in 3–7 days once the workflow is agreed, at a fixed price from €1,990 excl. VAT.

Sources and further reading

FAQ

Quick answers.

How do I convert a PDF bank statement to Excel?

In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From PDF. In the Navigator, choose the detected table, remove page headers and carried forward totals and convert the amounts to numbers. The CSV export from online banking for the same period is usually the cleaner source. Excel cannot read scanned statements without text recognition.

Why does my bank statement CSV open in one column?

Because the file uses a different delimiter from the one your Excel expects. Excel set to German or Austrian expects a semicolon in CSV files, Excel set to English a comma. With the wrong one, everything lands in column A. Import through Data > From Text/CSV and choose the delimiter yourself.

Why are special characters broken in my bank statement?

Usually the file is saved as UTF-8 but without the marker at the start that Excel looks for. Excel then reads it in the old Windows character set, and ü becomes ü. When importing through Data > From Text/CSV, set File Origin to 65001: Unicode (UTF-8), or repair the file with a tool first.

How do I categorise bank transactions in Excel?

With a keyword table and a formula. Keywords go in F2 to F6, categories in G2 to G6, the payment reference in column C. Then =XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$6,C2)),$G$2:$G$6,"Open") returns the category or Open, available in Microsoft 365 and Excel 2021 or newer. Assign transactions marked Open by hand.

How do I check that no amount was lost in the conversion?

With two checks. =COUNTA(D2:D413)-COUNT(D2:D413) must return 0, otherwise at least one amount is stored as text, which SUM ignores. And the opening balance plus the sum of all transactions must equal the closing balance on the statement.

Is it safe to use a free online bank statement converter?

Business accounts show names and IBANs of customers, suppliers and staff, which is personal data. If a provider processes it on your behalf, Article 28 of the GDPR requires a data processing agreement. Without one, you are better off importing in Excel or using a tool that keeps the file on your computer.

LET'S TALK ABOUT YOUR WORKFLOW

Write to us. We reply personally.

A question, an idea or a task that costs time every week? A few sentences are enough. You get an honest answer from the people who also build your office helper.

Ivan, Lukas & JanReply within 1–2 working days

  • Personal reply
  • No obligation
  • Based in Vienna, GDPR aware
Photo of Ivan
Rather talk directly?Ivan, your contact person+43 676 59 40 027office@pebblebyte.comMon to Fri 08:00–18:00
Have a specific task? Get your free workflow check

Your message

Please do not enter passwords or confidential customer data. We only use your details to handle your request. No newsletter, no sharing for advertising. Details in our privacy notice.