Calculation Issues

How to Stop Excel Formulas from Adjusting When Rows Are Inserted

Guest Writer Sep 25, 2026 869 views

Question details

The user wants to prevent Excel formulas from automatically updating their cell references when new rows are inserted above the referenced cells.

How to Stop Excel Formulas from Adjusting When Rows Are Inserted

Product: Excel
Device & OS: not provided
Scenario: Managing spreadsheets where a formula needs to continuously reference a specific row position, regardless of new rows being added or deleted above it.
Observed behavior: When a formula references a specific cell (e.g., =Sheet1!B$3) and a new row is inserted before row 3, Excel automatically changes the formula to =Sheet1!B$4 to track the original data, overriding the absolute reference dollar signs.

Before you start

Before modifying your formulas, identify the exact cell positions you need to lock, and note that using volatile functions to fix this issue may impact calculation performance on very large workbooks.

Solution 1Recommended

Use the INDIRECT Function to Lock the Reference

The INDIRECT function evaluates a text string as a cell reference, preventing Excel from automatically adjusting the row number when rows are inserted.

Excel's default behavior is to adjust formula references to track the original cell when rows or columns are added. Using a dollar sign ($) only creates an absolute reference for copying and pasting; it does not stop Excel from modifying the reference during row insertions.

To bypass this, you can wrap your cell reference in an INDIRECT function. Because INDIRECT treats the cell address as a rigid text string, Excel cannot alter it.

Select the target cell

Click on the cell where you want to place your locked formula.

Enter the INDIRECT function

Type =INDIRECT(" followed by the sheet name and exact cell reference. For example, to permanently lock the reference to cell B3 on Sheet1, type =INDIRECT("'Sheet1'!B3").

Use a dynamic row structure (Optional)

If your reference needs to match a relative row structure instead of a hardcoded string, you can concatenate text with the ROW() function, such as =INDIRECT("'Sheet1'!B"&ROW()+1).

Apply and test

Press Enter to calculate the formula. Insert a row above row 3 on the referenced sheet to confirm that the formula continues extracting data from the original position.

Use the INDIRECT Function to Lock the Reference

Performance Impact: INDIRECT is a volatile function, meaning it recalculates every time any change is made to the workbook. Use it sparingly in large, complex workbooks to avoid slowing down calculation times.

Solution 2

Redesign Data with Excel Tables or Key-Based Lookups

Using Excel Tables or lookup functions creates stable data references based on content rather than rigid row positions.

Lock Your Formulas Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced calculation functions like INDIRECT and structured table references, allowing you to easily lock formulas and manage dynamic data exactly like you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the file where your formula references need locking.
2. Select the cell: Click on the cell that currently contains the adjusting formula.
3. Apply the INDIRECT function: Replace the standard reference with the INDIRECT function, typing the exact address in quotation marks (e.g., =INDIRECT("Sheet1!B3")).
4. Verify your changes: Press Enter and test by inserting a new row in the source sheet. The formula will remain locked to the specific cell coordinate.

100% compatible with Microsoft Excel formulas and formatsFully supports volatile functions like INDIRECT and OFFSETAdvanced data tools including VLOOKUP, INDEX, and MATCHLightweight, fast, and free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the dollar sign ($) stop the row reference from changing?

The dollar sign creates an absolute reference that only locks the cell address when you copy and paste or drag the formula to other cells. It is not designed to stop Excel from updating the reference when physical rows or columns are inserted or deleted inside the sheet.

Can I use the OFFSET function to lock a row reference?

Yes, you can use the OFFSET function starting from an anchor point (like cell A1) to pinpoint a specific row coordinate. For example, =OFFSET($A$1, 2, 1) will always return the value in B3, even if rows are inserted above it. However, like INDIRECT, OFFSET is a volatile function.

Will using the INDIRECT function slow down my Excel workbook?

Yes, INDIRECT is known as a volatile function. This means it recalculates every single time Excel recalculates any part of the workbook, regardless of whether the specific source data has changed. If used extensively across thousands of cells, it can noticeably slow down workbook performance.

Excel & Data Problems