Formula Errors

How to Stop Excel Tables from Changing the Previous Row's Formula

Partner Editor Oct 9, 2026 869 views

Question details

The user needs to prevent formulas in an Excel table's last row from automatically expanding to include newly added rows, as absolute references fail to stop this behavior.

How to Stop Excel Tables from Automatically Changing the Previous Row's Formula

Product: Microsoft Excel
Device & OS: not provided
Scenario: Adding a new row of data at the bottom of a formatted Excel table that contains a running total or range-based formula.
Observed behavior: Excel automatically changes the previous row's formula range to include the new bottom row, ignoring absolute reference indicators like dollar signs.

Before you start

Determine whether your dataset must remain formatted as an official Excel Table (Insert > Table), as converting it back to a standard range is the most direct way to bypass automated column logic.

Solution 1Recommended

Use Structured References for Running Totals

Adapt your formula using structured references and explicit row logic to calculate totals based on the previous row rather than relying on an expanding fixed range.

Excel tables are designed to push one unified formula down an entire column. When you use expanding ranges (like $A$1:A2), Excel modifies the endpoints automatically when new rows are added. To avoid this, base your calculation on the cell immediately above.

Select the target cell

Click the first cell in your table's calculated column where the running total or formula begins.

Input relative structured logic

Type a formula that references the specific cell above it, combined with the current row's table headers. For example: =SUM(J208, [@IN] - SUM([@OUT])).

Apply across the table

Press Enter. Excel will automatically apply this structured logic down the column. When you add a new row, it will cleanly reference the immediate previous row without expanding earlier formulas.

Use Structured References for Running Totals

Understanding Calculated Columns: Using structured references (like [@ColumnName]) ensures your formula works in harmony with Excel's Calculated Columns feature instead of fighting against it.

Solution 2

Disable the Auto-Fill Formulas Setting

Turn off Excel's AutoCorrect setting that automatically propagates formulas to new rows in tables.

Solution 3

Convert the Table to a Normal Range

If strict control over absolute references ($A$1) is required, changing the table back to a standard range will stop Excel from overriding your references.

Manage Spreadsheet Data Easily

Manage Table Formulas Effectively with WPS Office

WPS Spreadsheet provides full compatibility with structured table references and calculated columns. You can easily manage running totals and toggle automatic formula settings through its highly intuitive interface.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data table.
2. Access Options: Click the 'Menu' button in the top-left corner and select 'Options' from the drop-down list.
3. Modify Edit settings: Navigate to the 'Edit' tab in the Options dialog box.
4. Disable table auto-fill: Locate and uncheck the setting that automatically fills formulas in tables, then click OK to apply the changes.

Fully compatible with Microsoft Excel (.xlsx) files and advanced table structuresEasily toggle calculated columns and AutoCorrect settingsLightweight application with a familiar, user-friendly interfaceCompletely free alternative for data analysis and daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do absolute references not work in an Excel table column?

In an official Excel Table, the 'Calculated Columns' feature intentionally overrides standard absolute references (like $A$1) to ensure the formula remains perfectly uniform across the entire column. It adjusts endpoints dynamically as the table grows.

How do I make a running total in an Excel table?

Instead of using a fixed range like SUM($A$1:A2), use a structured approach where you add the current row's value to the cell directly above it (e.g., =B1 + [@Value]). This maintains consistent relative logic as the table expands.

Will converting my table to a range remove my formatting?

No. Converting a table to a normal range removes the automated data features (such as calculated columns and auto-expanding filters), but it preserves your existing cell colors, borders, and font styles.

Excel & Data Problems