Formula Errors

Fix Excel Formulas That Change When Dragged or Copied

Amos GikundaAmos Gikunda Nov 12, 2026 868 views

Question details

The user needs to understand and correct formula references that change unexpectedly when a formula is dragged or copied.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A formula is being filled down or copied to another cell, and the user needs some references to adjust while others remain fixed.
Observed behavior
Excel changes relative cell references according to the formula's new position, which can produce unexpected results when references were not locked correctly.
Before you start

Open the formula in the formula bar and identify which row or column references should move with the formula. Compare the current result with the expected result so you can determine which references need absolute or mixed locking.

Solution 1Recommended

Lock the Excel references that should not change

Use this solution when a copied formula must keep one cell, row, or column fixed while other references continue adjusting.

Excel uses relative references by default, so A1 changes when the formula moves. Add dollar signs to create absolute or mixed references: $A$1 locks both the column and row, A$1 locks only the row, and $A1 locks only the column.

1
Edit the formula

Select the formula cell, click the formula bar or press F2, and place the cursor on the cell reference that should remain fixed.

2
Cycle through reference types

Press F4 repeatedly while the reference is selected to cycle through A1, $A$1, A$1, and $A1 until the required locking style appears.

3
Fill the corrected formula

Press Enter, select the corrected cell, and drag the fill handle or use Home > Fill > Down to copy the formula into the target cells.

Check the first copied result: Select one filled cell and compare its formula in the formula bar with the expected row and column references before filling the entire range.

Use WPS Spreadsheets to control copied formula references

WPS Spreadsheets supports relative, absolute, and mixed references, allowing you to lock the correct rows or columns before dragging formulas across a worksheet. Press F4 while editing a reference, then fill the corrected formula down or across.

  1. 1. Open the spreadsheet: Launch WPS Office, choose Spreadsheet, and open the XLS or XLSX workbook containing the formula.
  2. 2. Select the reference: Click the formula cell, press F2 or click the formula bar, and place the cursor on the reference that should change or remain fixed.
  3. 3. Apply the correct lock: Press F4 to cycle through the relative, absolute, and mixed reference formats until the dollar signs match your intended behavior.
  4. 4. Fill the formula: Press Enter, select the corrected formula cell, and drag its fill handle or choose Home > Fill to copy it across the required cells.
Lock rows, columns, or complete cell references with the F4 shortcutInspect formula changes directly in the formula bar before filling a rangeWork with familiar spreadsheet formulas and a lightweight Office interfaceOpen and edit Microsoft Excel XLS and XLSX files with compatible formatting
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change a cell reference when I drag a formula down?

Excel treats references such as A1 as relative by default. When the formula moves to another row, Excel adjusts the row reference to match the new position.

How do I keep a cell reference fixed in Excel?

Edit the formula, select the reference in the formula bar, and press F4 until it becomes an absolute reference such as $A$1. Pressing F4 again cycles through mixed reference formats.

What is the difference between $A$1, A$1, and $A1?

$A$1 locks both the column and row, A$1 locks only the row, and $A1 locks only the column. The unlocked part changes when the formula is copied.

Can I fix copied formula references in WPS Spreadsheets?

Yes. Open the formula in WPS Spreadsheets, select the reference in the formula bar, press F4 to choose a relative, absolute, or mixed format, and then fill the corrected formula.