Build a checkbook register in Excel that agrees with the bank. Convert the statement PDF into rows with check numbers and the running balance, paste them under the template headers, and stop typing transactions twice. One-time 15 page demo, no credit card.
Upload your bank statement
Drop file here or click to upload
PDF, JPG, PNG, BMP, HEIC, TIFF, MT940, BAI2, NACHA, camt.053, CSV
Uploading...
A check register in Excel is one row per transaction with eight columns: date, check or reference number, payee, memo or category, payment, deposit, a cleared flag, and a running balance. The balance in each row is the balance above it plus the deposit minus the payment. Keep the register as an Excel table so new rows inherit the formula, and tick the cleared column against the bank statement every month.
The template is never the problem. A register drifts because every line is typed by hand, months later, from a paper stub or a PDF that cannot be sorted.
Checks get written into the register because you hold the checkbook. Card swipes, autopays, and bank fees only show up on the statement, so the register balance stays higher than the real one.
A running balance passes each mistake to every row below it. A transposed 54.30 typed as 45.30 makes the next hundred balances wrong by exactly 9.00.
A balance formula that points at the row above gives nonsense the moment the table is sorted by payee or amount. Most free templates break this way.
Copying rows out of a PDF merges the check number into the description, so you cannot match cleared checks by number.
Without a cleared column ticked against the statement, outstanding checks hide inside the balance and the register never ties to the bank.
Rebuilding six months of a register from statements means retyping hundreds of lines before you can even start checking them.
Convert the bank statement into spreadsheet rows and paste them into the register. Every card payment, transfer, and fee is there already. You add the categories and the checks that have not cleared yet.
Date, description, payment, deposit, and balance land in separate columns, ready to drop under the register headers.
Where the statement prints a check number, it stays a separate field, so cleared checks match the register by number.
The statement balance comes through as its own column, so you can compare it with the register balance line by line.
Convert several statements in one batch when the register is months behind, then stack them in date order.
XLSX goes straight into the template, CSV into Google Sheets, and QBO into QuickBooks when the register moves to accounting software.
256-bit encryption on every upload, and you can delete your files whenever you want.
A working checkbook register in one sitting, with the statement doing the typing.
Type Date, Number, Payee, Category, Payment, Deposit, Cleared, and Balance in row 1, select the range, and press Ctrl+T to make it an Excel table.
Put the starting balance from your last reconciled statement in the first row as a deposit, with the statement date and Opening balance as the payee.
Convert the PDF statement above, download XLSX, and paste the rows under the opening balance. Then add checks you wrote that have not cleared yet.
Put a C in the Cleared column for every row on the statement. What is left without a C is your list of outstanding checks and deposits.
Anyone who writes checks or runs a small account and wants to know the real balance before the bank tells them.
Tracking an operating account where vendor checks clear days or weeks after they are written.
Rebuilding a client register from statements before the year end close or a catch up engagement.
Keeping a register the board can read, with every check numbered and every deposit dated.
Recording every payment from a trust or estate account so the accounting to beneficiaries ties to the bank.
A checkbook register has stayed the same for decades because it works. Each row is one event in the account and the last column tells you what is left. In Excel the layout below is all you need.
| Column | What goes in it | Why it matters |
|---|---|---|
| Date | The date you wrote the check or the bank posted the item | Keeps the register in the order the balance is calculated |
| Number | Check number, or a short code such as DEB, ACH, DEP, or FEE | The key you match against the cleared checks on the statement |
| Payee | Who was paid or who paid you | Lets you find a payment without the check stub |
| Category | Rent, payroll, supplies, owner draw | Turns the register into a spending summary with a pivot table |
| Payment | Money out, as a positive number | Separate from deposits so totals never cancel out by accident |
| Deposit | Money in, as a positive number | Same reason |
| Cleared | C once the item appears on the statement | Separates outstanding items from what the bank already shows |
| Balance | The formula, never a typed number | The answer to how much is really in the account |
Two habits keep it honest. Never type a balance, always let the formula carry it. And never overwrite a voided check, enter it with a zero amount and VOID as the payee so the check sequence has no gaps.
The classic register formula points at the row above, for example =H2+F3-E3 in cell H3. It works until someone sorts the table by payee, and then every balance is wrong. A safer version sums everything from the top of the table down to the current row, so the result only depends on the row order you see:
=SUM(INDEX([Deposit],1):[@Deposit])-SUM(INDEX([Payment],1):[@Payment])
Sort the table by date before you read the balance and it is always right. Our check register formula in Excel guide walks through this formula, a cleared balance, and the checks that catch typing errors.
Most of what lands in a register today never passes through the checkbook. Card payments, payroll debits, loan autopays, transfers, and fees are only on the statement. Instead of reading the PDF and typing each line, convert the statement and paste the rows:
Once a month, filter Cleared to C and total it: opening balance plus cleared deposits minus cleared payments must equal the ending balance on the statement. The rows without a C are your outstanding items. If the numbers do not agree, the gap usually tells you the cause.
| What you see | Likely cause | Fix |
|---|---|---|
| Difference divides evenly by 9 | Two digits swapped when typing | Compare amounts with the converted statement rows |
| Difference equals a small round amount | A bank fee never entered | Add the fee as a payment and mark it cleared |
| Difference is twice one item | A deposit entered as a payment, or the reverse | Move the amount to the right column |
| Register higher than the bank | Card or ACH payments not entered | Paste the missing statement rows |
| Same amount twice | A check entered by hand and pasted from the statement | Keep one row, matched by check number |
For the formal month end version with deposits in transit and outstanding checks on separate sides, use the bank reconciliation template in Excel.
A spreadsheet register is enough for one or two accounts. When several accounts, a payroll account, or a client base enters the picture, the same converted rows import into accounting software. Download CSV or QBO instead of XLSX and see converting bank statements to QuickBooks, or keep the statement rows as the source for a full year with combined bank statements in Excel.
Excel offers register and budget templates in its template gallery under File, New, and you can search for checkbook or register there. Many people still build their own, because eight columns and one formula are all a register needs, and a self built table is easier to fill from a converted bank statement.
Type the headers Date, Number, Payee, Category, Payment, Deposit, Cleared, and Balance, turn the range into a table with Ctrl+T, enter the opening balance as the first deposit, and put the running balance formula in the Balance column. New rows inherit the formula automatically.
The bank statement shows what the bank has posted. The register shows everything you have done, including checks the payee has not cashed yet. The difference between the two is your list of outstanding items, which is why the register balance is the one to trust when you decide whether you can pay a bill.
Yes. Download the statement as CSV from online banking, or convert the PDF statement into Excel with BankXLSX, then paste the rows under the register headers. Map the statement debit column to Payment and the credit column to Deposit.
Enter checks when you write them and reconcile the register with the statement every month. A weekly update from the online banking export keeps card payments from piling up.
Yes. The columns are the same. Google Sheets has no structured table references, so use a plain running sum such as =SUM($F$2:F2)-SUM($E$2:E2) in the Balance column and fill it down.
The running balance and cleared balance formulas explained.
The formal month end reconciliation format.
Turn the statement PDF into register rows.
Stack several months of statements into one sheet.
When a spreadsheet is no longer enough.
Cancel anytime from your account settings · refund policy
Get started converting bank statements to spreadsheets.
USD
per month
per month
$288 charged today
One shared monthly allowance, up to half of it per day
| Base AI Faster | 800 pages |
| Pro AI Best accuracy | 80 pages |
Scale statement conversion across your team with automation.
USD
per month
per month
$888 charged today
One shared monthly allowance, up to half of it per day
| Base AI Faster | 1,920 pages |
| Pro AI Best accuracy | 192 pages |
Enterprise-grade bank statement conversion and controls.
USD
per month
per month
$ charged today
One shared monthly allowance, up to half of it per day
| Base AI Faster | pages |
| Pro AI Best accuracy | pages |
Try it once, free: sign up and convert up to 15 pages (5 per statement) with no credit card. The demo does not renew.