How to Copy an Excel Formula While Locking One Cell
Question details
The user needs to copy an Excel formula down a column or across a row while keeping the reference to one specific cell constant.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Dragging and copying a single formula down a column where one reference must be static (absolute) and others dynamic (relative).
- Observed behavior
- By default, spreadsheet programs change all cell references relatively when a formula is copied. The goal is to enforce an absolute reference using dollar signs so the targeted cell reference remains unchanged.
Identify exactly which cell reference in your formula needs to remain fixed as a constant value, and which references should change dynamically based on the row or column.
Use an Absolute Reference to Lock the Cell Completely
Use a double dollar sign to freeze both the row and the column of a specific cell reference, ensuring it never shifts when the formula is copied.
By default, Excel uses relative referencing. If your formula is =(D11-(B11+F5)-(G11)+C11)/D11 and you copy it down one row, F5 automatically becomes F6. To prevent this, you must apply an absolute reference.
Select the cell containing your initial formula (e.g., F11) and click into the Formula Bar at the top of the spreadsheet.
Locate the specific cell reference you want to lock (e.g., F5) and manually type a dollar sign before the column letter and the row number to make it $F$5.
Press Enter to save the updated formula. Then, click and drag the small square fill handle at the bottom-right corner of the cell to copy the formula through your desired range.
Use a Mixed Reference to Lock Only the Row or Column
Apply a dollar sign to just one part of the reference if your data structure requires the cell to be locked in only one direction.
Easily Manage Cell References with WPS Spreadsheet
WPS Office Spreadsheet provides intuitive and robust spreadsheet functions, fully supporting all standard formula formatting and referencing shortcuts. With the F4 shortcut, you can lock cell references instantly and drag formulas without worrying about errors.
- 1. Open your document: Launch WPS Spreadsheet and open the file where you need to adjust formula referencing.
- 2. Select the reference: Double-click the cell containing your formula and highlight the cell reference you wish to lock.
- 3. Use the shortcut: Press the F4 key on your keyboard to instantly add the necessary dollar signs for an absolute reference.
- 4. Fill the series: Hover over the bottom-right corner of the cell until you see the crosshair cursor, then drag to copy the locked formula across rows or columns.
Frequently Asked Questions
Why do my formulas return errors when I copy them down a column?
By default, cell references in spreadsheet programs are relative. When you copy a formula down, the references shift down automatically. If a formula relies on a fixed constant value located in one specific cell (like a tax rate), you must use absolute referencing (like $A$1) to lock it. Otherwise, it will reference empty or incorrect cells as it moves down.
Can I lock multiple cell references within a single formula?
Yes, you can apply absolute references (dollar signs) to as many individual cell references within a formula as you need. Just insert the dollar signs or use the F4 shortcut on each distinct reference you want to keep stationary.
What exactly does pressing the F4 key do in the formula bar?
Selecting a cell reference in the formula bar and pressing F4 cycles through the different reference types. It changes a standard relative reference (A1) to absolute ($A$1), then to mixed row (A$1), then to mixed column ($A1), and finally back to relative.
