create running total in a table with opening balance

Dom 996 Reputation points
2026-10-21T17:57:01.4433333+00:00

I have a table that resembles a check book register. There are charges and deposits. There is a different table for each month - each on a separate tab. Each month starts with an opening balance and the running total takes the total from the cell above and adds the current transaction. For this to work, I have to hard code the opening balance in the first cell and then use a formula for the rest of the cells in the table column. Here is a sample of the table structure:

User's image

This seems like a crude way of doing this. Is there a more elegant way of accomplishing this? Using different formulas? Should I be using a table at all?

Any advice would help. Thanks.

Microsoft 365 and Office | Excel | For home | Windows
Answer accepted by question author

AI answer

2026-10-21T17:58:02.4733333+00:00

Keep the Excel table, move the opening balance to a separate cell, and use one consistent cumulative formula for every row in the Running column.

Assume:

  • The opening balance is in H1
  • Amount is column B
  • The first transaction is in row 2
  • Running is column D

Enter the opening balance in H1, then enter this formula in the first data cell of Running:

=$H$1+SUM($B$2:B2)

As Excel fills the formula down, it becomes:

=$H$1+SUM($B$2:B3)
=$H$1+SUM($B$2:B4)
=$H$1+SUM($B$2:B5)

$B$2 remains fixed while the end of the range expands, producing the running balance. This is the cumulative-range pattern Microsoft documents for running totals.^1^

Enter only actual charges and deposits in the table:

Date Amount Note Running
10/8/2026 -50.00 Bill 1 1276.59
10/8/2026 -51.18 Bill 2 1225.41
10/8/2026 17.68 Deposit 1 1243.09

This avoids treating the opening balance as both a transaction and a special first-row result.

Make the table fill the formula automatically

  1. Put the opening balance outside the table.
  2. Clear the entire existing Running column, including its hard-coded first value.
  3. Enter the formula once in the first data row.
  4. Press Enter.

Excel should create a calculated column and fill the formula through the entire table. New rows should then inherit it automatically.^2^

The hard-coded first balance is what makes the column inconsistent: one cell contains a value while the others contain formulas. A matching Microsoft Q&A case resolved the table autofill problem by clearing the Balance column and entering one consistent formula for the whole column.^3^

One table instead of separate monthly tables

For a check register, a single transaction table is generally easier to maintain:

  • Add every transaction chronologically.
  • Keep one opening balance outside the table.
  • Use the Date column’s filter to display a particular month.
  • Allow the running balance to continue across month boundaries.

Excel tables and their structured references automatically expand and contract as rows are added or removed, and formulas embedded in tables expand with the data.^4^

If separate monthly tabs are required for presentation, the same uniform-formula design can be used on each sheet, with that month’s opening balance placed outside its table. The opening balance can then be entered once or linked to the preceding month’s closing-balance cell.

Microsoft 365 alternative: one spilling formula

Because this is Microsoft 365, SCAN can calculate all intermediate balances from one formula. Place it in a normal worksheet range outside the table:

=SCAN(
    $H$1,
    Table1[Amount],
    LAMBDA(balance,amount,balance+amount)
)

SCAN accepts an initial value, applies the calculation to each amount, and returns every intermediate result.^5^

For a conventional table, however, the cumulative SUM formula is simpler and keeps the Running column inside the table.

References

  1. Calculate a running total in Excel
  2. Use calculated columns in an Excel table | Microsoft Support
  3. Excel formula not expanding in tables - Microsoft Q&A
  4. Excel performance: Tips for optimizing performance obstructions
  5. SCAN function
AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. riny 21,600 Reputation points Volunteer Moderator
    2026-10-21T19:24:46.4866667+00:00

    @Dom

    You chose to accept an AI answer offering two solutions. One is rather clumsy and the other overly complicated in my opinion. I don't know the name of your table, but let's say it is "Table1" (as AI did), then the following formula in the first cell of the "Running" column can be:

    =SUM(Table1[[#Headers],[Amount]]:[@Amount])

    It will copy itself down the column automatically. Try and judge for yourself.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.