Bank Reconciliation Format in Excel — Ready Layout

Build a clean bank rec sheet and pull your bank statement from PDF into real .xlsx—free 3/day, scans OK.

AI extraction can contain errors. Verify the output against the source document before using it for accounting, reporting or any decision.

🤖
AI Table Extraction

Text PDFs are parsed on our server; scans are read by GPT-5 vision

📊
Excel, CSV, Word, PowerPoint

One result, any format from the Download menu: .xlsx, .csv, .docx, .pptx

🧾
Statements → QuickBooks, Xero

Bank and card statements export to .qbo, .ofx and import-ready CSV for QuickBooks and Xero

🔒
No Registration

3 files a day free, files deleted right after processing. Pro: $9/mo, no daily limit

Short answer: Need a bank reconciliation format in Excel? Use PDF2XLS to pull your bank statement PDF into a real .xlsx, then fill the layout below. Free for 3 files/day, up to 10 MB, no signup. Scans/photos are handled by GPT‑5 vision. You get rows, columns, and numbers as numbers; fonts and formulas don’t transfer.

  • Free tier: 3 files/day, no signup
  • Max file size: 10 MB per file
  • Output: .xlsx — opens in Excel, Google Sheets, Numbers, LibreOffice Calc
  • Scans supported: Yes — GPT‑5 vision OCR built in
  • What you get: Table grid, cell values, numbers as numbers
  • Not preserved: Fonts, colors, logos, images, charts, headers/footers, formulas

What is the standard bank reconciliation layout in Excel?

It’s two sections — Bank side and Book (cash ledger) side — both adjusted to the same number. Here’s a clean layout you can copy into a sheet (Column A = line label, Column B = amount):

LineExample in B
Bank statement ending balanceEnter from statement
Add: Deposits in transit=SUM(Bank!E:E if you tag DIT)
Less: Outstanding checks=SUM(Bank!F:F if you tag O/S)
Plus/Minus: Bank errorsManual items
Adjusted bank balance=B1+B2-B3+/-B4
Book (cash ledger) ending balanceEnter from GL/cashbook
Less: Bank chargesFrom statement
Add: Interest earnedFrom statement
Plus/Minus: Book errorsManual items
Adjusted book balance=B6-B7+B8+/-B9
Difference (should be 0)=B5-B10

Keep your statement transactions on a separate tab (e.g., “Bank”). If you don’t want helper tags, you can enter amounts for deposits in transit and outstanding checks directly.

How do I build this sheet and bring in the statement data quickly?

  1. Upload the PDF bank statement above. The widget converts one file at a time into a real .xlsx.
  2. Open the download in Excel. You’ll see rows/columns with numbers typed as numbers (no formulas yet).
  3. Copy the transactions table into a “Bank” tab in your reconciliation workbook. Add header labels (Date, Description, Withdrawals, Deposits, Balance, Check No.).
  4. Create a “Reconciliation” tab using the layout shown. Link the “ending balance” cell to the final balance in the “Bank” tab or enter it manually.
  5. Identify reconciling items: - Deposits in transit: deposits dated on/before month end that are missing from the statement total. - Outstanding checks: checks issued before month end that have not cleared. Add a Status column on “Bank” (e.g., Cleared/DIT/O‑S) and sum each group with SUMIF/SUMIFS.
  6. Enter bank fees and interest from the statement, and any error corrections on either side. Your “Difference” should read 0.

Working in Google’s spreadsheet instead? Convert here, then import the .xlsx — see PDF to Google Sheets for import gotchas.

What trips up bank statement conversion (and how is it handled)?

Bank PDFs often have multi‑line descriptions, negative numbers in parentheses, and running balances across page breaks. PDF2XLS keeps the table grid and numbers, which is what a reconciliation needs. Expect styling, logos, and headers/footers to be dropped. For a deeper explanation of extraction, see our PDF extractor for tables.

Edge cases to watch for after conversion:

  • Parentheses negatives: ensure they came in as negative numbers, not text. If text, fix with Data > Text to Columns or VALUE() after replacing parentheses.
  • Dates: confirm month/day order. Sort by Date to spot mis-parsed rows.
  • Page repeats: remove any carried header rows that landed mid‑table.
  • Thousands/decimal separators: verify currency cells show the correct decimal point/comma for your locale.

If my PDF is a scan or photo, will this still work?

Yes. Scanned statements go through the same pipeline; GPT‑5 vision reads the page image and outputs a table. Accuracy depends on image quality (crooked shots, blur, shadows reduce fidelity). For tips and expectations, read OCR online for tables to XLSX before you trust the totals.

What should I verify before I sign off the reconciliation?

Run this checklist every time:

  • Opening and ending balances match the statement and the cash ledger for the same period.
  • Adjusted bank balance equals adjusted book balance (difference = 0).
  • All deposits in transit and outstanding checks are dated on/before the cutoff.
  • Fees and interest are posted to the ledger.
  • No duplicate lines from page breaks; running balance changes consistently.
  • Check numbers and payees look sane; sort by amount/date to spot outliers.

Do you keep my bank data?

No. Files are processed on the server via the OpenAI API and deleted immediately after conversion. We don’t keep your documents. There’s nothing to install; it works in any browser on Windows, macOS, Linux, iPhone, iPad, or Android.

When this won’t fit your case

If your file is over 10 MB, if you need to process a large batch at once, require preserved formatting/logos, or need to convert password‑protected PDFs, this isn’t the right tool. Convert one statement at a time within the limits, then build the rec in Excel.

Ready to reconcile? Upload your statement above to convert your PDF and start filling the sheet.

FAQ

We don’t host downloads, but the layout above is ready to copy into your workbook. Use the lines shown for Bank and Book sides, then add simple SUMIF or SUM formulas to total deposits in transit, outstanding checks, fees, and interest.

Yes. Make one tab per account and one reconciliation tab per month, or add an Account column to the transactions sheet and filter by month. Keep each period’s ending balances and reconciling items separate so the difference line reaches zero.

The limit is 10 MB per file. Try exporting a shorter date range from your bank, re‑saving the PDF at a lower DPI, or splitting the PDF by month. Then convert each part and combine the tables in Excel before reconciling.

No. The output is plain, accurate tables: rows, columns, and numbers as numbers. Fonts, colors, logos, images, and formulas do not survive. Add your formulas and any formatting after opening the .xlsx in Excel or Google Sheets.

The output here is .xlsx. If you truly need CSV, open the file in Excel or Google Sheets and export to CSV. Confirm your delimiter and UTF‑8 encoding, and recheck that negatives and dates still parse correctly.