Calculation Issues

Stop Excel Table Running-Total Formulas from Changing Automatically in WPS Spreadsheet

Huda QurayshiHuda Qurayshi Nov 13, 2026 871 views

Question details

The user needs to prevent Excel table calculated columns from automatically overwriting or shifting a running-total formula when new rows are added.

Stop Excel Table Running-Total Formulas from Changing Automatically in WPS Spreadsheet
Product
WPS Office Spreadsheet
Device & OS
not provided
Scenario
The user is maintaining a table with a running total column and wants formula references to stay stable when the table expands.
Observed behavior
Excel Tables automatically propagate calculated-column formulas, so relative references and inherited formatting can change as new rows are inserted.
Before you start

Before editing the table, confirm which column contains the running-total formula and note the exact formula you want to keep.

Solution 1Recommended

Delete and Rebuild the Affected Calculated Column

Use this when the table has already propagated an incorrect running-total formula and you want to remove the old calculated-column definition.

Excel Tables store calculated-column behavior after the first formula entry. In WPS Spreadsheet, deleting the entire column and creating a new table column clears that stored behavior so the corrected formula can be applied cleanly.

1
Select the affected calculated column

Click the header of the column that contains the running-total formula to select the entire table column.

2
Delete the table column

Right-click the selected header and choose Delete > Table Columns, or press Ctrl + - (minus) and confirm the deletion to remove the old calculated-column definition.

3
Insert a replacement table column

Right-click the header where the running-total column should appear and choose Insert > Table Columns to the Left or Right.

4
Enter the corrected running-total formula

Click the first data cell in the new column, type =SUM($D$2:[@Amount]), and press Enter.

5
Confirm the calculated-column fill

In the calculated-column prompt that appears, choose Overwrite all cells in this column with this formula so WPS Spreadsheet rebuilds the column with the new formula.

Delete and Rebuild the Affected Calculated Column
Test by adding a row: After rebuilding, add a new row at the bottom of the table and verify the running-total formula keeps the intended starting cell and does not shift.
WPS Spreadsheet Solution

Fix Excel Table Running-Total Formulas in WPS Office Spreadsheet

Open the workbook in WPS Office Spreadsheet, rebuild the calculated column, and use a mixed-reference running-total formula so new rows stop shifting the calculation. WPS keeps Excel formula and table compatibility while giving you direct control over calculated-column behavior.

  1. 1. Open the affected workbook in WPS Office Spreadsheet: Launch WPS Office, click Menu > Open, and select the .xlsx workbook that contains the Excel table.
  2. 2. Select and rebuild the calculated column: Delete the affected running-total column, insert a new table column, and enter the corrected running-total formula in the first data cell.
  3. 3. Use a mixed-reference formula: Enter a formula such as =SUM($D$2:[@Amount]) to lock the starting row while keeping the current table row relative.
  4. 4. Test with a new table row: Press Ctrl+Home and add a new row at the bottom of the table to verify that the running-total formula stays consistent and formatting is not incorrectly inherited.
Stop calculated columns from overwriting running totals when rows are addedFull compatibility with Microsoft Excel .xlsx workbooks, table formulas, and formattingFree spreadsheet tools for editing, formatting, and testing table behaviorLightweight familiar interface for quick formula corrections
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel table change the running-total formula when I add a new row?

Excel Tables treat formulas as calculated columns and automatically propagate them. If the formula uses relative references, the starting point can shift, so WPS Spreadsheet rebuilds the column or uses mixed references to lock it.

How do I delete only the calculated column formula but keep the data?

Select the entire table column, right-click and choose Delete > Table Columns, then insert a new table column and re-enter the formula to rebuild the calculated column cleanly.

What formula can I use for a stable running total in a table?

Use a mixed-reference SUM formula, such as =SUM($D$2:[@Amount]), where the first cell reference is absolute and the second uses the current row structured reference.

Can I stop Excel from automatically filling formulas in tables?

You can change AutoCorrect options by going to File > Options > Proofing > AutoCorrect Options and turning off the calculated-column option, but the more reliable fix is to rebuild the column with stable references.