Excel Same-Cell Running Total: Formula Alternative and VBA

If you overwrite a cell, a worksheet formula cannot still know the overwritten value. If A1 used to contain 2 and you type 3 into A1, the old 2 is gone unless you kept it somewhere else. For dependable work, keep a record column and calculate a running total from the records.

Microsoft’s running-total article uses the same principle: source values stay in cells, and the total formula expands as new rows are added: calculate a running total in Excel.

Recommended method: keep the entries

Cell	Entry or formula	Result
A2	2	first entry
A3	3	second entry
B2	`=SUM($A$2:A2)`	2
B3	`=SUM($A$2:A3)`	5

If you then enter -2 in A4, B4 becomes 2+3-2=3. This leaves an audit trail, which a same-cell design cannot provide by itself.

Why circular formulas are a special case

A formula that refers to its own cell creates a circular reference. Microsoft documents how circular references work and how iterative calculation can be enabled: remove or allow a circular reference in Excel and change formula recalculation, iteration, or precision in Excel. That approach is easy to misconfigure, so the example below uses stored state instead.

Advanced example: one target workbook, one worksheet, one input cell

This macro example is intentionally narrow: it is for one workbook that contains the VBA code, one target worksheet in that workbook, and one accumulator cell, A1. Setup checks that the active sheet belongs to ThisWorkbook before it creates or writes state. It also validates A1 before preparing the hidden state sheet, so an invalid starting value cannot leave behind a blank state sheet that blocks the next setup attempt.

During entry, the existing state sheet is trusted only after A1 equals the marker and B1 equals the target sheet CodeName. All error logging and Visible changes are guarded by that trusted-state flag. If a sheet named _accumulator_state already exists but is not this example’s state sheet, setup refuses to write to it. If the same sheet is already initialized, setup refuses to overwrite it; run ResetAccumulatorForActiveSheet only when you intentionally want to replace the stored total with the current A1 value.

Microsoft documents that Worksheet.Change runs when cells are changed by the user or an external link, and that Application.EnableEvents can prevent event procedures from recursively triggering themselves.

Save the workbook as .xlsm.
Put the VBA code in the same workbook that contains the target worksheet.
On the target sheet, put the starting visible total in A1, such as 2, or leave A1 blank for zero.
Press Alt+F11, choose Insert > Module, and paste the standard-module code. Microsoft’s VBA introduction covers the editor/module workflow: getting started with VBA in Office.
Run SetupAccumulatorForActiveSheet once while the target worksheet is active. Save the workbook immediately after setup so the hidden stored total is persisted.
Paste the worksheet code into that same target worksheet’s code module, not into a standard module.

Standard module code

Option Explicit

Public Const ACC_INPUT_CELL As String = "A1"
Public Const ACC_STATE_SHEET As String = "_accumulator_state"
Public Const ACC_MAGIC As String = "SAME_CELL_ACCUMULATOR_V5"

Public Sub SetupAccumulatorForActiveSheet()
    InitAccumulatorForActiveSheet False
End Sub

Public Sub ResetAccumulatorForActiveSheet()
    InitAccumulatorForActiveSheet True
End Sub

