VBA & Macro Problems

How to Create a Running Total in Excel Using One Input Cell

Khadija Khan Sep 28, 2026 869 views

Question details

The user wants every new value entered into a single input cell (e.g., A1) to automatically add to an existing total in another cell (e.g., A2), without using additional rows or creating a data table.

How to Create a Running Total in Excel Using One Input Cell

Product: Excel
Device & OS: not provided
Scenario: Continuous data entry where a single cell acts as an input field, and another cell acts as an ongoing accumulator or running total.
Observed behavior: By default, entering a new value overwrites the previous value, and standard Excel formulas cannot add a cell's new value to its own previous total without triggering a circular reference error.

Before you start

Ensure your organization permits the use of macros, as the most reliable method for creating a persistent running total requires VBA and saving your file as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use VBA Worksheet_Change Event

The most robust way to keep a running total without encountering circular reference errors is by using a short VBA script.

This method uses VBA to detect when the specific input cell (A1) changes. It then adds that new value to the total cell (A2) while temporarily disabling events to prevent Excel from freezing in an infinite loop.

Open the VBA Editor

Right-click the worksheet tab at the bottom of your Excel window and select 'View Code' from the context menu.

Paste the VBA Code

In the code window that appears, paste the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1")) Is Nothing Then Application.EnableEvents = False Me.Range("A2").Value = Me.Range("A2").Value + Me.Range("A1").Value Application.EnableEvents = True End If End Sub

Test the Running Total

Close the VBA editor and return to your worksheet. Type a number into cell A1 and press Enter. Cell A2 will automatically update to include this new number.

Save as a Macro-Enabled Workbook

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown. If you save it as a standard .xlsx file, the VBA code will be lost.

Macro Security: You must click 'Enable Content' when reopening the workbook in the future. If macros are restricted by your organization's IT policy, this method will not work.

Solution 2

Enable Iterative Calculations (No VBA)

If macros are restricted or disabled, you can force Excel to calculate a circular reference deliberately by turning on iterative calculations.

Advanced Spreadsheets Made Easy

Handle Macros and Formulas Effortlessly with WPS Office

WPS Spreadsheet fully supports VBA macros (.xlsm) and advanced formula settings like iterative calculations, allowing you to create dynamic running totals smoothly.

1. Open WPS Spreadsheet: Launch WPS Office and open your current workbook or create a new blank spreadsheet.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the VBA editor.
3. Insert the Code: Double-click your active sheet in the left panel and paste the Worksheet_Change code into the module.
4. Save Your Work: Go to Menu > Save As, and select 'Microsoft Excel Macro-Enabled Workbook (*.xlsm)' to keep your automations active.

Seamless compatibility with Microsoft Excel (.xlsx and .xlsm) formats.Built-in Visual Basic editor to automate data entry and run complex scripts.Lightweight application with a familiar, easy-to-navigate user interface.Free to use for everyday office productivity tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why did my running total stop working when I reopened the file?

If you used the VBA method, macros are often disabled for security reasons when you reopen a file. Ensure you saved the workbook as an .xlsm file, and remember to click 'Enable Content' in the yellow security warning bar at the top of the screen.

Can I clear the running total and start over?

Yes. Simply click on the total cell (e.g., A2) and press the Delete key to clear the current value. The running total will start accumulating from zero upon your next entry in the input cell.

Why does the iterative calculation method show a circular reference warning initially?

If you enter the formula =A1+A2 before enabling iterative calculations, Excel immediately flags it as a circular reference. You must enable 'Iterative calculation' in Excel Options before writing the formula to avoid this warning.

What happens if I type text instead of a number in the input cell?

If you type text into the input cell using the VBA method, it will trigger a 'Type Mismatch' error because Excel cannot mathematically add a word to a number. You should ensure only numeric values are entered, or add error-handling code to your VBA script.

Excel & Data Problems