Excel Formula Cell Reference Lock: Absolute References

Formulas & functions · Updated November 18, 2026

To lock a cell reference in an Excel formula, add dollar signs before the column letter, row number, or both: $A$1 locks the column and row, $A1 locks only the column, and A$1 locks only the row. In Excel for Microsoft 365 on Windows, while editing a formula, select or place the cursor on the reference and press F4 to cycle through these forms.

What a locked reference does

Excel references normally adjust when you copy or fill a formula into another cell. This behavior is useful when each row should calculate using its own inputs, but it can cause a problem when every formula needs to use one fixed cell, such as a tax rate, discount percentage, exchange rate, or lookup value. Adding dollar signs makes some or all of a reference stay in place as the formula moves.

For example, a formula in one row might multiply that row’s price by a rate stored in a single cell. When you fill the formula down, the price reference should move to the next row, but the rate reference should remain fixed. An absolute reference tells Excel to preserve both the rate cell’s column and row. The dollar signs affect how a reference changes when a formula is copied or filled; they do not change the value in the referenced cell.

A useful distinction: locking a reference in a formula is not the same as preventing someone from editing a worksheet cell. Reference locking controls how formulas adjust. Worksheet protection and locked cell formatting are separate features.

Relative, absolute, and mixed references

Excel has four common reference forms. The first is the ordinary relative reference, which has no dollar signs. The other three use dollar signs to hold the column, row, or both steady. Think of the dollar sign as attaching to the part of the address immediately after it.

  • Relative, A1: neither the column nor row is fixed. Copying a formula one column right changes the reference one column right; copying it one row down changes the reference one row down.
  • Absolute, $A$1: both the column and row are fixed. Copying the formula in any direction keeps the reference pointed at A1.
  • Mixed, $A1: the column is fixed, while the row can adjust. This is useful when formulas move across columns but should always read from column A.
  • Mixed, A$1: the row is fixed, while the column can adjust. This is useful when formulas move down rows but should always use row 1.

“Mixed” does not mean the reference is partly broken or uncertain. It means one coordinate is anchored and the other remains relative. Choosing the right form depends on the direction the formula will be copied and which part of the source address must stay constant.

Lock a reference with F4 on Windows

To add a reference lock with the keyboard in Excel for Microsoft 365 on Windows:

  1. Enter a formula or begin editing an existing one by double-clicking the cell or selecting the formula bar.
  2. Click the cell reference in the formula, or place the insertion point within that reference. If the formula contains several references, make sure the intended one is selected.
  3. Press F4 to cycle through the reference styles. Stop when the dollar signs appear in the positions you need.
  4. Press Enter to commit the formula, then copy or fill it to check that the reference behaves as intended.

For a reference that starts as A1, repeated presses cycle through $A$1, A$1, $A1, and back to A1. The exact starting point in the cycle depends on the reference’s current form. F4 changes the reference being edited; it does not lock a worksheet cell against editing.

If the keyboard’s function keys control hardware features such as brightness, you may need to press Fn+F4, depending on your keyboard and Windows settings. You can also type the dollar signs directly in the formula, which works regardless of the function-key setup.

Use an absolute reference in a practical example

Suppose a worksheet lists prices in column A and stores one tax rate in cell B1. You want column C to show the price including tax. The price should change for each row, but the tax-rate cell must remain B1 as you fill the formula downward.

With prices in A2:A4, the tax rate 8% in B1, and the formula entered in C2, use =A2*(1+$B$1). If A2 contains 100, the displayed result is 108. Filling the formula down makes the price reference move to A3 and A4 while the absolute reference continues to point to B1. The calculation is 100 multiplied by 1.08, which equals 108.

Notice that the formula does not lock A2. That reference is meant to change with each row. Locking every reference automatically would be wrong here: the formula would keep using the first price instead of each row’s price. A good rule is to anchor only the part that must stay put.

You can also use an absolute reference for a fixed lookup input or a shared assumption in a worksheet. Before copying a formula, identify which input is common to all the calculations and which input should vary by row or column. That simple check prevents many incorrect results.

When to use a mixed reference

