Bank statement format in Excel
A useful bank statement spreadsheet keeps transaction dates, descriptions, debits, credits and balances together. Download a free Excel template with an editable blank sheet and a fictional worked example, or follow the PDF and CSV conversion steps below. Check each row against the original before using the figures.
On this page
What is the bank statement format in Excel?
Keep one row per bank movement, with Date, Description, Debit, Credit and Statement balance columns. Enter debits and credits as positive values in their own columns; the template calculates a signed Amount and running balance. Keep the account, period, currency and opening and closing balances with the table.
Use the Your statement sheet for your own rows. The worked example starts at USD 2,200.00 and finishes at USD 2,566.00. Compare calculated balances with the statement and carry its two open evidence questions into the handover. This is a working spreadsheet, not a bank-issued statement.
Choose the clearest available source
Look for your bank's transaction download alongside the PDF statement. A CSV may give you structured rows directly, while the statement remains useful for checking account details, dates and balances. Keep both when available.
Create a working copy before editing. Record the business, account, date range and currency in the workbook so someone else can understand what the table covers. Avoid combining accounts without a clear way to identify each row.
Convert a statement in FigureLane
In Documents, choose Bank statement before uploading so money in and money out stay separate for review. Include every statement page. Use the document and record views to inspect dates and amounts, and resolve uncertain extraction against the original.
Open Reports and choose Uploaded dates, a month, year or custom range. Select Excel (.xlsx) for a workbook, or bank CSV for an import you will check separately. Reports are generated from the reviewed records; the original PDF’s layout is not recreated.
- Check the account currency before importing; FigureLane does not convert currencies.
- Classify income, expenses, transfers and excluded items as appropriate. A bank credit is not necessarily business revenue.
- Confirm receipt and invoice matches so supporting paperwork does not create a second copy of the same transaction.
- Review missing dates before relying on a date-filtered report.
Which columns should I check?
- Date: confirm the day and month order. An ISO date such as 2026-09-04 avoids ambiguous numeric dates.
- Description: preserve the bank's original description before adding a clearer explanation in another column.
- Amount: use one consistent convention. FigureLane expects credits to be positive and debits negative in a signed Amount column.
- Debit and Credit: if the download separates them, retain both columns and check how blank values are handled.
- Balance: preserve it when supplied. Do not label a running balance as a transaction amount.
- Currency: keep each account's currency clear. A column label does not convert an amount into another currency.
Worked example: four bank movements in USD
This fictional Harbour Studio statement covers 1–30 September 2026. It opens at USD 2,200.00 and closes at USD 2,566.00. The source statement shows money in and money out separately; the CSV uses one signed Amount column, with money out negative and money in positive.
Compare the source statement, CSV and Excel example row by row. The CSV has columns Date, Description, Amount and Balance, in that order. All example figures are USD; neither the example nor its column labels perform a currency conversion.
| Date | Description | Amount (USD) | Balance (USD) |
|---|---|---|---|
| 2026-09-04 | Northline supplies | -124.00 | 2,076.00 |
| 2026-09-06 | Studio rent | -680.00 | 1,396.00 |
| 2026-09-09 | Client payment | 1,450.00 | 2,846.00 |
| 2026-09-18 | Transfer to Alex | -280.00 | 2,566.00 |
Import the example and check it in Excel
In a version of Excel with From Text/CSV, choose Data → From Text/CSV and select the working copy. Preview the comma-separated columns before loading. Check how Excel interpreted the ISO dates, and make sure Amount and Balance contain numbers rather than text. Keep the original descriptions and amount signs.
If you load the provided four-column CSV with headers in row 1 and transactions in rows 2–5, enter =2200+SUM(C2:C5) in an empty cell outside the imported table. The result should be 2566.00: USD 2,200.00 opening balance + USD 1,450.00 credits − USD 1,084.00 debits. Adjust the opening balance and row range for your own statement.
Save the reviewed working file as an Excel workbook if you want to retain formulas and notes. A CSV stores table values rather than the full workbook's features. Keep the original statement and CSV alongside it.
Check totals and individual rows
Compare the opening balance plus credits minus debits with the closing balance. Investigate differences before handing over the workbook. Look for repeated rows, missing transactions, reversed signs and dates outside the period.
Then compare individual rows with the statement. A balance check can miss offsetting errors, and it cannot confirm that a payment's description or receipt match is right.
These deliberate changes to the example show what to look for. The expected closing balance stays USD 2,566.00; calculate from the edited Amount column rather than trusting a copied Balance value.
| Error in the working copy | Calculated closing balance | What the check reveals |
|---|---|---|
| Northline supplies loses its minus sign: +124.00 | USD 2,814.00 | USD 248.00 too high; reversing a debit changes the total by twice its value |
| The Studio rent row is missing | USD 3,246.00 | USD 680.00 too high; compare row coverage and the source statement |
| Northline becomes -142.00 and rent becomes -662.00 | USD 2,566.00 | The closing total still agrees. Check individual amounts and each running balance to find the offsetting errors |
What changes when the source is a PDF or photo?
Selectable PDF text and scanned images need different extraction methods. Check that every statement page is present and the rows remain readable. Use a clear, straight image rather than a photo taken at an angle.
Where supported, Excel's Data from Picture can turn an image of a table into editable data. Microsoft instructs users to review the results and make corrections before inserting them. Extraction does not replace checking the source.
Keep the workbook useful for the next reviewer
Include a source filename or access-controlled link for each relevant record, and identify unresolved questions. Confirm that the intended recipient can open the originals. Check any accounting import format rather than assuming every CSV is compatible.
The Harbour Studio balance agrees, but its Northline receipt is missing and the Alex transfer purpose is unresolved. Carry those questions into the monthly handover rather than treating the workbook as approved because its totals match.
FigureLane keeps statements, receipts and invoices in Documents, with Income and Expenses sections and an optional Document month filter. Reports has Excel, PDF, records CSV, bank CSV and ZIP outputs. The file month and report period follow document and record dates; the monthly upload allowance counts when files are sent.
Check your business figures
Use your reviewed totals in these free calculators. Each includes its formula, a worked example and downloadable results.
Common questions
Is the bank statement Excel template free?
Yes. Download the Excel workbook without an account or payment. It includes an editable blank sheet and a fictional USD example with balance checks. FigureLane's document-reading workspace is separate and includes a sample of 10 uploads total.
How do I calculate a bank statement balance in Excel?
Add credits to the opening balance and subtract debits. With a signed Amount column, add its sum to the opening balance. The template calculates running balances and compares them with the statement, but you must still check each date, description, amount and supporting document.
Is CSV usually easier to review than a scanned statement?
A bank-provided CSV already contains structured rows. Keep the statement too, so you can compare the imported data with its source.
Does a matching closing balance prove the data is correct?
No. Offsetting errors can still agree in total. Review individual rows, dates and supporting documents as well.
Can I convert a PDF bank statement to Excel with FigureLane?
Yes. Create an account, upload the PDF in Documents with Bank statement selected, check the extracted rows, then download Excel (.xlsx) from Reports. PDFs support up to 20 pages, including at most five scanned pages. The free sample includes 10 uploads total, with no expiry.