Private Sub InitAccumulatorForActiveSheet(ByVal allowReset As Boolean)
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim stateWs As Worksheet
    Dim initialValue As Variant
    Dim initialTotal As Double
    Dim oldEvents As Boolean
    Dim eNum As Long, eDesc As String
    Dim trustedState As Boolean

    If ActiveSheet Is Nothing Then Exit Sub
    If TypeName(ActiveSheet) <> "Worksheet" Then
        MsgBox "Run setup from the worksheet that contains " & ACC_INPUT_CELL & ".", vbExclamation
        Exit Sub
    End If

    Set ws = ActiveSheet
    Set wb = ws.Parent

    'This example must be pasted into the same workbook that contains the target sheet.
    If Not wb Is ThisWorkbook Then
        MsgBox "The active sheet is not in the workbook that contains this VBA code. Activate the target sheet in this workbook, then run setup again.", vbCritical
        Exit Sub
    End If

    'Validate the visible starting value before creating or touching the state sheet.
    On Error GoTo ValidateFail
    If ws.Range(ACC_INPUT_CELL).HasFormula Then Err.Raise vbObjectError + 699, , ACC_INPUT_CELL & " must not contain a formula for this accumulator."
    initialValue = ws.Range(ACC_INPUT_CELL).Value2
    If IsError(initialValue) Then Err.Raise vbObjectError + 700, , ACC_INPUT_CELL & " contains an Excel error value."
    If Len(CStr(initialValue)) = 0 Then
        initialTotal = 0
    ElseIf IsNumeric(initialValue) Then
        initialTotal = CDbl(initialValue)
    Else
        Err.Raise vbObjectError + 701, , ACC_INPUT_CELL & " must be blank or numeric before setup."
    End If
    On Error GoTo 0

    oldEvents = Application.EnableEvents
    On Error GoTo Fail
    Application.EnableEvents = False

    Set stateWs = PrepareAccumulatorStateSheet(wb, ws, allowReset)
    trustedState = True

    stateWs.Range("A1").Value = ACC_MAGIC
    stateWs.Range("B1").Value = ws.CodeName
    stateWs.Range("A2").Value = "stored_total"
    stateWs.Range("B2").Value = initialTotal
    stateWs.Range("A3").Value = "last_error"
    stateWs.Range("B3").ClearContents
    stateWs.Range("A4").Value = "last_updated"
    stateWs.Range("B4").Value = Now
    stateWs.Visible = xlSheetVeryHidden

    If Len(CStr(initialValue)) = 0 Then
        ws.Range(ACC_INPUT_CELL).ClearContents
    Else
        ws.Range(ACC_INPUT_CELL).Value = initialTotal
    End If

    Application.EnableEvents = oldEvents
    MsgBox "Accumulator initialized in this workbook for sheet code name " & ws.CodeName & ". Save the workbook now so the stored total is persisted.", vbInformation
    Exit Sub

ValidateFail:
    eNum = Err.Number
    eDesc = Err.Description
    MsgBox "Setup failed before creating or changing any state sheet: " & eDesc, vbCritical
    Exit Sub

Fail:
    eNum = Err.Number
    eDesc = Err.Description
    On Error Resume Next
    If trustedState Then
        stateWs.Range("B3").Value = "Setup error " & eNum & ": " & eDesc
        stateWs.Visible = xlSheetVeryHidden
    End If
    Application.EnableEvents = oldEvents
    MsgBox "Setup failed: " & eDesc, vbCritical
End Sub

Private Function PrepareAccumulatorStateSheet(ByVal wb As Workbook, ByVal ws As Worksheet, ByVal allowReset As Boolean) As Worksheet
    Dim stateWs As Worksheet
    Set stateWs = GetAccumulatorState(wb)

    If stateWs Is Nothing Then
        Set stateWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        stateWs.Name = ACC_STATE_SHEET
        Set PrepareAccumulatorStateSheet = stateWs
        Exit Function
    End If

    If stateWs.Range("A1").Value <> ACC_MAGIC Then
        Err.Raise vbObjectError + 710, , "A sheet named " & ACC_STATE_SHEET & " already exists but is not this accumulator state sheet. Rename that sheet or change the code before setup."
    End If

    If stateWs.Range("B1").Value <> ws.CodeName Then
        Err.Raise vbObjectError + 711, , "The existing accumulator state is bound to sheet code name " & stateWs.Range("B1").Value & ", not " & ws.CodeName & ". Setup did not overwrite it."
    End If

    If Not allowReset Then
        Err.Raise vbObjectError + 712, , "This sheet is already initialized. To intentionally replace its stored total with the current " & ACC_INPUT_CELL & " value, run ResetAccumulatorForActiveSheet."
    End If

    Set PrepareAccumulatorStateSheet = stateWs
End Function

Public Function GetAccumulatorState(ByVal wb As Workbook) As Worksheet
    On Error Resume Next
    Set GetAccumulatorState = wb.Worksheets(ACC_STATE_SHEET)
    On Error GoTo 0
