How to Stop Excel from Automatically Extending a Formula Range
Question details
The user wants to prevent Excel from automatically expanding formula ranges when new data is entered into adjacent rows.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering data in adjacent rows or working with Excel tables where formulas are automatically updating.
- Observed behavior
- Excel automatically extends the formula range or adjusts references unexpectedly when new data is added nearby.
Before making changes, determine whether your data is formatted as an official Excel Table (which has built-in auto-expansion) or just a standard range of cells.
Use Absolute Cell References
Lock your formula references using dollar signs ($) so they do not shift or expand automatically when new data is added.
By default, Excel uses relative cell references. When new rows are added or data is placed adjacent to a referenced range, Excel assumes you want the formula to include the new data. Absolute references lock the specific cells you want to calculate.
Click on the cell containing the formula that is automatically extending.
Click into the formula bar at the top of the worksheet to begin editing.
Highlight the cell range in the formula and press the F4 key on your keyboard to add dollar signs (e.g., change A1:A10 to $A$1:$A$10). This locks the reference.
Press Enter to apply the updated, locked formula to the cell.

Disable Auto-Expand in Excel Options
Turn off the specific setting that automatically extends data range formats and formulas.
Easily Manage Formulas with WPS Office
WPS Spreadsheet provides intuitive tools for managing formulas, locking cell references, and controlling how your data expands. Enjoy a familiar interface that makes handling complex data a breeze.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document.
- 2. Select the target formula: Click the cell with the formula you want to lock.
- 3. Lock the reference: In the formula bar, select the cell range and press F4 to convert it to an absolute reference (e.g., $A$1).
- 4. Disable auto-extend (optional): Go to Menu > Options > Edit, and uncheck the automatic formatting and formula extension settings if you prefer manual control.

Frequently Asked Questions
Why does Excel automatically change my formula references?
By default, Excel uses relative cell references. When you copy a formula or add new rows adjacent to your data, Excel assumes you want the formula to adapt to the new position or include the newly adjacent data.
What is the difference between an absolute and relative reference?
A relative reference (like A1) changes when copied or when rows and columns are added. An absolute reference (like $A$1) is locked and will always point to the exact same cell, no matter where the formula moves or how the worksheet changes.
Can I stop formulas from copying down automatically in an Excel table?
Yes. When you enter a formula in an Excel table and the AutoCorrect Options button appears next to the cell, click it and select 'Stop Automatically Creating Calculated Columns'.
Does WPS Office handle absolute references the same way as Excel?
Yes, WPS Spreadsheet uses the exact same syntax for formula references. You can lock cells by adding dollar signs manually or by selecting the reference and pressing the F4 key.




