Formula Errors

How to Keep Excel Formula References Fixed When Adding Columns

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to prevent column references inside a formula from automatically changing when new columns are inserted into the worksheet.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Modifying the structure of a spreadsheet by inserting new columns without disrupting existing formula target ranges.
Observed behavior: By default, even absolute references shift when columns are inserted to the left of the target data, causing the formula to evaluate the wrong column.

Before you start

Identify the exact sheet name and column letters that must remain completely static before modifying your formulas.

Solution 1Recommended

Use the INDIRECT Function to Lock the Reference

The INDIRECT function reads a text string and turns it into a valid cell reference. Since the reference is text, Excel ignores it when shifting columns.

By wrapping your column range inside quotes within an INDIRECT function, the formula will always point to that exact column letter, regardless of any columns added or deleted in the spreadsheet.

Select the formula cell

Click on the cell containing the formula you want to secure against column shifts.

Locate the shifting reference

Find the specific column reference in the formula bar that keeps changing, such as Current!D:D.

Wrap with INDIRECT

Replace the reference with the INDIRECT function and enclose the original reference in quotation marks. For example, change Current!D:D to INDIRECT("Current!D:D").

Apply the modified formula

Press Enter to save. A complete example looks like this: =TEXTJOIN(" ",TRUE,IF(INDIRECT("Current!D:D")="x2",Current!B:B,"")).

Performance Tip: For better performance in large sheets, avoid entire-column references like D:D. Instead, specify the exact data range, such as INDIRECT("Current!D1:D1000").

Solution 2

Combine FILTER with INDIRECT for Dynamic Output

If you are filtering data arrays, you can embed INDIRECT directly into your FILTER conditions to maintain strict logical testing columns.

Advanced Formula Management

Use WPS Spreadsheet to Handle Complex Formulas Seamlessly

WPS Spreadsheet fully supports advanced logical and reference functions like INDIRECT, FILTER, and TEXTJOIN, keeping your complex data tasks stable even when restructuring your workbooks.

1. Open your file in WPS: Launch WPS Spreadsheet and open your existing .xlsx workbook.
2. Edit the formula: Double-click the cell and use the formula bar to wrap your target columns with INDIRECT().
3. Insert columns freely: Right-click the column headers to insert new columns without breaking your carefully designed formulas.

Fully compatible with Microsoft Excel formulas and .xlsx files.Built-in dynamic array support for functions like FILTER and TEXTJOIN.Lightweight architecture for fast calculation speeds across massive datasets.Completely free to use with an intuitive, familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why do absolute references (using $ signs) still change when columns are inserted?

The dollar sign ($) only locks a reference when you are copying, pasting, or dragging the formula across different cells. It does not stop Excel from automatically adjusting references when the physical layout of the sheet is altered by inserting or deleting rows/columns.

Does using the INDIRECT function slow down my spreadsheet?

Yes, INDIRECT is a 'volatile' function, meaning it recalculates every time any change is made anywhere in the workbook. Overusing it in very large spreadsheets can cause noticeable performance drops.

Can I use INDEX to lock references instead of INDIRECT?

Yes, INDEX is non-volatile and often more efficient. You can use INDEX with specific row/column numbers to return a reference. While less intuitive to write than INDIRECT, it is much better for the calculation speed of large files.

Will the INDIRECT function work if it points to a closed workbook?

No, INDIRECT only evaluates references to other workbooks if those workbooks are currently open. If the target workbook is closed, the formula will return a #REF! error.

Excel & Data Problems