Excel Function to Keep a Cell Constant in Formulas Explained

To keep a cell reference fixed when you copy an Excel formula, make it absolute by adding dollar signs. With a quantity in B2 and a rate in E1, enter =B2*$E$1; the $ signs keep the formula pointed at E1 as it is copied. In Windows Excel, select the reference while editing the formula and press F4 to cycle through reference styles.

Why Excel uses dollar signs in references

There is no separate Excel function called KEEP, FIX, or CONSTANT that locks a cell reference. The feature you want is an absolute cell reference. The dollar signs tell Excel which parts of an address should stay fixed when a formula is copied or filled into other cells.

Normally, Excel uses relative references. If a formula in C2 refers to B2 and you copy it down one row, Excel changes the reference to B3 so the formula continues to use the value beside it. That is useful for repeated calculations, but not when every row must use the same rate, target, or assumption.

For example, if B2 contains 40 and E1 contains 0.08, the formula =B2*$E$1 returns 3.2. If you copy it to C3, the reference to B2 moves to B3, but $E$1 stays fixed. The formula is still multiplying the corresponding quantity by the rate stored in E1.

How absolute and mixed references work

A cell reference has a column part and a row part. The dollar sign can lock either part independently. This gives you four useful reference styles:

Relative: A1. Both the column and row can adjust when copied.
Absolute: $A$1. Both the column and row are fixed.
Mixed, fixed row: A$1. The row stays fixed, while the column can change.
Mixed, fixed column: $A1. The column stays fixed, while the row can change.

With a quantity in B2 and a rate in E1, =B2*$E$1 uses a relative quantity reference and an absolute rate reference. Copying the formula down changes B2 to B3, B4, and so on, but keeps E1 unchanged. Locking the entire formula would be a mistake if each row should use its own quantity.

Mixed references are useful when copying across a grid. With row labels in A2:A6, column headings in B1:F1, and a starting formula in B2, the reference =B$1 keeps the heading row fixed as the formula is filled down, but lets the column change when it is filled to the right. Conversely, =$A2 keeps the label column fixed as the formula is copied across, while allowing the row number to change down the grid.

Make a reference constant with F4

In Excel for Microsoft 365 on Windows, F4 cycles through the reference styles while you are editing a formula. Enter the formula, then place the insertion point on the cell reference you want to change. Press F4 until the desired dollar signs appear. If the reference is E1, repeated presses cycle through E1, $E$1, E$1, and $E1, then back to E1.

Start typing a formula in a cell, or select a formula cell and press F2.
Use the arrow keys or mouse to place the cursor on the reference to lock. You can also select the reference text in the formula bar.
Press F4 until you see the reference style you need. For a fully fixed reference, choose $E$1, for example.
Press Enter to finish the formula, then copy or fill it as needed.

If you are using Excel for Mac, Command+T cycles reference styles while editing a formula. Keyboard layouts and macOS function-key settings can affect the function keys, so entering the dollar signs directly is a dependable alternative on either platform. In Excel for the web, shortcut behavior can depend on the browser and keyboard setup; you can always type the dollar signs into the formula yourself.

Examples for common spreadsheet tasks

Apply one tax rate to many prices. With prices in B2:B4 and a tax rate of 8% in E1, enter this in C2: =B2*$E$1. If B2 is 40, the result is 3.2. Copy the formula down and Excel will use B3 and B4 for the next rows while retaining the rate in E1.

Calculate a percentage of a fixed target. With actual sales in B2:B10 and a target in E1, enter =B2/$E$1 in C2. If B2 is 250 and E1 is 1,000, the result is 0.25, or 25% when the result cell is formatted as a percentage. Filling down changes the actual-sales reference but keeps the target fixed.

Build a two-way calculation grid. Suppose the values in A2:A6 are quantities and the values in B1:F1 are rates. In B2, use =$A2*B$1. With A2 equal to 10 and B1 equal to 3, the result is 30. Fill the formula right and down: the fixed A column continues to supply the quantity, while the fixed row 1 continues to supply the rate headings. The other parts adjust to match each position in the grid.

