Fix Excel Table Running Total Formula Changes After Adding New Rows
Question details
The user needs to stop their running total formula from automatically altering its previous final row reference when a new row is inserted into an Excel table.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Creating a running total column in an Excel table and subsequently adding new data rows.
- Observed behavior
- Instead of maintaining correct absolute and relative cell references, Excel incorrectly modifies the formula in the previous final row upon adding a new row.
Verify your current Excel version by going to File > Account, as this specific auto-fill formula behavior is often tied to recent software updates and known bugs.
Apply the INDEX Formula Workaround
Replace the standard SUM formula with a more robust SUM and INDEX combination that prevents Excel from incorrectly adjusting row references.
The standard running total formula relies on an expanding range. Due to certain table auto-expansion behaviors in recent updates, Excel may overwrite the previous row's formula. Using the INDEX and ROW functions forces Excel to determine the range boundaries dynamically, avoiding the bug.
Click on the first cell in your running total column within the Excel table.
Delete the current standard running total formula (for example, =SUM(A$2:A2)).
Type the workaround formula: =SUM(A$2:INDEX($A$1:A2,ROW())). Be sure to adjust the column letters (A) and row numbers to match the actual data in your table.
Press Enter to apply the formula, allowing it to automatically fill down the table column. Add a new row to the bottom of the table to verify that the previous row's formula remains unchanged.
Install Office Updates and Report the Issue
Since this is a known update-related bug in Excel for Windows, installing the latest patches or reporting it to Microsoft can lead to a permanent fix.
Avoid Formula Glitches with WPS Office
Experience stable table formatting and precise formula auto-filling with WPS Spreadsheet. As a highly compatible and lightweight alternative, WPS Office smoothly handles complex functions without the frustrating update bugs commonly found in other spreadsheet software.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install the software: Run the downloaded installation file and follow the on-screen prompts to set up WPS Office.
- 3. Open your spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx file to continue working with your running total formulas without compatibility issues.
Frequently Asked Questions
Why does my SUM formula change when I add a new row in an Excel table?
In structured Excel tables, the auto-expansion feature attempts to keep formulas consistent across all rows. However, due to certain software update bugs, Excel may incorrectly update the relative cell references of the previous final row when expanding. Using an INDEX workaround forces the formula to remain stable.
Can I disable automatic formula filling in Excel tables?
Yes, you can stop tables from automatically filling formulas. Go to File > Options > Proofing, click on 'AutoCorrect Options', navigate to the 'AutoFormat As You Type' tab, and uncheck the box labeled 'Fill formulas in tables to create calculated columns'.
How do I correctly write a running total formula normally?
Normally, a running total in cell B2 (summing values in column A) is written as =SUM($A$2:A2). As you drag it down, the first reference stays locked at row 2 ($A$2), while the second reference expands to include new rows (e.g., A3, A4) to calculate the cumulative total.
