Import Bank Statements Into Excel Without Breaking Them

To import bank statements into Excel without breaking them, use Data, From Text/CSV instead of double-clicking the downloaded file, and set each column's type before loading: dates as Date, descriptions and account numbers as Text, amounts as Decimal Number. Then compare the row count and closing balance with the statement before trusting the sheet.

Opening a bank statement CSV versus importing it into Excel

Opening a bank statement CSV means double-clicking it or using File, Open, and letting Excel guess every column. Importing means using Data, From Text/CSV, which shows a preview and lets you choose each column's type first. Opening is faster. Importing is the route that keeps account numbers, long reference numbers and dates intact.

When you open a CSV directly, Excel reads it with its own defaults. Microsoft's article on importing or exporting text files says Excel "uses the current default data format settings to interpret how to import each column." The same article warns that a CSV column may be in month-day-year order while Excel expects another order.

Those defaults include a set of automatic conversions. Microsoft's page on setting automatic data conversions lists four: removing leading zeros, cutting numbers to 15 digits and possibly showing them in scientific notation, turning digits around the letter E into scientific notation, and turning letter-and-number strings such as JAN1 into dates. The page says these apply when you open a .csv or .txt file.

Each conversion has a bank statement version:

• A branch or account code such as 00418 becomes 418.
• A 16-digit card or reference number loses its last digit to the 15-digit limit.
• A check memo like 3E10 becomes a number in scientific notation.
• A description fragment that looks like a date becomes a date.

You can switch these conversions off under File, Options, Data, Automatic Data Conversion on Windows, according to the same Microsoft page. The page also says the options do not directly affect data imported with Power Query, which is what Data, From Text/CSV uses. For files you open often, changing the option helps. For a client's year of statements, importing is the safer habit, because the column types are written down in the query and repeat the same way next time.

Importing bank statement files with Data, From Text/CSV

Importing a bank statement file with Data, From Text/CSV loads it through Power Query, Excel's import tool, so you can check and set each column's type before any row reaches the worksheet. The steps below suit a CSV download from online banking, such as a checking account export with date, description, amount and balance columns.

Microsoft's guide to importing data from data sources gives the path as Data, Get Data, From File, From Text/CSV, and says Power Query detects delimiters, column names and types on its own. The Power Query data types documentation adds that this detection inspects the first 200 rows. Automatic detection is a guess, so review it.

Step list: importing one bank statement CSV

1. Save the download untouched. Keep the bank's CSV as the source file and work in a separate workbook.
2. Start the import. On the Data tab, in the Get & Transform Data group, select From Text/CSV, then pick the file.
3. Check the preview. Confirm the delimiter is a comma and that the first row holds the column names.
4. Select Transform Data rather than Load. This opens the Power Query Editor, where the types can be fixed.
5. Set each column's type from the icon on the left of the column heading, using the table below.
6. Fix the date column with a locale if needed. If dates show errors, right-click the heading and choose Change Type, then Using Locale, and pick the locale that matches the bank's date order.
7. Close and Load to a sheet named for the account and period.
8. Prove the import. Count the rows against the statement, and add the amounts to the opening balance to reach the closing balance.

Column types for each bank statement column

The Power Query documentation describes Fixed decimal number, also called Currency, as a type with four digits after the decimal separator that is "always precise," while Decimal Number is a floating-point type. Either works for statement amounts. Fixed decimal number avoids tiny rounding differences when you total thousands of rows.

Dates are the column most likely to need more work after import. Fixing text dates, dates with no year and swapped day and month is covered in how to convert bank statement text dates to real dates.

How do you import bank statement QFX or OFX files into Excel?

QFX and OFX files are bank downloads built for finance software, not spreadsheets. OFX, short for Open Financial Exchange, is a published standard for exchanging financial data, and banks label QFX downloads for Quicken. The dependable way into Excel is to download the same activity again as a CSV.

Banks usually offer several formats side by side. U.S. Bank's help page on downloading transactions lists a spreadsheet CSV, QFX for Quicken and QBO for QuickBooks, and says up to 18 months of transactions can be downloaded. When a client sends a QFX or QBO file, ask for the CSV for the same dates, or download it yourself if you have access.

If only the OFX file exists, it helps to know what is inside. The Financial Data Exchange page on the OFX Work Group says OFX 1.0 used an SGML syntax and OFX 2.x specifications are XML-based. Opened in a text editor, the file shows each transaction as a set of tagged values rather than columns.

Excel can read XML through Data, Get Data, From File, From XML, according to Microsoft's guide to importing data from data sources. An OFX 2.x file is XML, so this route may show its transaction list in the Navigator. An older SGML file is not XML, and you should not expect the XML import to read it. In that case the CSV download, or the PDF statement for months outside the download window, is the record to work from.

