Lock a Number in Excel: Keep It Fixed in Formulas

To keep a number fixed when you copy a formula, make its cell reference absolute by adding dollar signs, such as $A$1. In a formula, click the reference and press F4 on Windows to add the dollar signs. If you mean preventing someone from changing a number in a worksheet, unlock the other cells and then protect the sheet instead.

What You’ll Need

Excel for Microsoft 365 on Windows, or access to a compatible version of Excel.
A workbook with the number you want to keep fixed, ideally in its own cell so you can update it in one place.
For preventing edits, permission to change the worksheet’s protection settings. A password is optional, but store it safely if you choose one.

These are two different ways to lock a number. An absolute reference keeps a formula pointed at the same cell when the formula is copied or filled. Sheet protection stops users from changing cells that are marked as locked. You can use both methods in the same workbook, but neither one substitutes for the other.

Step-by-Step

Put the number you want to reuse in a cell. For example, enter a tax rate, commission rate, or multiplier in E1 and label it clearly. Keeping the value in a cell is easier to maintain than typing it repeatedly into formulas: change the one cell later and formulas referring to it can use the updated value.
Write a formula that refers to the number. With quantities in B2:B10 and a multiplier of 1.08 in E1, enter the formula below in C2. If B2 contains 40 and E1 contains 1.08, the result is 43.2.

With the quantity in B2 equal to 40 and the multiplier in E1 equal to 1.08, enter this in C2; it returns 43.2:

=B2*E1

Make the reference to the multiplier absolute. Double-click C2, or select it and press F2, then click in the formula bar or use the arrow keys to put the insertion point on the E1 reference. Press F4. Excel changes that reference to $E$1. Press Enter to commit the change. On many Mac keyboards, use Command+T to cycle the reference type; depending on keyboard settings, Fn+F4 may also work. If the shortcut does not respond, type the dollar signs yourself.
Check the completed formula before filling it down. With B2 equal to 40 and E1 equal to 1.08, enter this in C2; it returns 43.2:

=B2*$E$1

The dollar signs before both the column letter and row number keep the reference on E1. The B2 reference is still relative, so it can change to B3, B4, and so on as the formula is copied. Select C2 and check the formula bar if you are unsure which version you have.

Copy the formula to the other rows. Select C2 and drag its fill handle—the small square at the lower-right corner of the selection—down through C10. You can also copy C2, select C3:C10, and paste. Each row should use its own quantity while continuing to refer to E1.
Verify a copied result. Select C3 and inspect the formula bar. It should show a reference to B3 and an unchanged reference to $E$1. If B3 contains 25 and E1 is still 1.08, the returned value should be 27. Check a few rows, especially the first and last, before relying on the filled formulas.
Use the right reference type if only one part should stay fixed. F4 cycles through the reference styles: A1, $A$1, A$1, and $A1. Use $A$1 to lock both the column and row; A$1 to lock only row 1; and $A1 to lock only column A. Press F4 until the desired style appears while the reference is selected in the formula.
Protect a cell if you also need to prevent edits. First decide which cells users should be able to change. Select those input cells, press Ctrl+1, open the Protection tab, clear the Locked checkbox, and click OK. On Mac, open Format Cells and use its Protection tab; the exact shortcut can depend on the keyboard. By default, Excel marks cells as locked, but that setting has no effect until you protect the sheet.
Turn on sheet protection. In Excel for Windows, choose Review > Protect Sheet. Choose what users may do, such as select unlocked cells, and optionally enter a password. Confirm the password if Excel asks. Leave the number cell marked as Locked, and the unlocked input cells editable. Test the result by trying to change each type of cell. On Mac, use the Review tab’s Protect Sheet command. In Excel for the web, protection controls and available options can differ; use the Review tab and check the sheet-protection options available in your version.

For example, if E1 holds a rate that only an owner should change, leave E1 locked, unlock the cells where users enter quantities, and protect the sheet. Formulas can still refer to E1 using an absolute reference. Protection controls editing; the dollar signs control how a formula reference behaves when copied.

