To keep a number fixed when you copy a formula, put the number in a worksheet cell and lock that cell reference with dollar signs: change C2 to $C$2. In Excel for Microsoft 365 on Windows, you can select the reference in the formula bar and press F4 to add the dollar signs. If you mean a number that should stay constant inside the formula itself, enter it directly, but using a separate input cell is usually easier to update.
What You’ll Need
- A workbook in Excel for Microsoft 365, with the value you want to keep fixed entered in a cell.
- A formula that uses that value and will be copied or filled into other cells.
- Optionally, a mouse or keyboard shortcut for changing a relative reference to an absolute reference.
You do not need to enable a special setting. The dollar signs are part of the cell reference in the formula; they do not lock the worksheet cell against editing. If you want to prevent people from changing the value itself, see the note about worksheet protection in Tips.
Step-by-Step
- Set up the value you want to keep fixed. Put the input in its own cell and label it so you can identify it later. For example, enter a tax rate, discount rate, or conversion factor in C2, and add a label in B2. Keeping the value in a cell makes it easy to change later without editing every formula. The example below uses a rate of 10% and sales values in column B.
- Enter a formula that refers to both the changing value and the fixed input. Assume B2:B4 contains sales of 100, 200, and 300, and C2 contains 10%. In D2, enter
=B2*(1+C2). With the specified values, D2 returns 110. The reference B2 is relative: it should move to B3 or B4 as the formula is copied. The reference C2 should stay put because it contains the rate. - Make the input reference absolute. Click in the formula bar or press F2 to edit the formula in D2. Click or place the insertion point within the C2 reference, then press F4. Excel changes that reference to $C$2. The formula in D2 should now be
=B2*(1+$C$2). The dollar sign before C locks the column, and the dollar sign before 2 locks the row. With B2 equal to 100 and C2 equal to 10%, this still returns 110. - Finish editing and check the formula. Press Enter. Select D2 and look at the formula bar to confirm that the reference is $C$2, not C2. A formula that displays 110 can look correct even when the reference is not locked, so checking the formula text is useful before filling it down.
- Copy the formula to the other rows. Select D2 and drag the fill handle—the small square at the lower-right corner of the selected cell—down through D4. You can also copy D2, select D3:D4, and paste. In D3, Excel adjusts the sales reference to B3 but keeps the input reference at $C$2. Assuming B3 is 200 and C2 remains 10%, D3 returns 220. For B4 equal to 300, D4 returns 330.
- Check a copied formula before relying on the results. Select D3 or D4 and confirm that the row-specific reference changed while $C$2 remained the same. If every row points to B2, the changing reference was locked by mistake. If the rate reference changes to C3 or C4, it was not made absolute. Correct the first formula and fill again if necessary.
Windows shortcut: While editing a formula, place the cursor in or select the cell reference and press F4. If Excel is on a laptop, you may need to hold Fn and press F4, depending on the keyboard’s function-key settings. Pressing F4 repeatedly cycles through the reference styles: $C$2, C$2, $C2, and C2. Stop when the style you need appears.
Mac and Excel for the web: In Excel for Mac, Command+T cycles reference styles while you edit a formula; some keyboards also support F4 or Fn+F4, depending on their settings. In Excel for the web, shortcuts can depend on the browser and keyboard, so typing the dollar signs into the reference is a reliable alternative. The formula syntax is the same.
Common Mistakes
- Locking the wrong part of the reference. $C$2 locks both the column and row. $C2 locks only the column, while C$2 locks only the row. If you are filling a formula down and need the same input cell every time, use $C$2.
- Locking the changing reference instead of the input. If you turn B2 into $B$2 in the example, every copied formula keeps calculating from the first sales value. Usually, lock the reference to the shared assumption and leave the row-specific data reference relative.
- Assuming dollar signs protect a cell from being changed. They control how a reference adjusts when a formula is copied. They do not prevent someone from typing over the referenced value or changing the formula.
- Typing a constant into many separate formulas. A value such as 10% can be entered directly, but changing it later means finding and updating each formula. A single input cell with an absolute reference is easier to maintain and audit.
- Using the wrong reference style for the direction of copying. A row-only or column-only lock can be useful in a two-way table, but it will not behave like a fully fixed reference when copied in both directions. Choose the style based on whether the row, column, or both must stay unchanged.
Tips
- Use a clearly labeled input cell. A label such as “Rate” next to C2 helps someone reviewing the sheet understand why the formula refers to that cell. Consider using a distinct fill color for input cells, and keep the value in one place rather than repeating it in formulas.
- Distinguish a fixed cell reference from a literal number. If A2 contains 50 and you intentionally want to multiply by a fixed factor of 1.1, you can use a literal. With A2 equal to 50, enter
=A2*1.1in B2; it returns 55. When copied down, A2 adjusts to A3, while 1.1 remains unchanged because it is a typed constant, not a cell reference. Use this for genuinely permanent constants, not values likely to change. - Consider a named range for important assumptions. A descriptive name can make a formula easier to read than a cell address. Create a name through Formulas > Define Name, then use that name in formulas. A defined name generally remains the same when a formula is copied, so the dollar-sign method is not required for that named input.
- Use worksheet protection if the input must not be edited. Absolute references do not provide that protection. To restrict edits, unlock cells that users should be able to change, then use Review > Protect Sheet. Set a password only if appropriate and keep it somewhere safe; protection is different from locking a formula reference.
- Check the number format separately. The formula calculates a value, while formatting controls how it appears. If a rate or result looks wrong, select the cell and check Home > Number to see whether it is formatted as a percentage, currency, or another number type.
Troubleshooting
The value changes when I copy the formula. Select a copied cell and inspect its formula. If the input reference has shifted from C2 to C3, edit the original formula so the reference reads $C$2, then fill it down again. If you only want to lock the row or the column, use C$2 or $C2 instead.
F4 does not add dollar signs. First make sure you are editing the formula and that the cursor is on the reference, rather than simply selecting a worksheet cell. On some laptops, use Fn+F4. On Mac, try Command+T or the keyboard’s Fn+F4 combination. If the shortcut is intercepted by your device or browser, type the dollar signs directly around the column letter and row number.
Excel shows an error after I edit the formula. Check that the reference is written as $C$2, with the dollar signs before the column letter and row number. Make sure the formula begins with an equals sign and that parentheses are balanced. If the result is a number but seems incorrect, verify the input value and its number format; 10% is stored as 0.1, not 10.
All copied rows show the same result. Check whether the changing data reference was accidentally made absolute. In the example, the formula in D3 should refer to B3, not $B$2. Remove the dollar signs from the data reference, leave them on the fixed input reference, and fill down again.
I want a fixed number, not a fixed cell. Type the number into the formula if it is a permanent constant, or place it in a separate input cell and use an absolute reference if you may update it. If the value is copied into different formulas, the cell-reference approach makes later changes much less error-prone.
Frequently Asked Questions
What does locking a number in an Excel formula mean?
Usually, it means making a cell reference absolute so that it does not shift when you copy the formula. Add a dollar sign before the column and row, as in $C$2. It does not lock the cell against editing.
How do I lock a number in Excel without using F4?
Edit the formula and type a dollar sign before the column letter and before the row number. For example, change C2 to $C$2. This works in desktop Excel and Excel for the web.
What is the difference between $C$2, $C2, and C$2?
$C$2 fixes both the column and row. $C2 fixes the column but lets the row adjust. C$2 fixes the row but lets the column adjust. Choose based on the direction you will copy the formula.
Can I lock only the number in a formula, rather than a cell reference?
Yes. A typed constant, such as 1.1 in a multiplication formula, stays the same when the formula is copied. But if you expect the value to change, storing it in a cell and using an absolute reference is usually simpler to maintain.
Does locking a reference stop other people from changing the value?
No. Dollar signs only control how references adjust when formulas are copied. To restrict editing, use worksheet protection and configure which cells can be changed.
Related
- Insert 0 in Excel Before a Number: Text or Formatting
- How to Indent a New Line in an Excel Cell: Easy Steps
- How to Indent in Excel Without Changing Cell Contents
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Positive Numbers in Excel: Formula and ExamplesTo sum positive numbers in Excel, use SUMIF with the criterion ">0". If the numbers are in B2:B10,…
- Excel DATEDIF Age Formula: Calculate Years, Months, DaysTo calculate someone’s age in completed years in Excel, use =DATEDIF(A2,TODAY(),"Y"), with the date of birth in A2.…
- Excel Formula for Transpose: 3 Easy WaysThe simplest Excel formula for transposing data is =TRANSPOSE(array), entered as an array formula by pressing Ctrl+Shift+Enter (or…
- Calendar in Excel: Create a Monthly Calendar With a FormulaTo put a calendar in Excel, enter a year in B1 and a month number in B2, add…
- Excel MAX of Filtered Data: Formula, Limits, and FixesIf your numbers are in B2:B100, use =SUBTOTAL(4,B2:B100) to return the largest value among rows currently shown by…
- Excel COUNTIFS With Multiple Criteria in One RangeYes. Excel’s COUNTIFS can apply more than one condition to the same range by repeating that range for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.