The bank's own labels say what each file is for: QBO for QuickBooks and QFX for Quicken. Importing bank files into accounting software, rather than into Excel, has its own guides in the QuickBooks, Xero and Excel category.

Pasting bank statement text into Excel columns

Pasting bank statement text into Excel puts each copied line into one cell, so the date, description and amount all sit in column A. Text to Columns splits that cell into separate columns. It is the quickest way to turn a few copied lines from a web page or a digital PDF into a bank statement spreadsheet.

Microsoft's article on the Convert Text to Columns Wizard gives the steps:

1. Select the cell or column that holds the pasted text.
2. On the Data tab, select Text to Columns.
3. Choose Delimited, then Next.
4. Select the delimiters that match the data, such as Comma or Space, and watch the Data preview.
5. Select Next, choose the Destination, and select Finish.

Bank statement text rarely splits cleanly on spaces. A line like "03/14 POS PURCHASE HARBOR HARDWARE 0418 62.18" splits into six or seven columns, because the description itself contains spaces. Set the destination to a blank area, split, then join the description pieces back together. Check the last column on every row: it should be the amount, and a row where it is not usually had an extra word in the description.

Microsoft's page on automatic data conversions lists Text to Columns among the operations where Excel's conversions can apply, so watch for dropped leading zeros in reference numbers. For more than a page of text, a CSV download or a PDF import through Power Query is less work than splitting and checking line by line.

Bank statement import mistakes that turn amounts into text

An amount stored as text looks like a number but does not add up: SUM skips it, the column total comes out low, and the balance check fails. Bank statements cause this through trailing minus signs, currency symbols, parentheses around negatives, and comma decimal separators. Each has a specific fix.

Excel often marks these cells with a small error indicator. Microsoft's page on converting numbers stored as text says you can select the cells, open the indicator and choose Convert to Number, or use the VALUE function in a helper column and paste the results back as values.

VALUE handles plain cases. Microsoft's VALUE function page shows =VALUE("$1,000") returning 1000, and says VALUE returns #VALUE! when the text is not in a number, date or time format Excel recognizes. For the other patterns, remove the problem characters with SUBSTITUTE first.

Fix table for bank statement amounts stored as text

The formulas assume the text amount is in cell A2. All values are examples.

Two of these can be handled at import. The Text Import Wizard page says its Advanced button lets you set the file's decimal and thousands separators and specify that values may carry a trailing minus sign. The wizard is a legacy feature: the page says to turn on From Text (Legacy) under File, Options, Data first.

After any fix, test it. Total the corrected column and run the balance check again. A fixed column that still misses the closing balance points to a dropped or doubled row, not a format problem.

How do you combine twelve months of bank statements in Excel?

Combining twelve months of bank statements in Excel works best with a Power Query folder import. Put the twelve CSV files for one account in their own folder, import the folder, and Excel stacks every file into one table. When next month's file arrives, add it to the folder and refresh.

Microsoft's article on importing data from a folder with multiple files gives the path as Data, Get Data, From File, From Folder. After you select the folder, Excel lists the files. Choose Combine and Transform Data to review the result first, or Combine and Load to send it straight to a sheet. In the Combine Files dialog, the Sample File box sets which file Power Query uses as its model.

The same article sets the requirement that matters most: "Each file should have the same schema with consistent column headers, data types, and number of columns." It says columns may be in a different order, because matching is done by column name, and that the folder should hold only the files you want to combine.

A worked example for one account

In this fictional example, the firm's client Larkspur Dental Group has a checking account at Example Community Bank, XXXX-4821. The bookkeeper downloads twelve monthly CSVs for 2026 and names them 2026-01.csv through 2026-12.csv in a folder called Larkspur checking 4821.

1. Import the folder and choose Combine and Transform Data.
2. Pick 2026-01.csv as the sample file.
3. Set the column types from the table earlier in this post.
4. Load the result to a sheet named Checking 4821 2026.
5. Check the total row count against the twelve statements added together.
6. Confirm the January opening balance plus the year's amounts equals the December closing balance.

Two things break a folder import. A bank that changed its export layout midyear produces files with different headers, so rename the columns in the odd files before combining. A stray file, such as a PDF saved into the same folder, adds errors, so keep the folder to CSVs only.

For months only available as PDF statements, the CSV route is closed. Schema CPA reads the PDF bank, savings, loan and credit card statements a client sends, ties each month's balance to the next, and exports the rows as Excel or CSV for the same folder; the document types Schema CPA reads are listed on the homepage. Card statements follow different sign rules, which the post on getting a credit card statement into Excel works through, and firms that prefer a browser spreadsheet can convert bank statements to Google Sheets instead.