Common Mistakes

Locking the formula reference but expecting to block edits. Dollar signs do not protect a cell. Someone can still change the value in E1 unless the worksheet is protected and the cell is locked.
Protecting a sheet without unlocking the input cells first. Excel marks cells as locked by default. If you protect the sheet without clearing Locked for the cells people need to fill in, those cells will be blocked too.
Locking too much of a reference. $A$1 freezes both parts. If you want the column to move but the row to stay fixed, use A$1; if the row should move but the column stay fixed, use $A1.
Using a typed-in constant in every formula. A number written directly into a formula stays the same when copied, but changing it later means finding every formula that contains it. A single labeled input cell is generally simpler to review and update.
Assuming worksheet protection is encryption. Sheet protection is intended to prevent routine edits, not to secure confidential information. Do not use it as a substitute for appropriate file access controls.

Tips

Give the fixed number a clear label, such as Standard rate, and use a distinctive cell format or fill color to identify it. Color helps readers but does not lock the value.
Use F4 while editing a formula rather than adding dollar signs after copying many formulas. Check the result in the formula bar because each press cycles to a different reference type.
Consider naming the input cell through the Name Box, left of the formula bar. A meaningful name can make a complex formula easier to read, though an absolute cell reference is often clearest for a simple worksheet.
If you protect a worksheet, make a note of the password somewhere secure. Excel may not be able to recover a forgotten sheet-protection password for you.
Test the workbook as a user would: copy the formula, edit an allowed input, and try to change the protected value. This catches both reference and protection mistakes before you share the file.

Troubleshooting

The fixed number changes when I copy the formula. Select the formula cell and inspect the reference in the formula bar. If it shows E1 without dollar signs, edit it, place the cursor on E1, press F4, and confirm that it becomes $E$1. Then copy the corrected formula again.
F4 inserts a different action or does nothing. Make sure you are editing a formula and that the cursor is on a cell reference. On some laptops, press Fn+F4. On Mac, try Command+T while the reference is selected. You can always type the dollar signs directly in the formula.
I cannot edit a cell after protecting the sheet. The cell is probably still marked Locked. If you know the sheet password, choose Review > Unprotect Sheet, select the cell, clear Locked in Format Cells > Protection, and protect the sheet again. Do not remove protection if you do not have permission.
I can still change the supposedly locked number. Confirm that you protected the correct worksheet, not just the workbook structure. Check that the number cell is marked Locked in Format Cells > Protection, then protect the sheet. A locked setting alone does not block edits.
My formula displays the wrong result even though the reference is fixed. Check that the cell contains a number rather than text, confirm the intended multiplier and arithmetic, and inspect the formula for an accidental reference to a different cell. Absolute referencing keeps a location fixed; it does not validate the value stored there.

Frequently Asked Questions

Does a dollar sign lock the number itself?

No. It locks part or all of a cell reference when a formula is copied. For example, $E$1 keeps a formula pointed to E1, but the value stored in E1 can still be edited unless sheet protection is set up.

How do I lock just a number typed directly in a formula?

A typed-in number, such as 1.08, remains unchanged when the formula is copied, so it does not need an absolute reference. If you may need to revise it, put it in a separate cell and use an absolute reference instead.

Can I lock only a row or only a column?

Yes. In a formula, A$1 keeps row 1 fixed while the column can change. $A1 keeps column A fixed while the row can change. Press F4 repeatedly while editing the reference to cycle through the available styles.

Why are all my cells uneditable after I protect the sheet?

Excel marks cells as locked by default. Before protecting the sheet, select the cells users need to edit, clear Locked in Format Cells > Protection, and then protect the worksheet again.

Does protecting a worksheet stop people from viewing the number?

No. Sheet protection can restrict editing, but it is not a way to hide sensitive data securely. Use suitable workbook access controls if a value must not be seen by other people.

Will absolute references work in Excel for the web?

Yes. Dollar-sign references such as $E$1 are part of standard Excel formulas and work in Excel for the web. The protection interface and some options may differ from the Windows desktop version.

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.