Mixed references are especially helpful in two-way calculation tables, where formulas are filled both across columns and down rows. Imagine a multiplication table with row labels in A2:A6 and column labels in B1:F1. Each interior result should multiply the label at the left of its row by the label at the top of its column.

With row labels in A2:A6, column labels in B1:F1, and the first result in B2, enter =$A2*B$1. If A2 contains 2 and B1 contains 10, the result in B2 is 20. When you fill the formula right, $A2 continues to read column A while the row label reference can move down as the formula is filled down. The B$1 part keeps row 1 fixed while its column changes across the table.

In this pattern, the first reference anchors the label column and the second anchors the heading row. If a copied formula gives unexpected results, inspect both references: a missing dollar sign may let an anchor move, while an extra one may stop a reference that should change.

Copying formulas and checking the result

After writing a formula, use the fill handle—the small square at the lower-right corner of the selected cell—to copy it into adjacent cells. You can also copy and paste, or use Excel’s fill commands. Relative references adjust according to the formula’s new position; absolute parts remain fixed. Mixed references adjust only in the unanchored direction.

Check at least one copied formula rather than assuming the fill worked correctly. Select a destination cell and look at its formula bar. For a formula filled down one row, a relative row reference should usually advance by one, while an absolute reference should remain unchanged. For a formula filled right, check whether relative columns have advanced and fixed columns have not.

When filling a large range, test the top, bottom, and a middle cell if practical. Also compare a result with a hand calculation using simple sample values. A formula can be syntactically valid and still point to the wrong input because an anchor was omitted or placed on the wrong coordinate.

Common mistakes and a related misconception

  • Locking the wrong reference: If a formula has several cell addresses, F4 changes the reference where the cursor is positioned. Confirm the selected address before pressing it.
  • Locking both parts when only one should be fixed: A fully absolute reference will not move in either direction. Use a mixed reference when one coordinate needs to adjust.
  • Assuming dollar signs freeze a cell’s value: They do not. If the value in the referenced cell changes, formulas using that cell can recalculate. The dollar signs only control how the address behaves when the formula is moved or copied.
  • Confusing reference locking with cell protection: To restrict editing, use worksheet protection and cell formatting settings; dollar signs alone do not stop users from changing a cell.
  • Expecting every formula to need an absolute reference: Ordinary relative references are correct when each copied formula should use the corresponding row or column’s inputs.

There is also a difference between copying a formula and changing the value it refers to. Locking an address does not make a formula’s result permanent. If a fixed rate needs to remain historically unchanged, store or record the applicable rate separately rather than relying on an absolute reference to preserve an old value.

Excel for Mac and Excel for the web

The dollar-sign notation works in Excel on Windows, Mac, and the web. On Excel for Mac, the keyboard shortcut for cycling a selected reference is commonly Command+T while editing a formula; on some Mac keyboard and Excel configurations, Fn+F4 may work instead. If the shortcut does not respond, type the dollar signs directly or check the shortcut information for your installed Excel version and keyboard settings.

Excel for the web also supports formulas with absolute and mixed references. Keyboard behavior can depend on the browser and keyboard, so typing the dollar signs is a dependable alternative there as well. Regardless of platform, the important part is the reference form in the formula, not the shortcut used to create it.

Frequently Asked Questions

How do I lock a cell reference in an Excel formula?
Add a dollar sign before the column letter and row number to lock both, as in $A$1. While editing a formula in Excel for Windows, select the reference and press F4 to cycle through absolute and mixed forms.

What does F4 do to a cell reference?
When a cell reference is selected during formula editing, F4 cycles through relative, absolute, and mixed reference styles. It changes the formula’s address behavior when copied; it does not protect the referenced cell from being edited.

What is the difference between $A$1 and A$1?
$A$1 fixes both column A and row 1. A$1 fixes row 1 but allows the column to change when the formula is copied horizontally.

Can I lock only a column or only a row?
Yes. Use $A1 to keep the column fixed while allowing the row to change, or A$1 to keep the row fixed while allowing the column to change.

Does locking a reference stop other people changing the cell?
No. Dollar signs control how a formula reference shifts when copied or filled. To restrict edits, use Excel’s cell-locking and worksheet-protection features, which are separate from formula references.

Related

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.