Fix Excel Formulas That Change When Dragged or Copied
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.
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.
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.
Select the formula cell, click the formula bar or press F2, and place the cursor on the cell reference that should remain fixed.
Press F4 repeatedly while the reference is selected to cycle through A1, $A$1, A$1, and $A1 until the required locking style appears.
Press Enter, select the corrected cell, and drag the fill handle or use Home > Fill > Down to copy the formula into the target cells.
Review the copied formula against its new position
Use this check when you are unsure whether the formula changed correctly after dragging or copying.
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. Open the spreadsheet: Launch WPS Office, choose Spreadsheet, and open the XLS or XLSX workbook containing the formula.
- 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. 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. 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.

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.




