Free Bank Account Spreadsheet in Excel: Build a Register

Quick Answer: A free bank account spreadsheet in Excel is a check register: columns for Date, Description, Check No., Withdrawal, Deposit, Balance and Cleared, formatted as a table so the running balance formula fills itself. Start with the opening balance from your last statement, then paste each month's transactions from the bank's CSV export or a converted PDF statement rather than typing them, and compare the cleared balance with the statement.

You don't need a template to keep a bank account register in Excel. Seven columns and two formulas give you a running balance that updates whenever you add a row, plus a cleared balance you can check against the bank statement each month. The slow part is entering transactions, and that can come straight from your statement.

What should a bank account spreadsheet track?

A register answers two questions at any moment: what the account will hold once everything you've written or scheduled goes through, and what the bank has actually processed so far. That second number is what makes the sheet useful for reconciling. The IRS, in Publication 583, calls the business checkbook "your basic source of information for recording your business expenses" and recommends reconciling it each month, and the same habit works for a personal account.

Use these columns, in this order:

Optional extras: Category (Groceries, Rent, Income) for budgeting, and Notes. Keep categories in one column so a PivotTable can total them later.

Setting it up, step by step

1. In a new workbook, type the seven headers in row 1.
2. In row 2, enter the statement date in Date, "Opening balance" in Description, and the ending balance from your most recent statement in Deposit. Mark it C, since the bank already agrees with it.
3. Click any header and turn the range into a table. Microsoft's table instructions give the steps (Home > Format as Table, or Insert > Table) and the option to confirm that your table has headers. Then rename the table Register (Excel calls it Table1 by default) so the formulas below read clearly.
4. Format Withdrawal, Deposit and Balance as Accounting or Currency, and Date as a date.
5. Freeze the header row so it stays visible as the list grows. Microsoft covers this in Freeze panes to lock rows and columns.

How do you make the running balance calculate itself?

In the first Balance cell (row 2), use a formula that adds every deposit and subtracts every withdrawal from the top of the table down to the current row:

=SUM(INDEX([Deposit],1):[@Deposit])-SUM(INDEX([Withdrawal],1):[@Withdrawal])

Press Enter and Excel copies it down the whole column. Microsoft's article on calculated columns in Excel tables explains this behavior: you enter one formula and it "will automatically expand to the rest of the column," including new rows added later.

Why this formula rather than the familiar =F2+E3-D3? The previous-row version breaks when you sort, insert or delete rows, because each cell depends on the one above it. The INDEX(...,1):[@...] range always runs from the first row of the table to the current one, so sorting by date and recalculating gives the right answer. The formula assumes the rows are in date order; sort by Date (oldest to newest) after each batch of entries.

Add the two balances you'll actually look at

Above or beside the table, add:

• Register balance (everything entered): =SUM(Register[Deposit])-SUM(Register[Withdrawal])
• Cleared balance (what the bank has processed):

=SUMIFS(Register[Deposit],Register[Cleared],"C")-SUMIFS(Register[Withdrawal],Register[Cleared],"C")

The SUMIFS function totals only the rows that meet a condition, here a C in the Cleared column. The gap between the two numbers is the total of checks and payments that haven't reached the bank yet, plus deposits still in transit.

Filling the register from your statement

Typing every transaction is where most homemade registers die. Instead, bring the statement's rows in and then mark them cleared in one go.

If your bank offers a CSV download: export the statement period, open it, and copy the date, description and amount columns into the table's next empty rows.

If you only have the PDF statement: convert it. QuickBankConvert's PDF to Excel conversion reads the statement you downloaded and returns rows of date, description, category and amount, and shows the statement's opening and closing balances above the preview so you can check the total. A free account includes Excel downloads for 3 statements a month, which covers a personal checking account; see pricing for more.

Either way, most banks give you a single signed Amount column (negative for money out), while the register has separate Withdrawal and Deposit columns. Split it with two helper formulas next to the pasted data:

• Withdrawal: =IF(C2<0,-C2,"")
• Deposit: =IF(C2>0,C2,"")

Copy the results and use Paste Special > Values into the register. If the bank's export shows negatives in parentheses or as text, the CSV formatting fixes guide shows how to convert them before splitting.

Is my register balance supposed to match the bank statement?

Not exactly, and that is the point. The cleared balance should equal the statement's ending balance once you've marked every statement line C. The full register balance will differ by whatever is still outstanding. Publication 583 lists the usual reasons the two differ: bank charges you didn't enter, and deposits or checks that didn't reach the bank before the statement date.

A monthly routine:

1. Paste the new statement's rows and split the amounts.
2. Delete any row you had already entered by hand, keeping your version if it has a better description.
3. Mark every row that appears on the statement with C.
4. Compare the cleared balance to the statement's ending balance.
5. If they differ, look for a bank fee or interest credit you haven't entered, a typo in an amount, or a duplicate.
6. Note which uncleared items are more than a month old and follow up on them.

For a more formal version with a reconciliation worksheet, see how to reconcile a checking account.

Small upgrades that pay off

• Data validation on Cleared so only C or blank is allowed.
• Conditional formatting that shades uncleared rows older than 30 days.
• A Category drop-down from a short list on a second sheet, so the same expense is always spelled the same way.
• One sheet per account, each with its own table, and a summary sheet that pulls each register balance.
• A PivotTable by Category and month for a quick spending view; the budget spreadsheet guide builds on this register.

Mistakes to avoid

• Entering withdrawals as negative numbers in the Withdrawal column. The formulas subtract that column already, so a negative there adds money.
• Starting the register mid-month without an opening balance that matches a statement.
• Leaving blank rows inside the table, which breaks sorting.
• Typing over the Balance formula in one row. Undo it, or retype the formula and Excel will offer to overwrite the column.

Related guides

• Monthly expenses spreadsheet
• How to import bank statements into Excel
• Negative numbers in parentheses in Excel