How to Lock Cells in Excel Formulas With the F4 Shortcut

To lock a cell reference in an Excel formula, edit the formula, select the reference, and press F4. Excel adds dollar signs to make the row, column, or both stay fixed when you copy the formula. In Microsoft Excel for Microsoft 365 on Windows, the shortcut cycles through the available reference types; on many laptops, use Fn+F4 if the function keys control brightness or volume. This locks a formula reference, not the cell against editing.

What You’ll Need

Excel for Microsoft 365 on Windows, or Excel for Mac or the web if you are following the platform notes below.
A worksheet with a formula, or a clear idea of which cell reference in a new formula should stay fixed when you copy it.
A keyboard with an F4 key. If your laptop uses that key for a hardware feature, check whether you need to hold Fn as well.

Step-by-Step

Identify what should stay fixed. Decide whether the formula needs to keep the same row and column, only the row, or only the column. For example, a formula that uses one tax-rate cell copied down a list usually needs to keep both parts of the rate cell reference fixed. A formula filled across a price table may need one fixed row and one fixed column.
Enter or open the formula for editing. Select the cell and type the formula, or select an existing formula cell and press F2 on Windows. You can also click in the formula bar to edit the formula there. Do not press Enter yet if you still need to change the reference.
Find the reference to lock. Within the formula, locate the cell address that must not shift. With quantities in B2:B10 and a rate in E1, for example, the formula in C2 could be =B2*E1. In this layout, B2 is meant to change as the formula is filled down, while E1 should remain the rate cell. The displayed result depends on the values in those cells.
Put the cursor in the reference. Click within the reference in the formula bar, or use the arrow keys while editing to position the insertion point in the address. If you select the reference text with the mouse, make sure you have selected the cell reference you want to change, not another part of the formula. F4 applies to the reference containing the cursor or selected in the formula.
Press F4 until the right dollar signs appear. With the cursor in E1, press F4 once to change it to $E$1. The complete formula becomes =B2*$E$1. The dollar sign before E fixes the column, and the one before 1 fixes the row. On a laptop that assigns other actions to its function keys, try Fn+F4.
Choose the correct reference style. Repeated presses cycle through the reference types: A1 (relative), $A$1 (absolute), A$1 (row fixed), and $A1 (column fixed), then back to A1. For the rate in E1, use $E$1 if both its row and column must stay the same. To fix only a row or column, stop on the matching mixed reference instead.
Finish the formula and fill it. Press Enter to save the formula. Select the formula cell again and drag its fill handle down, double-click the fill handle, or copy and paste the formula into the destination cells. In the example, the B reference changes to B3, B4, and so on, while $E$1 remains unchanged.
Check a copied formula. Select one of the filled cells and look at the formula bar. Confirm that the changing reference moved as intended and the locked reference did not. For example, if the layout has quantities in B2:B10 and the rate in E1, the formula copied from C2 to C3 should use B3 and still use $E$1. Check the calculated result against the values in the row.

Here is a mixed-reference example for a two-way table. Assume prices are in B1:D1, quantities are in A2:A4, and the first result belongs in B2. The formula is =B$1*$A2. If B1 contains 3 and A2 contains 4, the result in B2 is 12. When filled right, the price column changes but row 1 stays fixed; when filled down, the quantity row changes but column A stays fixed. This is useful when a formula must work in both directions.

Mac and web notes: In Excel for Mac, select the reference while editing and use Command+T to cycle reference styles. Some Mac keyboard setups also allow F4; if not, use Command+T or type the dollar signs directly. In Excel for the web, the function key may be intercepted by the browser or computer, so try Fn+F4 if appropriate. If the shortcut does not respond, edit the reference manually in the formula bar.

Common Mistakes

Locking the wrong part of the address: $E$1 fixes both the column and row, but E$1 fixes only the row and $E1 fixes only the column. Check the formula in a copied cell rather than assuming the shortcut selected the right style.

Pressing F4 when the reference is not active: If the insertion point is outside a cell address, Excel may not change the reference. Edit the formula, click inside the address, and try again. If you have several references in one formula, place the cursor in each one separately before changing it.

Locking every reference unnecessarily: Making both references absolute can stop a formula from adjusting as it is copied. In the example =B2*$E$1, B2 should remain relative so each row uses its own quantity. Lock only the parts that should remain constant.

