How to Keep a Cell Reference Fixed in Excel AutoFill
Question details
The user needs to prevent Excel from automatically adjusting specific cell references when copying or dragging formulas down a column or across a row.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using the AutoFill handle or copying formulas where a specific multiplier, constant, or data range must remain exactly the same.
- Observed behavior
- Excel automatically changes relative cell references (e.g., B22 becomes B23, B24) when the formula is dragged downward, causing calculation errors because the intended reference is lost.
Before dragging the AutoFill handle, identify exactly which cell references in your formula need to change dynamically (relative) and which ones must stay locked (absolute).
Use Absolute Cell References (The Dollar Sign Method)
Lock the exact row and column of a cell reference by adding a dollar sign ($) before the column letter and row number.
When using AutoFill, Excel defaults to relative references, meaning formulas adjust based on their new position. By adding a dollar sign ($) to a cell reference, you convert it to an absolute reference, which tells Excel to lock that exact coordinate.
Click on the cell containing your formula, such as the one containing =PRODUCT(B3,B22).
Click inside the Formula Bar located at the top of the worksheet, or simply double-click the cell to begin editing the formula.
Place your cursor on the reference you want to lock (e.g., B22) and type a $ before the column letter and a $ before the row number to make it $B$22. You can also press the F4 key on your keyboard to automatically insert the dollar signs.
Press Enter to save the updated formula. Click and drag the fill handle (the small square at the bottom-right corner of the selected cell) downward. The locked reference will remain as $B$22 in all the filled cells.
Easily Lock Cell References in WPS Spreadsheet
WPS Spreadsheet fully supports standard Excel formulas and AutoFill behaviors. You can quickly lock cell references to prevent them from changing by using the exact same F4 keyboard shortcut and absolute reference formatting.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing the formulas you want to lock.
- 2. Edit the formula: Double-click the cell with the formula, or click inside the Formula Bar at the top.
- 3. Use the F4 shortcut: Place your text cursor on the cell reference you wish to fix (e.g., B22) and press the F4 key to instantly add dollar signs ($B$22).
- 4. Drag the fill handle: Press Enter, then click the small square at the bottom-right corner of the cell and drag it downward to apply the locked formula.
Frequently Asked Questions
What is the keyboard shortcut to lock a cell reference?
The F4 key is the standard shortcut. While editing a formula, place your cursor within or immediately after the cell reference and press F4 to instantly cycle between absolute ($B$22), mixed row (B$22), mixed column ($B22), and relative (B22) references.
Why did my cell reference only partially change when I dragged the formula?
You likely used a mixed reference instead of an absolute one. If your formula has a dollar sign only before the column letter (e.g., $B22), the column is locked but the row will still change as you drag vertically. If it is only before the row number (e.g., B$22), the row is locked but the column can change if dragged horizontally.
Does WPS Office use the same cell reference rules as Excel?
Yes, WPS Spreadsheet uses the exact same formula syntax, including relative, absolute, and mixed references. You can open your Excel spreadsheets directly in WPS Office without breaking any formulas or AutoFill logic.
How do I fix multiple cell references in a long formula?
You must lock each cell reference individually. Click on each relative reference within the Formula Bar and manually type the dollar signs, or press F4 for every single reference you want to keep constant before pressing Enter.