End Function

Target worksheet code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim stateWs As Worksheet
    Dim watchCell As Range
    Dim priorValue As Variant
    Dim priorTotal As Double
    Dim newEntry As Variant
    Dim newTotal As Double
    Dim oldEvents As Boolean
    Dim havePrior As Boolean
    Dim stateWritten As Boolean
    Dim trustedState As Boolean
    Dim failureText As String
    Dim eNum As Long, eDesc As String

    Set watchCell = Me.Range(ACC_INPUT_CELL)
    If Intersect(Target, watchCell) Is Nothing Then Exit Sub

    oldEvents = Application.EnableEvents
    On Error GoTo UnexpectedFail
    Application.EnableEvents = False

    Set stateWs = GetAccumulatorState(Me.Parent)
    If stateWs Is Nothing Then
        failureText = "Accumulator state sheet is missing. No replacement total was invented. Restore from the last saved workbook or an entry log."
        GoTo FailClosedNoTrustedState
    End If

    If stateWs.Range("A1").Value <> ACC_MAGIC Or stateWs.Range("B1").Value <> Me.CodeName Then
        failureText = "Accumulator state is missing, damaged, or bound to another worksheet. No total was changed intentionally."
        GoTo FailClosedNoTrustedState
    End If
    trustedState = True

    priorValue = stateWs.Range("B2").Value2
    If IsError(priorValue) Or Not IsNumeric(priorValue) Then
        failureText = "Stored total is missing or damaged. No replacement total was invented."
        GoTo FailClosedTrustedState
    End If
    priorTotal = CDbl(priorValue)
    havePrior = True

    If Target.CountLarge > 1 Then
        If Not TrySetCellValue(watchCell, priorTotal, eDesc) Then
            failureText = "Only edit " & ACC_INPUT_CELL & " by itself. Restore to the prior total failed: " & eDesc
            GoTo FailClosedTrustedState
        End If
        failureText = "Only edit " & ACC_INPUT_CELL & " by itself. The accumulator cell was restored from the prior stored total."
        GoTo FailClosedTrustedState
    End If

    If watchCell.HasFormula Then
        If Not TrySetCellValue(watchCell, priorTotal, eDesc) Then
            failureText = "Formula input was rejected, and restoring the prior total failed: " & eDesc
            GoTo FailClosedTrustedState
        End If
        failureText = "Do not enter a formula in " & ACC_INPUT_CELL & ". The accumulator cell was restored."
        GoTo FailClosedTrustedState
    End If

    newEntry = watchCell.Value2
    If IsError(newEntry) Then
        If Not TrySetCellValue(watchCell, priorTotal, eDesc) Then
            failureText = "The new entry is an Excel error value, and restoring the prior total failed: " & eDesc
            GoTo FailClosedTrustedState
        End If
        failureText = "The new entry is an Excel error value. The accumulator cell was restored."
        GoTo FailClosedTrustedState
    End If

    If Len(CStr(newEntry)) = 0 Then
        newTotal = 0
    ElseIf IsNumeric(newEntry) Then
        newTotal = priorTotal + CDbl(newEntry)
    Else
        If Not TrySetCellValue(watchCell, priorTotal, eDesc) Then
            failureText = "Nonnumeric input was rejected, and restoring the prior total failed: " & eDesc
            GoTo FailClosedTrustedState
        End If
        failureText = "Enter a number in " & ACC_INPUT_CELL & ". The accumulator cell was restored."
        GoTo FailClosedTrustedState
    End If

    On Error GoTo StateWriteFail
    stateWs.Range("B2").Value = newTotal
    stateWritten = True

    On Error GoTo VisibleWriteFail
    If Len(CStr(newEntry)) = 0 Then
        watchCell.ClearContents
    Else
        watchCell.Value = newTotal
    End If

    On Error Resume Next
    If trustedState Then
        stateWs.Range("B3").ClearContents
        stateWs.Range("B4").Value = Now
        stateWs.Visible = xlSheetVeryHidden
    End If
    Application.EnableEvents = oldEvents
    Exit Sub

