Set up a spreadsheet that matches your bank statement

The simplest way to balance a checking account in Excel is to create a list of every transaction your bank shows, then compare it to what you wrote down. Start with a blank spreadsheet. In the first row, create column headers: Date, Description, Withdrawal (money out), Deposit (money in), and Running Balance.

Open your most recent bank statement — the one your bank mailed or emailed you. This statement shows every transaction for a specific period, usually one month. You will enter each transaction from that statement into your spreadsheet, row by row, in the order the bank lists them.

The reason you use the bank's list, not your own checkbook or transaction records, is that the bank's version is the official record. Your personal records might have timing differences — a check you wrote last month might not have cleared until this month, for example. The bank statement shows what actually happened in your account.

Key Takeaways

  • Create five columns in Excel: Date, Description, Withdrawal, Deposit, and Running Balance, then enter every transaction from your bank statement.
  • Use a formula to calculate your running balance automatically: if money came in, add it; if money went out, subtract it.
  • Your final running balance in Excel should match the "ending balance" number on your bank statement.
  • If the numbers do not match, look for transactions you missed, amounts you entered wrong, or deposits and withdrawals you confused.
  • Once your spreadsheet balances, you can use the same file each month by clearing old transactions and entering new ones.

Enter transactions in the order the bank lists them

In column A (Date), type the date of the first transaction. In column B (Description), write what the transaction was — for example, "Debit card purchase at grocery store" or "Direct deposit paycheck". In column C (Withdrawal), enter the amount if money left your account. In column D (Deposit), enter the amount if money came in. Leave one column blank if the transaction is only a withdrawal or only a deposit.

Work through your entire bank statement this way. Do not skip transactions, even small ones. A 50-cent fee or a $1.25 ATM charge matters because it changes your balance. If you miss even one transaction, your final number will not match the bank's.

Some transactions might be labeled in ways you do not recognize — "ACH debit" or "wire transfer" or "NSF fee". Your bank statement should explain what these mean, usually in a legend at the bottom. If it does not, you can call your bank's customer service line and ask what a specific transaction was.

Use a formula to calculate your running balance automatically

In column E (Running Balance), you will create a formula that adds and subtracts as you go. In the first row of data, type this formula: =starting balance + D2 - C2. Replace "starting balance" with the opening balance from your bank statement — the amount you had at the start of the period the statement covers.

For example, if your statement says you started with $1,500, and your first transaction is a $200 deposit, your formula in E2 would be: =1500+D2-C2. When you press Enter, Excel will show $1,700.

In the next row (E3), type: =E2+D3-C3. This tells Excel to take the balance from the row above, add any deposit in this row, and subtract any withdrawal. Copy this formula down to every row with a transaction. Excel will automatically adjust the row numbers as it copies — E4 will become =E3+D4-C4, and so on.

Compare your final balance to the bank statement

When you have entered all transactions, look at the last number in your Running Balance column. This should match the "ending balance" or "statement balance" shown on your bank statement. If it does, your account is balanced — you have found every transaction and entered it correctly.

If the numbers do not match, the difference tells you where to look. Subtract the smaller number from the larger one. For example, if your spreadsheet shows $2,450 but the bank shows $2,500, you are missing $50 somewhere. Look for a transaction you might have skipped, or check whether you entered an amount backwards (a withdrawal as a deposit, or vice versa).

Go through your bank statement line by line and compare it to your spreadsheet. Check that every amount is correct and that you did not accidentally flip a deposit and withdrawal. This usually takes just a few minutes.

Save your spreadsheet and reuse it each month

Once your spreadsheet balances, save it with a name like "Checking Account Balance 2024" or "Monthly Bank Reconciliation". You can reuse this file every month instead of starting from scratch.

To use it again next month, delete all the old transactions but keep the column headers and formulas. Enter your new bank statement's opening balance in the first formula, then add the new month's transactions. The formulas will recalculate automatically.

Some people keep all months in one file by adding a new set of columns for each month, or by creating a separate sheet within the same file for each month. Either way works — use whatever is easiest for you to find later.

What to do if you find a bank error

If you balance your spreadsheet correctly but the number still does not match the bank statement, the error might be on the bank's side. This is rare, but it happens. Before you call, make sure you have checked your own work thoroughly — look for transposed numbers (typing 123 instead of 132), deposits you entered as withdrawals, or transactions you missed entirely.

If you are certain your spreadsheet is correct, contact your bank's customer service. Tell them the date range of the statement, the amount of the difference, and whether the bank's balance is higher or lower than yours. The bank can review the statement and correct it if there was an error on their end. This process usually takes a few business days.

Frequently Asked Questions

Do I need to include pending transactions?

No. A bank statement shows only transactions that have cleared — money that actually moved. Pending transactions are on your way but have not finished processing yet. Balance your account against the official statement, not against pending items. Once a pending transaction clears, it will appear on the next statement.

What if I wrote a check that has not cleared yet?

If a check you wrote is not on your bank statement, do not include it in your spreadsheet. The bank has not processed it yet. Once it clears, it will show up on a future statement. Your spreadsheet will always match the bank statement because you are using the bank statement as your source.

Can I use Google Sheets instead of Excel?

Yes. Google Sheets works the same way — you create the same columns, enter the same data, and use the same formulas. The formulas are identical. If you use Google Sheets, you can access your spreadsheet from any device with internet, which some people find more convenient than Excel.

What if I have multiple checking accounts?

Create a separate spreadsheet for each account. Name each file clearly — "Checking Account 1 Balance" and "Checking Account 2 Balance" — so you do not mix them up. Balance each account against its own bank statement.

How often should I balance my account?

Most people balance once a month when the bank statement arrives. Some balance weekly or after large transactions. There is no rule — do it as often as it helps you feel confident about your money. Monthly is standard because that is when your bank sends an official statement.