Check Register Template in Excel for a Checkbook Register That Balances

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.

One-time 15 page demo, no credit card
Check numbers kept in their own column
Excel, CSV, and QBO output

PDF, JPG, PNG, BMP, HEIC, TIFF, MT940, BAI2, NACHA, camt.053, CSV

Upload your bank statement

Extract:

The check register layout, in short

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.

Why a Checkbook Register Drifts Away From the Bank

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.

Debit Card and ACH Lines Never Get Entered

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.

One Typo Carries Down Every Row

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.

Sorting Breaks the Balance

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.

Check Numbers Are Lost in Copy and Paste

Copying rows out of a PDF merges the check number into the description, so you cannot match cleared checks by number.

Nobody Marks What Cleared

Without a cleared column ticked against the statement, outstanding checks hide inside the balance and the register never ties to the bank.

Catch Up Months Take a Weekend

Rebuilding six months of a register from statements means retyping hundreds of lines before you can even start checking them.

Fill the Register From the Statement, Then Add What Only You Know

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.

One Transaction Per Row

Date, description, payment, deposit, and balance land in separate columns, ready to drop under the register headers.

Check Numbers in Their Own Column

Where the statement prints a check number, it stays a separate field, so cleared checks match the register by number.

Bank Balance for Every Row

The statement balance comes through as its own column, so you can compare it with the register balance line by line.

Months at Once

Convert several statements in one batch when the register is months behind, then stack them in date order.

Excel, CSV, or QBO

XLSX goes straight into the template, CSV into Google Sheets, and QBO into QuickBooks when the register moves to accounting software.

Private by Default

256-bit encryption on every upload, and you can delete your files whenever you want.

How to Set Up a Check Register in Excel in 4 Steps

A working checkbook register in one sitting, with the statement doing the typing.

1

Create the Headers

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.

2

Enter the Opening Balance

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.

3

Paste the Statement Rows

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.

4

Mark Cleared Items

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.

Who Keeps a Check Register in Excel

Anyone who writes checks or runs a small account and wants to know the real balance before the bank tells them.

Small Business Owners

Tracking an operating account where vendor checks clear days or weeks after they are written.

Bookkeepers

Rebuilding a client register from statements before the year end close or a catch up engagement.

Treasurers of Clubs and Associations

Keeping a register the board can read, with every check numbered and every deposit dated.

Trustees and Fiduciaries

Recording every payment from a trust or estate account so the accounting to beneficiaries ties to the bank.

Transaction Types We Handle

Written checks
Debit card payments
ACH and autopay
Bank fees
Deposits
Transfers
Interest earned
Voided checks

The columns a check register needs

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.

ColumnWhat goes in itWhy it matters
DateThe date you wrote the check or the bank posted the itemKeeps the register in the order the balance is calculated
NumberCheck number, or a short code such as DEB, ACH, DEP, or FEEThe key you match against the cleared checks on the statement
PayeeWho was paid or who paid youLets you find a payment without the check stub
CategoryRent, payroll, supplies, owner drawTurns the register into a spending summary with a pivot table
PaymentMoney out, as a positive numberSeparate from deposits so totals never cancel out by accident
DepositMoney in, as a positive numberSame reason
ClearedC once the item appears on the statementSeparates outstanding items from what the bank already shows
BalanceThe formula, never a typed numberThe 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 balance formula that survives sorting

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.

Fill the register from the bank statement

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:

  1. Upload the statement PDF to BankXLSX and download Excel. Scanned statements go through OCR.
  2. Copy date, check number, description, debit, and credit into Date, Number, Payee, Payment, and Deposit. The bank writes debits and credits from its own side, so a debit on the statement is a payment in your register.
  3. Put C in Cleared for every pasted row, because the bank already posted them.
  4. Add the checks you wrote after the statement closing date, with Cleared left blank.
  5. Compare the register balance on the statement closing date with the closing balance printed on the statement. If they agree, the register is right.

Monthly check of the register against the bank

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 seeLikely causeFix
Difference divides evenly by 9Two digits swapped when typingCompare amounts with the converted statement rows
Difference equals a small round amountA bank fee never enteredAdd the fee as a payment and mark it cleared
Difference is twice one itemA deposit entered as a payment, or the reverseMove the amount to the right column
Register higher than the bankCard or ACH payments not enteredPaste the missing statement rows
Same amount twiceA check entered by hand and pasted from the statementKeep 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.

When a register outgrows 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.

Why the Rows Matter More Than the Template

8
Columns in a complete register
1
Formula that carries the balance
9
Divides the gap on a transposition

Security & Privacy

  • Your statements stay yours
  • Uploads are encrypted with 256-bit encryption and you can delete your files at any time.

Common Questions About a Check Register 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.