Confusing a fixed reference with sheet protection: Dollar signs affect how a formula adjusts when copied. They do not stop anyone from changing a cell’s contents. To restrict editing, use cell formatting and protect the worksheet; see the protection note in the tips below.

Using the wrong copy direction: A formula built for filling down may not behave as expected when filled across. Before copying over a large range, test it in one cell in the intended direction and inspect which row and column addresses changed.

Tips

Think of the dollar signs as markers for what should not move: $A$1 fixes everything, A$1 fixes the row, and $A1 fixes the column. A plain A1 shifts in both directions. This small distinction helps you choose the right reference before you fill a formula across hundreds of cells.

You do not have to cycle through every style if you already know the result. While editing, you can type the dollar signs directly into the address in the formula bar. This is often clearer when a formula contains multiple references or when a laptop’s function keys are inconvenient. Afterward, press Enter and inspect the formula once more.

For a large copied range, test the formula in the first row and one later row before filling the whole range. If it is a two-dimensional table, test one cell to the right and one cell below. These checks reveal a misplaced dollar sign early and make it easier to correct the starting formula.

If the goal is to prevent people from overwriting inputs or formulas, select the cells users should be able to edit and open Format Cells: use Ctrl+1 on Windows, or Command+1 on Mac. On the Protection tab, clear Locked for the cells that should remain editable. Then use Review > Protect Sheet and set the allowed actions. Cells are marked locked by default, but that setting only takes effect after sheet protection is turned on. Worksheet protection is a basic editing control, not a substitute for securing sensitive information.

Troubleshooting

F4 does nothing: Make sure Excel is editing a formula and the cursor is inside a cell reference. On many Windows laptops, press Fn+F4; a function-key setting may also let F4 work without Fn. On Mac, try Command+T. In Excel for the web, the browser or device may take over the key, so type the dollar signs directly if the shortcut is unavailable.

The formula changes the wrong reference: A formula can contain several addresses. Click directly inside the address that needs adjustment before pressing the shortcut. If you accidentally change another reference, use Ctrl+Z on Windows or Command+Z on Mac, then edit the correct one. Review the full formula before confirming.

The reference still moves after copying: Check that the copied formula actually contains dollar signs in the needed position. For a cell that must stay at E1 in every copied formula, the reference must read $E$1. If only the row is locked, the column can still change when filling left or right.

The formula shows an error or the wrong value: Compare the formula bar in the original cell and a copied cell. Confirm the relative reference points to the intended row, and check that the fixed cell contains the expected value. A reference can be locked correctly but still point to the wrong input cell. Also confirm the formula starts with an equals sign and that the referenced cells contain suitable values.

You cannot edit cells after protecting the sheet: This is separate from formula reference locking. If you intended to leave input cells editable, unprotect the sheet, clear the Locked setting for those cells in Format Cells, and protect the sheet again. If you only wanted formulas to copy correctly, sheet protection is not required.

Frequently Asked Questions

What is the shortcut to lock a cell reference in Excel?

On Windows, press F4 while editing a formula with the cursor in the reference. It cycles between relative, absolute, and mixed references. On a laptop, Fn+F4 may be necessary. In Excel for Mac, use Command+T to cycle reference styles.

How do I lock only a row or only a column?

Use F4 repeatedly until the dollar sign appears only where you need it. In A$1, the row is fixed while the column can change. In $A1, the column is fixed while the row can change. Check the copied formula in the direction you plan to fill.

Does locking a reference stop someone from editing the cell?

No. Dollar signs control how a formula reference moves when copied or filled. To limit editing, set the cell’s Locked property and turn on worksheet protection through Review > Protect Sheet. These are separate features.

Can I lock a reference without using the F4 shortcut?

Yes. Edit the formula and type dollar signs directly into the address, such as changing E1 to $E$1. This works in desktop Excel and is a useful fallback in Excel for the web or when a keyboard intercepts F4.

Why does F4 change the reference in a different way than I expected?

F4 cycles through four reference styles, and the starting style determines what appears next. It also changes the reference where the cursor is positioned. Check the address after each press and stop when the dollar signs match the row and column you want to hold fixed.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.