Free sample bank statement in Excel (with a blank template)
Need a bank statement in Excel to practise on, test an import or a converter, or use as a starting layout for your own? This one is free with no sign-up. It's a fictional month of business checking, 31 transactions laid out like a statement, with formulas that work out the running balance and check the total against the statement's ending balance. There's a blank copy of the same layout in the same file, so it doubles as a bank statement template.
Short answer: download the sample bank statement Excel file (.xlsx). The Sample statement sheet is the made-up Example Community Bank, Business Checking ending 0427, September 2026: beginning balance 12,418.36, deposits 29,597.43 (11), withdrawals 28,575.27 (20), ending balance 13,440.52, with Date, Description, Withdrawals, Deposits and Balance columns. The Blank template sheet has the same formulas and 300 empty rows. The same transactions also come as a raw bank-export Excel file, a clean CSV and the PDF statement.
See a bank Excel file turned into QuickBooks and Xero files
Open the bank-export Excel sample in Utilly's converter: it finds the header row under the title lines, combines Withdrawals and Deposits into one signed amount, leaves out the beginning balance line and writes a .QBO, .IIF, Xero CSV or Quicken file. Every download is free for the sample, and your own spreadsheet is free up to 100 rows.
Got your own statement as a PDF? Turn it into Excel
Drop in a real bank statement PDF: the preview reads every transaction and checks it against the statement's ending balance, free with no sign-up. Your first 3 full statements are free with your email (every format: Excel, CSV, QuickBooks, Xero, Quicken). After that, $9 for 5 statements, no subscription.
1. What's in the Excel file
One workbook, three sheets. It opens on the sample.
- Sample statement: the fictional statement, laid out the way a statement prints. A header block (bank, account, period), a summary (beginning balance, deposits and other credits with a count, withdrawals and other debits with a count, the calculated ending balance, the ending balance printed on the statement, and a Check line), then all 31 transactions with real dates and numbers.
- Blank template: the same layout and formulas with the yellow input cells empty and 300 transaction rows.
- How to use: short instructions, including what to do when the check doesn't tie out.
Only the Balance column, the totals and the check are formulas; everything else is typed values. It uses only SUM, COUNT, ROUND, IF, AND and TEXT, so it works the same in Excel, Google Sheets (File, Import), LibreOffice and Numbers.
2. All the sample Excel and CSV files
Every file below holds the same 31 fictional transactions, so you can compare them side by side.
| File | Layout | Use it for |
|---|---|---|
| Statement workbook (.xlsx) | Summary block, then Date, Description, Withdrawals, Deposits, Balance (formulas); plus a blank template | Learning to read a statement, a layout for your own, spreadsheet practice |
| Bank-export Excel (.xlsx) | 2 title lines, a blank line, then Date, Description, Debit, Credit, Balance with a Beginning balance line | Testing a converter or import against a realistic bank download |
| Clean CSV | Date (YYYY-MM-DD), Description, Amount (money out negative), Balance | Pivot tables, scripts, a known-good reference |
| QuickBooks Online CSV | Date, Description, Amount | A QuickBooks Online test company, Upload from file |
| Xero CSV | *Date, *Amount, Payee, Description, Reference | Xero's Demo Company, Import a Statement |
The raw CSV version of the bank export is explained line by line on sample bank statement CSV, the printed statement on sample bank statement PDF, a credit card version of this workbook on credit card statement Excel template, and the .QBO, .IIF, .QIF and .OFX files are on sample bank statement files.
3. Bank statement format in Excel
A bank statement in Excel works best with one row per transaction and one kind of value per column. The sample uses the layout most statements print:
| Column | Sample | Tip |
|---|---|---|
| Date | 09/01/2026 | A real Excel date, not text, so it sorts and filters. Text dates are left-aligned; see formulas for the fix |
| Description | ACH DEBIT GUSTO PAYROLL 0912 | Keep the bank's wording; it's what QuickBooks and Xero bank rules match on |
| Withdrawals | 6,420.18 | Money out as a positive number, Deposits empty on that row |
| Deposits | 3,184.27 | Money in as a positive number, Withdrawals empty on that row |
| Balance | 5,998.18 | The running balance after the row; on a statement it lets you check every line |
The other common layout is one signed Amount column (money out negative), which is what the clean CSV and QuickBooks Online's 3-column import use. Two columns are easier to read; one signed column is easier to total and import. The formulas below convert between them. A real bank's statement adds things the sample leaves out, such as check images, fee and interest disclosures and the bank's contact details.
4. Bank statement formulas in Excel
On the Sample statement sheet the beginning balance is in E10 and the transactions are rows 19–49 (C = Withdrawals, D = Deposits, E = Balance).
| What | Formula | Sample result |
|---|---|---|
| Total deposits | =SUM(D19:D49) | 29,597.43 |
| Total withdrawals | =SUM(C19:C49) | 28,575.27 |
| Number of deposits / withdrawals | =COUNT(D19:D49) / =COUNT(C19:C49) | 11 / 20 |
| Ending balance | =ROUND(E10+SUM(D19:D49)-SUM(C19:C49),2) | 13,440.52 |
| Running balance (first row, then fill down) | =E10-C19+D19, then =E19-C20+D20 | 5,998.18, then 9,182.45 |
| Running balance that survives sorting and blank rows (what the file uses) | =IF(AND(C19="",D19=""),"",ROUND($E$10+SUM(D$19:D19)-SUM(C$19:C19),2)) | 5,998.18 |
| Two columns into one signed Amount | =D19-C19 | −6,420.18 |
| One signed Amount (in C) into two columns | =IF(C2<0,-C2,"") and =IF(C2>0,C2,"") | 6,420.18 and blank |
| Deposits in a month (signed Amount in C, dates in A) | =SUMIFS(C:C,C:C,">0",A:A,">="&DATE(2026,9,1),A:A,"<"&DATE(2026,10,1)) | 29,597.43 on the clean CSV |
Two problems account for most broken bank statement spreadsheets:
- Amounts stored as text (they sit left-aligned and SUM returns 0). Use Data, Text to Columns, Finish on the column, or
=VALUE(SUBSTITUTE(SUBSTITUTE(C2,"$",""),",","")). A trailing minus or brackets, as some banks print, need cleaning first. - Dates stored as text, or day and month swapped when a US file opens on a computer set to day-first dates. Don't double-click a CSV to open it; import it with Data, From Text/CSV and set the date column's format, as shown in opening a bank CSV in Excel.
5. Using the blank template
- Type the bank, account and period, and the beginning balance from the statement into the yellow cells.
- Enter each transaction: date, description, and the amount under Withdrawals or Deposits as a positive number.
- Balance fills in by itself. Compare it with the balance the statement prints on the same line, and a typo shows up on the row where they part.
- Type the ending balance printed on the statement. The Check line reads Ties out when every transaction is there, or Off by the difference.
- Need more than 300 rows? Insert rows above the last transaction row so the totals and Balance formulas stretch with them.
The template is for organising transactions you already have. It isn't a way to produce a statement: a statement only counts when it comes from the bank (see what the sample is not for). If you're comparing the statement with your own books, the free bank reconciliation template is the next step — or follow how to reconcile a bank statement in Excel with that template.
Writing checks and payments as they happen, before the statement exists? Use the check register template instead: a running balance, a Cleared column, and an Example sheet that is this same account in register form.
Want the same month with a category on every line and totals by category? The cash book template has it as its Example sheet.
6. Getting an Excel statement into QuickBooks or Xero
QuickBooks Online and Xero import bank transactions from CSV or bank files (.QBO, .OFX), not from a statement-style workbook with a summary block on top. Three ways to get there:
- Save as CSV yourself: keep one header row and only the transaction rows, combine Withdrawals and Deposits into a signed Amount (
=D19-C19), and save as CSV. QuickBooks Online takes Date, Description, Amount (or Date, Description, Credit, Debit); Xero's statement import has its own template with *Date and *Amount. - Upload the workbook to a converter: Excel to QBO or Excel to IIF reads the first sheet with data, finds the header row under the summary, combines Withdrawals and Deposits and shows the rows before you download. The blank template is the first sheet, so a filled-in copy uploads as it is. Free up to 100 rows.
- Use the bank's own download when the months you need are still available in online banking, as .QBO for QuickBooks or CSV for Xero.
Try any of these with the sample in a test or demo company only, never your real books.
7. Checking against the known totals
After converting or importing the sample, compare four numbers:
| Check | Correct result | If it's wrong |
|---|---|---|
| Transactions | 31 | 32 means the Beginning balance line of the bank-export file came in as a row |
| Money in | 29,597.43 (11 rows) | 42,015.79 means the 12,418.36 beginning balance was read as a deposit |
| Money out | 28,575.27 (20 rows) | A smaller total usually means amounts stored as text were skipped |
| Beginning to ending balance | 12,418.36 to 13,440.52 | 11,396.20 means Withdrawals and Deposits were swapped |
On the bank-export Excel file (beginning balance in E5, transactions in rows 6–36): =E5-SUM(C6:C36)+SUM(D6:D36) returns 13,440.52. The free bank statement balance checker runs the same test on any pasted list.
8. What the sample is not for
It's for learning the layout, practising formulas and testing software. The bank, account and transactions are made up, and the file says FICTIONAL DATA next to the account and not a real bank next to the bank's name. It can't be used as proof of income, address or funds. Making up or altering bank records to use as proof is fraud. If someone asks for your bank statement, download the real one from your bank.
9. Your own statement in Excel
Most banks let you download recent transactions as CSV or Excel from online banking, often under Download transactions or Export; the bank pages linked from bank CSV formats show where. For older months, or when all you have is the PDF statement, upload it to bank statement to Excel: the preview is free with no sign-up and checks that the rows add up to the statement's ending balance, so you don't have to type anything. Your first 3 full statements are free with your email; after that, downloading uses 1 credit ($9 for 5, no subscription) or Pro at $19 a month.
Turn your statement PDF into Excel
PDF statements, downloaded or scanned, get a free preview with the balance check, no sign-up. CSV and Excel exports convert to QuickBooks, Xero and Quicken files free up to 100 rows. Files are processed in memory and not stored.
FAQ
Where can I download a sample bank statement in Excel?
On this page, free and with no sign-up. The .xlsx holds a fictional business checking statement from the made-up Example Community Bank, account ending 0427, September 2026: beginning balance 12,418.36, 11 deposits totalling 29,597.43, 20 withdrawals totalling 28,575.27 and an ending balance of 13,440.52, with Date, Description, Withdrawals, Deposits and Balance columns. A raw bank-export Excel file and a clean CSV hold the same transactions.
Is there a free bank statement template for Excel?
Yes. The same workbook has a Blank template sheet with the statement layout, 300 transaction rows and the formulas already in place: enter the beginning balance and the transactions, and it works out the running balance, the totals and whether the result matches the ending balance printed on the statement. It works in Excel, Google Sheets, LibreOffice and Numbers.
What is the formula for a running balance on a bank statement in Excel?
Start from the beginning balance and apply each row: with the beginning balance in E10, withdrawals in C and deposits in D from row 19, use =E10-C19+D19 on the first row and =E19-C20+D20 below it, filled down. If amounts are in one signed column, it's the previous balance plus the amount. The template uses =IF(AND(C19="",D19=""),"",ROUND($E$10+SUM(D$19:D19)-SUM(C$19:C19),2)) so empty rows stay blank.
Is this a real bank statement?
No. The bank, account and transactions are made up, the file says FICTIONAL DATA next to the account, and it can't be used as proof of income, address or funds. Making up or altering bank records to use as proof is fraud. It's for learning the layout, practising formulas and testing software.
Can I import an Excel bank statement into QuickBooks Online or Xero?
Not as a statement-style workbook. Both import bank transactions from CSV or bank files such as .QBO and .OFX. Keep one header row and the transaction rows, combine withdrawals and deposits into one signed Amount and save as CSV, or upload the workbook to an Excel to QBO converter, which finds the header row and combines the columns for you.
How do I get my own bank statement into Excel?
Download transactions as CSV or Excel from online banking for recent months. For older months, or when you only have the PDF statement, a bank statement to Excel converter reads the PDF and checks the rows add up to the statement's ending balance. Utilly's preview is free with no sign-up.
Related guides
- How to reconcile a bank statement in Excel (step by step with a free template)
- Free sample bank statement CSV (fictional, with known totals)
- Free sample bank statement PDF (fictional, with known totals)
- Free sample bank statement files: PDF, CSV, QBO, IIF, QIF and Xero CSV
- Free bank reconciliation template for Excel (with a worked example)
- Free check register template for Excel (with a cleared-balance check)
Related tools
- Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
- Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.
