digest
Short Summary
Main recommendation: Build a running-balance bank reconciliation in Excel using single-cell formulas that combine IF, ISNUMBER, and SUM, with a Data Validation dropdown restricting the reconciliation column to "Y". Separate formulas calculate the "My Book Balance" and the "Bank Balance," and each updates automatically when a transaction is marked "Y" or a date is entered. The video explicitly defers the dynamic spilled array version to the next video (1922).
Key Points
- A Data Validation list limited to the letter "Y" prevents typing errors like "y " or "x" that would break the reconciliation formulas.
- SUM is used because it ignores text, so the running balance can add and subtract amounts while skipping the "Y" text in the reconciliation column.
- ISNUMBER checks whether a date exists in the row; if so, the formula runs, and if not, it returns an empty text string so nothing appears.
- The bank balance formula uses two nested IF functions: an outer IF checks ISNUMBER on the date, and an inner IF subtracts the amount only when the reconciliation cell equals "Y", otherwise carrying forward the previous balance.
- Ctrl+Enter enters the formula, and the fill handle crosshair (which he jokes Bill Gates calls the crosshair but he nicknames the "angry rabbit") copies formulas down; double-clicking it fills to the end of adjacent data.
Details Worth Noting
- Concrete instructions:
- Data Validation: go to Data > Data Tools > Data Validation, set Allow to List, and type Y as the source (no quotes needed). Click OK to create the dropdown.
- My Book Balance: in the first balance cell, enter =IF(ISNUMBER(date cell), SUM(previous balance cell, in cell, -out cell), ""), where the in and out cells are the deposit and withdrawal columns for that row. Press Ctrl+Enter, then double-click the fill handle to copy down.
- Bank Balance: enter =IF(G4="Y", SUM(previous bank balance, -out cell), previous bank balance) for the first condition, and wrap it in an outer IF(ISNUMBER(date cell), ..., "") so the bank balance only shows on rows with a date. Press Ctrl+Enter, then double-click the fill handle to fill down.
- Test the setup by entering a date and an amount to confirm the book balance appears and updates, then marking a row "Y" to confirm the bank balance updates.
- The formulas use relative cell references, so copying them down makes each row refer to the row above and the row being evaluated.
- The video's "angry rabbit" is the speaker's nickname for the fill handle crosshair; the transcript also gives a tip to highlight a cell touching adjacent data so the double-click fill extends correctly.
- The method handles outstanding items on either side: book-only transactions are captured in the book balance, and bank-only transactions are reflected once marked "Y".
Bottom Line
Single-cell IF, ISNUMBER, and SUM formulas, paired with a "Y" dropdown, give a self-updating two-balance reconciliation that is easy to audit; the dynamic spilled array version is covered in the next video.
