Bank Reconciliations

Hi Harry435​,

Steps to build a Cashflow Calculator for bank reconciliations.

The goal was to:

• Track monthly income and expenses over 12 months
• Add new rows above the Total line
• Have totals auto-update without needing formula edits
• Make the spreadsheet usable by novices
• Lock formulas to prevent accidental changes
• Provide a historical view of cashflow trends

I have built and tested a solution that meets all these needs.

Here's how it works:

Spreadsheet Structure:

• Details column for income/expense item names
• 12 monthly columns (January to December)
• Row Total column: auto-calculates sum of monthly values
• % column: shows each row’s share of the grand total
• Grand Totals for Income and Expenses
• Bank Reconciliation section with Opening Balance, Income, Expenses, Difference, and Closing Balance

Key Features:

• Excel Tables: Income and Expense sections are formatted as tables. When users insert a new row, totals auto-update.
• Structured Formulas: Grand Totals use =SUM(TableName[Row Total]) so they expand dynamically.
• Formula Protection: All formula cells are locked. Sheet protection allows only row insertion and data entry.
• Novice-Friendly: Users only need to add a row and enter monthly values. Everything else updates automatically.
• Historical View: The 12-month layout helps users track trends in their bank balance and spending habits.

How to Use:

1. Add a new row under Income or Expenses
2. Enter your item name in the “Details” column
3. Fill in monthly values (Jan–Dec)
4. Row Totals and % will update automatically
5. Bank balances and summaries reflect changes instantly

If you have feedback kindly reach out

This tool is designed to empower users with minimal spreadsheet knowledge to manage their finances confidently.

Thanks for reading! —Olufemi

I appreciate what my on-line friend< Riny_van_Eekelen​ , has done to help with your approach.

My own suggestion would be to radically change your approach, or at least to consider changing. Excel can do virtually all of the summarizing for you, of income in various categories and expenses in various categories, by means of the Pivot Table. All you (or your novice users) would need to do is enter each transaction, by date, showing whether income or expense, and budget-category...i.e., a transactional history in the form of a database. (I will point out that your approach is already asking the user to do the monthly summary "off-line", introducing the possibility of careless mistakes or overly casual "eye-ball" summaries..)

I'm attaching a very simple example for your consideration. This highly simplified example is based on a spreadsheet I use myself for tracking our own income and expenses. I have a more complicated (two to three levels of sub-categories) set of budget categories. The main point is that Excel's Pivot Table does all the summarizing. No formulas are needed. The Pivot Table, once discovered, becomes a favorite of many Excel users. In addition to that link I've already given, you'll find a wealth of "how-to" videos on YouTube.

Harry435​ 

Yes, that works fine!

I believe you can use the suggested OFFSET option. By the way, do you have any particular reason to use formulas as =SUM(B10) or =SUM(B49+B50-B51). No need for the SUM function here.

I find it difficult to visualize a spreadsheet based on a textual description. Please upload or share a file with some dummy data so that we can see what you are dealing with.

I'm guessing that you are NOT working with a structured table. Let's assume that you have a Total formula =SUM(G1:G49) in G50. Then you can't insert row 50 and expand regular SUM function automatically to include the new row 50.

One way would be allow for a growing sum range by including an empty row. So, in the above, example, row 49 would be empty and then you insert a row above it. Then the sum range becomes G1:G50.

A formula solution would be to insert an OFFSET in the SUM function like this:

=SUM(G1:OFFSET(G50,-1,0))

Now you can insert a row above row 50 and the OFFSET will make sum range to end one row above (the -1) the row where the formula sits.

Alternatively, use a structured table with a Total row. That will allow you to insert a row below the last row with data and the sum will update automatically.