How to Build a Bank Reconciliation with Opening and Closing Balances in Excel

Managing cash flow requires accurate and timely record-keeping. When matching your internal financial records against official bank statements, a structured spreadsheet is the most reliable tool to identify missing funds or unrecorded fees. this guide explains the formulas, layout configurations, and verification steps necessary to track your funds and confirm your true cash position.

Structuring Your Data for Build a Bank Reconciliation with Opening and Closing Balances in Excel

To begin, you need a clean layout that mimics a standard bank ledger. A proper structure prevents formula errors when you begin calculating running totals. Open a blank worksheet and create your foundational column headers in row 1.

Select cell A1 and type Date. Moving across the row, label cell B1 as Transaction Description, C1 as Reference Number, D1 as Deposits, E1 as Withdrawals, and F1 as Running Balance. To properly track clearance, label cell G1 as Cleared Status. This setup isolates incoming funds from outgoing funds, which is critical for mathematical accuracy.

Formatting your numerical columns before entering data saves time later. Highlight columns D, E, and F by clicking and dragging across their column letters at the top of the sheet. Navigate to the Home tab on the top ribbon, click the Number Format dropdown, and select Accounting or Currency. This ensures all future entries display with two decimal places and standard alignment.

Build a Bank Reconciliation with Opening and Closing Balances in Excel

With your headers established, you can now input your baseline numbers and automate the running balance calculation. Follow these explicit steps to build the mathematical core of your document.

1. Log the baseline: Click cell A2 and type Opening Balance. Skip the deposit and withdrawal columns for this row. Click cell F2 and enter the starting balance from your previous month's bank statement (for example, type 5000.00). Press Enter.
2. Input the first transaction: Move down to row 3. Enter the transaction date in A3 and the description in B3. If the transaction was a received payment, enter the amount in cell D3. If it was an outgoing payment or fee, enter the amount in cell E3.
3. Apply the calculation formula: Click cell F3. Type the formula =F2+D3-E3 and press Enter. This formula instructs the spreadsheet to take the previous row's balance, add any new deposits, and subtract any new withdrawals.
4. Automate the column: Click cell F3 again to select it. Hover your mouse over the small square at the bottom-right corner of the cell until the cursor changes to a thin black crosshair. Click and drag this Fill Handle down to row 100 (or however many rows you need). The formula will dynamically adapt to each row.

Your spreadsheet will now look similar to this structure:

Date	Transaction Description	Deposits	Withdrawals	Running Balance
01/01/2024	Opening Balance			$5,000.00
01/05/2024	Client Payment	$1,200.00		$6,200.00
01/08/2024	Office Supplies		$300.00	$5,900.00

Identifying Discrepancies in Build a Bank Reconciliation with Opening and Closing Balances in Excel

Once your running balance is calculated for all internal transactions, verify it against the actual closing balance on your official bank document. If the final number in column F does not match your bank statement exactly, you have outstanding items or missing entries.

Place your physical or digital bank statement next to your screen. Read down the bank statement line by line. For every transaction that has cleared the bank, click into the corresponding row in your spreadsheet's Cleared Status column (Column G) and type the letter Y.

Next, you will generate an Adjusted Bank Balance that ignores pending checks and deposits in transit. Click into an empty cell outside your main data (for example, J2) and enter this specific formula: =F2+SUMIF(G:G,"Y",D:D)-SUMIF(G:G,"Y",E:E). This takes your exact starting balance, adds only the deposits marked "Y", and subtracts only the withdrawals marked "Y". The resulting number must correctly match the final balance printed on your bank statement. If it does not, you are either missing a bank fee in your spreadsheet or you have mistyped a transaction amount.

Build a Bank Reconciliation with Opening and Closing Balances in Excel with WPS Spreadsheets

If you are managing finances on a device that struggles with heavy software subscriptions, WPS Office provides a highly capable, lightweight environment for this exact financial workflow. You do not need to rely on complex Microsoft 365 cloud licensing to build professional accounting ledgers. WPS Office natively creates and edits .xlsx files, ensuring your formulas and formatting remain correctly intact if you share the document with accountants using different software.

Most banks export monthly statements exclusively as PDF files. Manually typing hundreds of transaction rows from a PDF into your ledger is slow and highly prone to keystroke errors. You can bypass this manual entry entirely by using the WPS PDF-to-Excel tool. Open your downloaded bank statement PDF directly within the WPS Office application. Navigate to the top ribbon, click the Tools tab, and select PDF to Excel. The integrated AI engine parses the financial tables and converts them into structured spreadsheet columns. You can then copy these accurate deposit and withdrawal figures directly into your reconciliation template, allowing your =F2+D3-E3 formula to instantly calculate the true closing balance without human data entry errors.

FAQs

Why does my calculated Excel closing balance differ from the official bank statement?

This discrepancy is almost always caused by timing differences. You have likely recorded a written check or a pending customer deposit in your internal spreadsheet, but the bank has not yet processed the transaction on their end. By implementing the "Cleared Status" column and using a SUMIF formula to calculate only cleared items, you can filter out pending transactions and match the bank's exact closing number.

What is the most reliable formula to calculate a running balance?

The standard and most stable method uses simple arithmetic referencing the cell immediately above the current row. Assuming row 2 holds your opening balance, you type =F2+D3-E3 into cell F3 (where D3 is the current deposit and E3 is the current withdrawal). Dragging this formula down by the Fill Handle ensures every row dynamically calculates based on the previous row's verified total.

How do I handle an opening balance of zero for a brand-new account?

If you are reconciling a newly opened checking account, click into your designated starting balance cell (e.g., F2) and type 0. The running balance formula will continue to function normally, building your account's financial history entirely from your first recorded initial deposits.

How can I automatically highlight uncleared transactions in my ledger?

You can use Conditional Formatting to visually flag pending items. Highlight your entire transaction table. Navigate to the Home tab, click Conditional Formatting, select New Rule, and choose Use a formula to determine which cells to format. Enter the formula =$G3<>"Y" (assuming column G holds your cleared status and your data starts on row 3). Click the Format button, select a light red fill color, and apply it. Every row that lacks a "Y" will instantly turn red, indicating it has not yet cleared the bank.