Lock Cell References in Excel With Dollar Signs Easily

To lock a cell reference in an Excel formula, add a dollar sign before the column letter, the row number, or both—for example, $B$1 locks both. Click the reference in the formula and press F4 in Excel for Windows to cycle through the reference styles. This keeps the referenced row or column from shifting when you copy the formula; it does not stop someone from editing the cell itself.

In Excel, “locking cells with dollar signs” usually means fixing a formula reference, also called making it absolute or mixed. If you mean preventing people from changing cell contents, that requires cell formatting and sheet protection instead. The steps below cover dollar signs first, then explain how to protect a worksheet if that is your goal.

What You’ll Need

Excel for Microsoft 365 on Windows, with a workbook containing a formula or values you can use to create one.
A clear idea of what should stay fixed as you copy the formula: the whole reference, just its row, or just its column.
Optional: a small practice area or a copy of your workbook if you are changing formulas in important data.

Step-by-Step

Identify the reference that needs to stay fixed. A cell reference such as B1 has a column letter and a row number. By default, both parts are relative: when you copy a formula one column right or one row down, Excel adjusts the reference to match the new position. Decide whether you need to anchor both parts or only one. A tax rate, exchange rate, or fixed lookup value often needs both its row and column anchored. A formula copied down a table may need a fixed row but a changing column, or vice versa.
Enter a simple formula and check its layout. For a practical example, put the tax rate 8% in B1 and item prices 10, 25, and 40 in A2:A4. In B2, calculate the tax amount by multiplying the price in A2 by the rate in B1. The formula for this layout is =A2*B1. It returns 0.8, which you can display as $0.80 by applying a currency number format. At this point, both references are relative, so copying the formula down would change B1 to B2, then B3.
Select the reference while editing the formula. Double-click the formula cell, or select it and press F2, then click or use the arrow keys to place the insertion point within the reference you want to fix. You can also select just the reference text in the formula bar. For example, position the cursor in B1, not in A2, if the tax rate is the part that should remain constant. You do not need to select the entire formula.
Press F4 to add dollar signs. With the cursor in the B1 reference, press F4 once. Excel changes it to $B$1, anchoring both the column and row. In the example layout—8% in B1 and prices in A2:A4—the completed formula in B2 is =A2*$B$1. It returns 0.8, or $0.80 with currency formatting. A2 remains relative so it can change as the formula is copied, while the tax-rate reference stays at B1.
Use the other reference styles when appropriate. Pressing F4 repeatedly cycles through the four styles for the reference under the cursor: B1, $B$1, B$1, and $B1. The first has no fixed part. The second fixes both parts. The third fixes only row 1, allowing the column to change. The fourth fixes only column B, allowing the row to change. Stop when the style matches how you plan to copy the formula.
Confirm the formula, then fill it into other cells. Press Enter to save the edit. Select B2 and drag the fill handle—the small square at the bottom-right corner of the selected cell—down through B4. You can also copy B2, select B3:B4, and paste. With the example values, the results are 0.8, 2, and 3.2, or $0.80, $2.00, and $3.20 with currency formatting. The formula in each row should use that row’s price and still refer to $B$1.
Check the copied formula, not just the displayed result. Select B3 and look at the formula bar. Its price reference should have changed to A3, while the fixed reference should still read $B$1. If the result looks plausible but the formula refers to B2 or another unintended cell, correct the reference before filling more rows. Checking one copied formula is a quick way to catch a mistake before it spreads across a large range.
For keyboard differences, use the matching method. In Excel for Windows, F4 cycles the selected reference while you edit a formula. On many Mac keyboards, Command+T cycles the reference style; depending on the keyboard and macOS function-key settings, F4 or Fn+F4 may also work. In Excel for the web, edit the formula and try F4 if your browser does not intercept it, or type the dollar signs directly. Typing them manually works on every platform: edit B1 to $B$1, taking care to leave the rest of the formula unchanged.
If you meant to stop people changing cell contents, protect the sheet. Dollar signs do not provide access control. To protect formula cells, first select cells users should be able to edit, open Format Cells with Ctrl+1, choose the Protection tab, and clear Locked. Then choose Review > Protect Sheet and set the options you want. By default, cells have the Locked property, but it only takes effect after sheet protection is enabled. In Excel for Mac, the sheet-protection command is also on the Review tab; in Excel for the web, use Review > Protect Sheet if available for your workbook.

Common Mistakes

Confusing a fixed reference with a protected cell. A dollar sign controls how a formula reference changes when the formula is copied. Anyone with permission to edit the worksheet can still type over the referenced cell or the formula cell. Use sheet protection when the goal is to prevent accidental edits, and remember that worksheet protection is not the same as encrypting a workbook or setting a password to open it.

