To lock a cell reference in an Excel formula, edit the formula, place the cursor in or select the reference, and press F4. Excel cycles through absolute and mixed references, adding dollar signs such as $B$1 so that the reference stays fixed when you copy the formula. This locks a reference for copying; it does not prevent someone from editing the worksheet cell.
These steps are for Excel for Microsoft 365 on Windows. You need a formula containing a cell reference to change, and a keyboard with access to F4. If you are using a laptop, you may need to press Fn+F4. On a Mac, use Command+T in many Excel keyboard layouts, or Fn+F4 if your function-key settings make F4 available to Excel.
What You’ll Need
- An Excel workbook with a formula you are entering or editing.
- A cell reference in that formula that should stay fixed, or have only its row or column fixed, when the formula is copied.
- Access to the F4 key on Windows. On some keyboards, use the Fn key as well.
Step-by-Step
- Decide what should stay fixed. A normal reference such as B1 is relative: if you copy a formula one column right and one row down, Excel adjusts it to C2. An absolute reference,
$B$1, keeps both the column and row fixed. A mixed reference fixes just one part:$B1keeps column B fixed but lets the row change, whileB$1keeps row 1 fixed but lets the column change. Choose the form that matches how you will fill the formula. - Start or edit the formula. Click the cell containing the formula. To edit it, press F2 or click in the formula bar. If you are entering a new formula, type an equals sign and begin the expression. You need to be editing the formula itself; simply selecting a worksheet cell is not enough for F4 to change a reference.
- Put the insertion point on the reference. Use the arrow keys, or click the reference in the formula bar, so the cursor is within the cell address you want to lock. You can also highlight the address. For example, select B1 within the formula rather than leaving the cursor at the end of the entire formula. If the reference appears more than once, work on each occurrence that needs a different setting.
- Press F4 to cycle the reference type. In Windows Excel, each press changes the selected reference through four forms:
$B$1,B$1,$B1, andB1. Stop when the form you want appears. If the cursor is in a range reference, such as B1:B10, make sure you understand which part Excel is changing; check the full formula before you confirm it. - Confirm the formula. Press Enter to commit the edit. If you are still composing a formula, you can move to another part of it before pressing Enter. Check the displayed result and, if needed, click the cell again to confirm that the dollar signs are present in the formula bar. Dollar signs may not be obvious from the calculated value alone.
- Test how the formula copies. Use the fill handle, the small square at the lower-right corner of the selected cell, to drag the formula down or across. Alternatively, copy and paste it into a nearby cell. Look at the copied formula in the formula bar: the locked portion should be unchanged, while any relative portion should move as intended. Undo the test with Ctrl+Z if you do not want to keep the copied formula.
- Check a practical absolute-reference example. Suppose quantities are in A2:A10, a fixed rate is in B1, and you want each result in column C. With the rate stored in B1 and the quantity for the first calculation in A2, enter this in C2:
=A2*$B$1. To create it, type the formula with the references first, place the cursor on B1, and press F4 until it reads$B$1. When you fill C2 down, A2 changes to A3, A4, and so on, but the rate reference remains B1. This is useful for a tax rate, commission rate, unit price, or other shared input. - Use a mixed reference when filling in two directions. Suppose monthly values are in B2:M10 and a fixed percentage is in row 1, with each row using the percentage above its column. With the percentage for column B in B1 and the first value in B2, enter this in C2:
=B2*B$1. The layout assumption is that the value is in B2 and the corresponding percentage is in B1. Lock just the row by cycling the B1 reference toB$1. When copied down, row 1 stays fixed; when copied right, the column can change from B to C and onward. If instead every formula must use column B but the row should change, use$B1. - Use the matching shortcut on a Mac if needed. In Excel for Mac, Command+T commonly cycles reference styles while you edit a formula. Depending on the keyboard and macOS function-key settings, Fn+F4 may work instead. If neither shortcut acts on the reference, type the dollar signs directly and verify the resulting formula. Excel for the web may also depend on your browser and keyboard settings; manual entry of the dollar signs is a reliable alternative.
Common Mistakes
- Pressing F4 while a cell is only selected. In that situation, F4 may repeat a previous action or do something unrelated; it will not necessarily convert a reference. Enter formula edit mode first, then position the cursor on the address.
- Locking both the row and column by accident.
$B$1is not the same asB$1or$B1. If you fill formulas across columns, a fully absolute reference will keep pointing to B1 even when you meant to use C1, D1, and so on. - Assuming a dollar sign protects the worksheet cell. Dollar signs control how a formula reference adjusts when copied. They do not block typing, deletion, or other edits to the referenced cell. Use worksheet protection if preventing edits is the goal.
- Forgetting to lock every needed occurrence. A formula can refer to the same input more than once. Changing one B1 to
$B$1does not automatically change a separate B1 elsewhere in the formula. Review the entire expression before filling it. - Not checking the copied result. A formula can calculate successfully while using the wrong row or column. Inspect at least one copied formula in the formula bar, especially when copying in both directions or across a large range.
Tips
- Remember the dollar sign as a marker: it sits immediately before the part that should not move. In
$B$1, both parts are fixed; inB$1, only the row is fixed; in$B1, only the column is fixed. - Use F4 while building a formula rather than waiting until after it has been filled across hundreds of cells. It is easier to spot and correct the reference type in the first formula.
- If a shortcut is awkward, type the dollar signs yourself. You can edit B1 to
$B$1,B$1, or$B1directly, then press Enter. The result is the same as cycling to that form with F4. - Use a small test area before filling a large table. Copy the formula one cell down and one cell right, then confirm that the fixed and moving parts behave correctly. This is particularly helpful with mixed references.
- For formulas that should refer to one input cell in many places, consider naming that input cell. A named range can make a formula easier to read, although it does not replace checking how the formula behaves when copied.
- On a laptop, check whether the top-row keys control brightness or volume by default. If so, try Fn+F4, or change the keyboard’s function-key setting if available. Avoid changing keyboard settings if the dollar-sign method is simpler for your task.
Troubleshooting
- F4 does not add dollar signs. Click in the formula bar or press F2, then place the cursor on a reference such as B1. On a laptop, try Fn+F4. If you are on a Mac, try Command+T or Fn+F4. You can always type the dollar signs directly.
- F4 changes volume, brightness, or another system setting. Your keyboard may be treating the function row as media keys. Use Fn+F4, or adjust the keyboard’s function-key behavior. If that is inconvenient, edit the address manually.
- The formula changes after copying when it should not. Reopen the original formula and check for dollar signs before the relevant column letter, row number, or both. Use F4 until the intended form appears, press Enter, and refill the destination cells.
- The formula stays pointed at the same cell when it should move. You may have made the reference fully absolute. For example, change
$B$1toB1if both row and column should adjust, or choose a mixed reference if only one should adjust. - You meant to stop people editing cells. F4 is the wrong tool for that task. In Excel for Microsoft 365 on Windows, select the cells users should be able to edit, open Format Cells with Ctrl+1, go to the Protection tab, and clear Locked. Then use Review > Protect Sheet. Worksheet cells are marked Locked by default, but that setting only prevents edits after sheet protection is turned on. Consider setting a password only if appropriate and keep it somewhere safe.
Frequently Asked Questions
What does F4 do to a cell reference in Excel?
While you edit a formula, F4 cycles a reference between absolute, mixed, and relative forms. For B1, the cycle is $B$1, B$1, $B1, and B1. It changes how that reference adjusts when the formula is copied.
Does F4 lock a cell in place?
It locks the reference inside a formula, not the worksheet cell against editing. To prevent users from changing cells, set the appropriate Locked options and turn on Review > Protect Sheet.
Why does F4 not work on my laptop?
The laptop may reserve F4 for a hardware control such as volume or brightness. Try Fn+F4 while editing the reference. You can also enter the dollar signs manually.
What is the difference between $B$1 and B$1?
$B$1 fixes both column B and row 1. B$1 fixes only row 1, allowing the column letter to change when you copy the formula sideways.
How do I lock only a column or only a row?
Use $B1 to fix column B while allowing the row to change, or B$1 to fix row 1 while allowing the column to change. Press F4 repeatedly while the reference is selected until the desired form appears.
Can I use F4 after I have already entered the formula?
Yes. Select the formula cell, press F2 or click the formula bar, place the cursor on the reference, and press F4. Press Enter to save the change, then check how the formula fills into neighboring cells.
Related
- Count Excel Rows While Excluding Hidden Rows with SUBTOTAL
- COUNTA in Excel: Count Nonblank Cells for Data Analysis
- Power Query Excel: Aggregate Data into Summary Tables
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Time Between Two Timestamps: Formulas and TipsIf the start timestamp is in A2 and the end timestamp is in B2, enter =B2-A2 to calculate…
- Concat Excel Columns: Formulas, Methods, and Fixes ExplainedTo concat Excel columns—that is, join values from separate cells into one—use the ampersand operator. With first names…
- Show Formulas in Excel: Keyboard Shortcut GuideTo show formulas in Excel for Microsoft 365 on Windows, press Ctrl+` (Ctrl plus the grave accent key,…
- Concat Excel Multiple Cells: Formulas, Spaces, and TipsTo concat (combine) multiple cells in Excel, use =CONCAT(A2:C2) to join the values in A2 through C2 without…
- Excel Rank Formula: Rank a Number List Highest to LowestTo rank numbers from highest to lowest in Excel, use RANK.EQ with the order argument set to 0.…
- Excel Formula to Split Cell Data After a Comma: ExamplesTo return everything after the first comma in cell A2 in Microsoft 365, use =TEXTAFTER(A2,","). To remove the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.