How to Convert a Bank Statement to Excel

The easiest way to get a bank statement into Excel is not to convert a statement at all. Instead, sign in to your online banking and download your transactions as a CSV or Excel file, which opens straight in Excel. If you only have a PDF statement, Excel for Microsoft 365 on Windows can import tables from it: go to Data > Get Data > From File > From PDF. For a paper statement, use Data from Picture to scan the table with your phone or webcam. Whichever method you use, check that the totals match the statement before you rely on the data. Here’s each method, plus how to clean up the result.

Method 1: Download transactions from online banking (best)

1. Sign in to your bank’s website, not the mobile app, which often doesn’t offer exports.
2. Open the account and look for Download, Export or Download transactions, often near the transaction list or under statements.
3. Choose a date range and pick CSV or Excel as the format. (QFX and OFX are for accounting apps, not Excel.)
4. Open the downloaded file in Excel.

If dates or amounts look wrong after opening a CSV, import it with Data > From Text/CSV instead, which lets you set each column’s type. See how to import a CSV file into Excel.

Method 2: Import a PDF statement with Get Data

This works in Excel for Microsoft 365 on Windows (and some recent standalone versions). If you don’t see From PDF, use Method 3 or 4.

1. Open a blank workbook and go to Data > Get Data > From File > From PDF.
2. Select the statement PDF and click Import. If it’s password-protected, enter the password when asked.
3. The Navigator lists the tables and pages Excel found. Click each one to preview it, and pick the table that holds the transactions. Tick Select multiple items to choose several pages.
4. Click Load to put it straight into a sheet, or Transform Data to clean it up in Power Query first.

Each page often comes in as a separate table. In Power Query, use Home > Append Queries to stack them into one list. For more, see how to find and enable Power Query in Excel.

This only works on PDFs that contain real text. A scanned statement is just a picture, so use Method 3 for that.

Method 3: Data from Picture (paper or scanned statements)

1. In Excel, go to Data > From Picture (or Insert > Data from Picture, depending on your version). In the Excel mobile app, tap the Insert data from picture button.
2. Choose a screenshot or image file of the statement, or take a photo with your phone.
3. Excel converts the table and shows a preview. Check each highlighted cell, fix any mistakes, and click Insert data.

Good lighting and a flat page make a big difference. See how to insert data in Excel from a picture.

Method 4: Copy the table through Word

If you don’t have Get Data from PDF, open the PDF in Word (File > Open, then click OK when Word offers to convert it). Then copy the transaction table and paste it into Excel. The layout isn’t always perfect, but it’s often good enough for a few pages.

A word on online converters

Many websites convert PDF statements to Excel. Bank statements contain your account number, address and spending history, so think carefully before you upload them to a service you don’t know. Use the built-in Excel methods above when you can, and remove or black out account numbers if you do use an outside tool.

Clean up the data

• Dates as text: If dates won’t sort, select the column and use Data > Text to Columns > Finish, choosing the date format your bank uses (such as DMY or MDY).
• Debit and credit columns: Combine them into one amount column with =C2-B2 (credits minus debits), so you can total it.
• Amounts with “CR” or “DR”: Use Find and Replace (Ctrl + H) to remove the letters, then make debits negative.
• Repeated headers: Filter the date column for the header text and delete those rows.
• Turn it into a table: Press Ctrl + T so you can sort, filter and add categories easily.

Check the numbers

Before you rely on the converted data, compare it with the statement:

1. Add up deposits and withdrawals with =SUMIF(D:D,">0") and =SUMIF(D:D,"<0").
2. Check that the opening balance plus the net total equals the closing balance on the statement.
3. Count the transactions and compare the count with the statement.

If the numbers don’t match, a row was probably missed at a page break. For a full method, see how to do a reconciliation in Excel.

What next?

Add a Category column (such as Groceries, Rent or Fuel) and use a PivotTable to total spending by category or month. Or plug the totals into a budget. See how to make a monthly budget in Excel.

Summary

1. Download a CSV or Excel file from online banking whenever you can.
2. For PDF statements, use Data > Get Data > From PDF.
3. For paper statements, use Data from Picture.
4. Clean up dates and amounts, then check the totals against the statement.

Related: convert PDF to Excel without losing formatting.

Related: transfer data from Word to Excel.

Matt Jacobs

Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.

His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.