Locking the wrong part of a reference. $B$1 fixes both the column and row; B$1 fixes only the row; and $B1 fixes only the column. These differences matter when filling formulas across columns or down rows. If you anchor both parts when only one should be fixed, the copied formula may keep pointing to the same cell instead of moving along the intended row or column.

Pressing F4 when the cursor is not in a cell reference. F4 changes the reference style only when Excel is editing a formula and the insertion point is within a reference. If the cell is not in edit mode, F4 may repeat the last action instead. Enter edit mode first, then click or move the cursor into the specific reference.

Thinking the dollar sign is a currency symbol in the formula. In a reference like $B$1, each dollar sign marks a fixed address. It does not turn a number into money. Currency display is controlled separately through number formatting, such as Home > Number Format.

Copying a formula without checking its direction. Relative references adjust based on where the formula goes. A formula filled horizontally may behave differently from one filled down a column, especially when mixed references are involved. Test the formula in the first destination cell and inspect the resulting reference before filling a large range.

Tips

Use the fewest fixed parts that produce the intended result. For a single input cell that should remain the same in every copied formula, an absolute reference such as $B$1 is usually easy to understand. If you are making a two-variable table, mixed references can be useful: fixing the header row while letting the column change, or fixing the header column while letting the row change. Work out which direction the formula will be copied before choosing the style.

You can type dollar signs directly instead of relying on the shortcut. This is particularly useful when editing several references or using Excel for the web. Check that each sign sits immediately before the relevant column letter or row number: $B1 and B$1 are different from $B$1. Do not add spaces inside a cell reference.

When the same fixed input is used in many formulas, give that input cell a descriptive defined name through Formulas > Define Name. A name can make formulas easier to read, although names have their own rules and are not the same as adding dollar signs to a cell reference. For a simple worksheet, standard absolute references are often clearer.

Keep the input value visibly distinct from calculated results, and add a short label such as Tax rate beside it. That makes it easier for someone reviewing the sheet to understand why a formula points repeatedly to one cell. If you later move the input, Excel generally adjusts references when cells are moved or inserted, but verify important formulas after restructuring a worksheet.

Before editing many formulas, save the workbook or make a copy. If a copied formula produces an unexpected result, use the formula bar to inspect its references rather than assuming the displayed number is the problem. You can also select a formula cell and use Formulas > Trace Precedents to see which cells feed into the calculation.

Troubleshooting

F4 does nothing or repeats an action: Make sure the formula is actively being edited and that the cursor is inside a reference. On some laptops, use Fn+F4 because the function keys control hardware features by default. On Mac, try Command+T or the keyboard’s F4 combination. If the shortcut is intercepted or unavailable, type the dollar signs directly.

The reference changes after copying: Reopen the formula and check whether both required dollar signs are present. If only the column or row is fixed, press F4 until you reach the correct pattern, or edit the reference manually. Then copy the corrected formula again. If you pasted values rather than formulas, there will be no reference to adjust.

The formula shows an error or an unexpected number: Confirm that the fixed reference points to the cell containing the intended input, and that the input is numeric where the calculation expects a number. Check the formula bar for a missing multiplication operator or an accidental reference change. In the tax example, B1 should contain 8% as a numeric percentage, not text that merely looks like 8%.

People can still edit a “locked” cell: That is expected if you only added dollar signs to a formula. To restrict edits, set the Locked property for the cells you want protected and then use Review > Protect Sheet. If users still cannot edit cells that should be open, check that you cleared Locked for those cells before turning on protection. Unprotect the sheet first if you need to change those settings.

Frequently Asked Questions

What does the dollar sign do in an Excel formula?

It anchors the row, column, or both parts of a cell reference so that part does not change when you copy the formula. In $B$1, both the column B and row 1 remain fixed.

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

Put the dollar sign before the part to keep fixed. Use B$1 to fix row 1 while allowing the column to change, or $B1 to fix column B while allowing the row to change.

Does F4 lock the cell permanently?

No. F4 changes the reference style in the formula being edited. It does not prevent the cell from being edited, deleted, or replaced. Use worksheet protection to limit changes to cells.

Can I add the dollar signs without using F4?

Yes. Edit the formula and type a dollar sign before the column letter, the row number, or both. For example, change B1 to $B$1. This works in Windows, Mac, and Excel for the web.

What is the difference between $B$1 and B1?

$B$1 is an absolute reference: copying the formula does not shift either part. B1 is relative: Excel adjusts the column and row according to where you copy the formula.

Will dollar signs stay when I copy a formula?

Yes. The fixed parts remain anchored in copied formulas, while any unfixed parts adjust. For example, copying a formula with $B$1 down a column preserves that reference, but a reference such as A2 can become A3, A4, and so on.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.