Refer to a fixed cell on another worksheet. With the rate in B2 on a sheet named Rates and quantities in A2:A10 on the current sheet, enter =A2*Rates!$B$2 in B2. If the sheet name contains spaces, surround it with single quotation marks, as in =A2*'Tax Rates'!$B$2. The dollar signs lock the referenced row and column when copied; the sheet name tells Excel where to find the cell.

What a fixed reference does—and does not do

An absolute reference fixes an address during copying and filling; it does not freeze the value stored in that cell. If E1 contains 8% and you change it to 9%, formulas referring to $E$1 recalculate using 9%. This is usually desirable for a shared assumption such as a tax rate, exchange rate, or budget limit.

It also does not make a cell impossible to edit. If you need to prevent people from changing an input, that is a worksheet-protection task, not a reference-style task. By default, cells are marked as locked, but that setting does not take effect until the sheet is protected. On Windows, use Review > Protect Sheet and choose the permissions appropriate for the worksheet. Protection helps control editing; it is not a substitute for encryption or a way to keep workbook information confidential.

Finally, a dollar sign does not make an address immune to every structural change. Copying a formula will keep an absolute reference fixed, but inserting or deleting rows or columns can cause Excel to adjust references to preserve the relationship to the referenced cell. If a workbook’s structure is changing, review important formulas afterward.

Common mistakes and troubleshooting

Locking too much: If you turn B2 into $B$2 in a formula that must calculate using each row’s quantity, copying down will keep pointing to B2. Use an absolute reference only for the part that should stay put.
Pressing F4 at the wrong time: F4 changes a reference while a formula is being edited. If Excel is not in formula-edit mode, the key may perform another action or behave according to your keyboard settings. Press F2 first or click inside the formula bar.
Seeing a reference change after copying: Check whether both parts of the address have dollar signs. A$1 locks only the row; $A1 locks only the column.
Confusing a fixed reference with a fixed result: If the cell’s value changes, formulas using its absolute address update too. To preserve a calculated result as a number, copy the result and use Paste Special > Values, understanding that this replaces formulas with their current results.
Expecting the dollar sign to change formatting: In a formula, $ is part of a cell address when it appears before the column or row, as in $E$1. It does not apply currency formatting to the result. Use the number-format controls on the Home tab to display a value as currency.

Choosing the right way to keep something constant

Choose the method based on what you want to keep unchanged. If formulas should continue referring to one input cell while being copied, use an absolute reference such as $E$1. If one dimension should adjust and the other should not, use a mixed reference such as E$1 or $E1. If the cell’s contents must not be changed by users, protect the worksheet after setting the relevant cell permissions. If you only want headings to remain visible while scrolling, use View > Freeze Panes; this affects the screen view, not formulas or cell values.

For a calculation you no longer want Excel to recalculate, use a copy of the result pasted as a value. This is different from locking a reference: a pasted value is a snapshot and will not respond when its source data changes. That can be useful for a finalized report, but it removes the live link and may make later updates harder. Keep a formula-based version when you need the result to remain auditable or refreshable.

Frequently Asked Questions

What is the Excel symbol for keeping a cell constant?
Use the dollar sign in a cell reference. $A$1 locks both the column and row when the formula is copied; A$1 or $A1 locks only one part.

Is there an Excel function to lock a cell reference?
No dedicated function is needed. Add dollar signs to the reference directly or use F4 on Windows, or Command+T on Mac, while editing the formula.

Why does my absolute cell reference still change its value?
The reference address stays fixed during copying, but the cell’s contents can still be edited. Formulas also recalculate when that content changes. Protect the worksheet if you need to restrict editing.

How do I lock only a row or only a column?
Put the dollar sign before the part you want fixed. A$1 fixes row 1 but lets the column change; $A1 fixes column A but lets the row change.

Can I keep a formula result from changing?
Copy the result and paste it as a value if you need a static snapshot. This removes the formula, so later changes to its input cells will not update that pasted number.

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.