StateWriteFail:
    eNum = Err.Number
    eDesc = Err.Description
    On Error Resume Next
    If havePrior Then watchCell.Value = priorTotal
    failureText = "The stored total could not be updated (" & eNum & ": " & eDesc & "). The visible cell was restored only if that best-effort write succeeded; check it before continuing."
    GoTo ReportTrustedState

VisibleWriteFail:
    eNum = Err.Number
    eDesc = Err.Description
    On Error Resume Next
    If stateWritten And trustedState Then stateWs.Range("B2").Value = priorTotal
    failureText = "The stored total was written but the visible cell could not be updated (" & eNum & ": " & eDesc & "). The code attempted to put the stored total back to the prior value; verify both A1 and the hidden state before continuing."
    GoTo ReportTrustedState

FailClosedNoTrustedState:
    On Error Resume Next
    Application.EnableEvents = oldEvents
    MsgBox failureText, vbCritical
    Exit Sub

FailClosedTrustedState:
    On Error Resume Next
    If trustedState Then
        stateWs.Range("B3").Value = failureText
        stateWs.Visible = xlSheetVeryHidden
    End If
    Application.EnableEvents = oldEvents
    MsgBox failureText, vbExclamation
    Exit Sub

ReportTrustedState:
    On Error Resume Next
    If trustedState Then
        stateWs.Range("B3").Value = failureText
        stateWs.Visible = xlSheetVeryHidden
    End If
    Application.EnableEvents = oldEvents
    MsgBox failureText, vbCritical
    Exit Sub

UnexpectedFail:
    eNum = Err.Number
    eDesc = Err.Description
    On Error Resume Next
    If havePrior Then watchCell.Value = priorTotal
    failureText = "The accumulator stopped before finishing (" & eNum & ": " & eDesc & ")."
    If trustedState Then
        stateWs.Range("B3").Value = failureText
        stateWs.Visible = xlSheetVeryHidden
    End If
    Application.EnableEvents = oldEvents
    MsgBox failureText, vbCritical
End Sub

Private Function TrySetCellValue(ByVal targetCell As Range, ByVal valueToWrite As Variant, ByRef errText As String) As Boolean
    On Error GoTo Fail
    targetCell.Value = valueToWrite
    TrySetCellValue = True
    Exit Function
Fail:
    errText = Err.Description
    TrySetCellValue = False
End Function

What this macro does and does not guarantee

Normal case: after setup with A1 = 2, typing 3 in A1 reads prior total 2, stores new total 5, and writes 5 to A1. Typing -2 next stores and displays 3.
State missing, damaged, wrong sheet, or untrusted: the code does not create a new zero state during an edit, does not log into an untrusted same-name sheet, and does not hide a same-name sheet unless its marker and sheet binding both match.
State write failure: if the hidden stored total cannot be written, the code does not write the new total to A1. It tries to put the prior total back in A1 and reports that the visible value must be checked.
Visible write failure after state write: if the stored total was written but A1 could not be updated, the code tries to put the hidden total back to the prior value and reports that both A1 and hidden state need verification. This is best-effort compensation, not a database transaction.
Clear A1 to reset: deleting the contents of A1 deliberately sets the stored total to zero and leaves A1 blank. The next numeric entry starts a new total; clearing A1 is not an undo operation.
Multi-cell paste: if a paste includes A1 and the stored prior total is readable, A1 is restored from the prior total and the edit is rejected. Other cells included in the paste are not rolled back.
Formula input: the code rejects formulas in A1. The event runs on user edits, not on later recalculation, so A1 should be a typed-value cell only.
Macro disabled risk: if macros are disabled and someone edits A1, the visible value can diverge from the stored state. Re-enable macros, inspect the hidden state, and reconcile from records before continuing.
Save requirement: the hidden state persists only after the workbook is saved. If Excel closes without saving after a new entry, the workbook can reopen with an older stored total.
Testing status: this environment did not provide a local Excel application. The code received a lightweight static block-balance check and was reviewed against the cited Microsoft event documentation; it was not executed in Excel.