Freeze a Number in an Excel Formula with $ References

Formulas & functions · Updated November 20, 2026

To freeze a number’s cell reference when you copy an Excel formula, add dollar signs to the reference—for example, change F1 to $F$1. In Excel for Microsoft 365 on Windows, edit the formula, select the reference, and press F4 to cycle through reference types. The dollar signs keep the reference from shifting; they do not stop the value in that cell from changing.

If you mean that a calculation should always use a particular numeric value, you can also type that value directly into the formula. The steps below explain both options, so you can choose the one that matches what you want to stay fixed.

What You’ll Need

  • An Excel workbook with a formula you plan to copy or fill into other cells.
  • A clear idea of what should stay fixed: the location of a cell, such as a tax rate in F1, or the numeric value itself.
  • For the shortcut method, a keyboard with an F4 key. On some laptops, you may need to hold Fn as well.

Step-by-Step

  1. Identify what needs to stay fixed. If a formula should keep reading the same cell as you copy it, use an absolute reference with dollar signs. For instance, a tax rate kept in F1 should remain linked to F1 in every copied formula. If the source cell changes later, formulas using it will update to reflect the new value.

    If the calculation must use a literal value that will not change when a source cell is edited, enter the number directly in the formula instead. This is less flexible: changing the rate later means finding and changing each formula that contains it.

  2. Set up a simple example. Put an amount of 100 in B2 and a tax rate of 8% in F1. Select C2 for the result. With the amount in B2 and the rate in F1, enter =B2*(1+$F$1) in C2. The result is 108: 100 multiplied by 1.08. In this formula, B2 is allowed to move when copied, while $F$1 stays anchored.

  3. Enter or edit the formula. To start a formula, select its destination cell, type an equals sign, and build the calculation using cell references. If you already have a formula, double-click its cell or select it and edit in the formula bar. You can also press F2 on Windows to edit the selected cell. Find the reference you want to hold in place.

  4. Select the reference—not the whole formula. While editing, click or use the arrow keys to place the insertion point within the reference, such as F1. You can select the reference text if that is easier. Make sure you do not accidentally select a different reference or part of a number. The shortcut changes the selected reference’s style.

  5. Press F4 to add dollar signs. In Excel for Microsoft 365 on Windows, F4 cycles through the reference styles. Starting with F1, successive presses cycle through $F$1 (fixed column and row), F$1 (fixed row only), $F1 (fixed column only), and F1 (relative reference). Stop when the reference has the dollar signs you need. For the tax-rate example, use $F$1.

    On a laptop, you may need Fn+F4 if the F keys control brightness or other system features. On a Mac, try Fn+F4; the precise key behavior can depend on the keyboard settings. If the shortcut is unavailable or intercepted, type the dollar signs directly in the formula. In Excel for the web, manual editing is also a reliable fallback.

  6. Confirm the formula. Press Enter to finish editing. Check the result, then select the cell and inspect the formula bar to make sure the dollar signs are still present. If the formula shows an error, verify that you placed the signs inside the cell reference and did not remove a parenthesis or operator.

  7. Fill or copy the formula. Select the formula cell, then drag its fill handle—the small square at the lower-right corner of the selection—down or across. You can also copy the cell and paste it into the destination range. In the tax example, copying C2 to C3 should adjust B2 to B3 while leaving $F$1 unchanged. That lets each row use its own amount and the same tax-rate cell.

  8. Check a copied formula. Select one of the destination cells and look at the formula bar. Confirm that the relative reference changed as expected and the absolute reference did not. For example, after copying the example formula from C2 to C3, the formula should use B3 and $F$1. Verify the displayed result against the input values before relying on a larger fill.

  9. Choose a mixed reference when only one part should stay fixed. A dollar sign before the column letter fixes the column; one before the row number fixes the row. For a small table, assume A2 contains 4, row 1 has a multiplier of 3 in B1 and 5 in C1, and the formula is entered in B2. Use =$A2*B$1 in B2; the result is 12. When filled right, the reference to the header moves from B$1 to C$1. When filled down, $A2 moves to $A3. This is useful for multiplying row values by column headings.

  10. Use a typed constant only when that is intentional. With an amount in B2, enter =B2*1.08 in C2 to multiply it by 1.08. If B2 contains 100, the result is 108. Copying the formula keeps 1.08 unchanged while the relative reference B2 can adjust. This is appropriate for a deliberate fixed factor, but a cell reference is usually easier to maintain when the rate might need updating.

Common Mistakes

  • Locking the wrong part. F1 is relative, $F$1 locks both the column and row, $F1 locks only the column, and F$1 locks only the row. Use the version that matches the direction in which you will copy the formula.

  • Assuming an absolute reference freezes the cell’s value. $F$1 keeps the formula pointed at F1, but if someone changes the value in F1, the formula recalculates. It anchors the address, not the contents.

  • Adding dollar signs to a number instead of a reference. Dollar signs in Excel formulas mark absolute cell references. They are not a way to make a numeric literal permanent. A typed constant such as 1.08 remains as typed when a formula is copied, but it can still be changed by editing the formula.

  • Pressing F4 when the reference is not selected. While editing, F4 changes a cell reference only when the insertion point is within that reference. If it does not produce the expected dollar signs, position the cursor inside the address and try again, or type the signs manually.

  • Confusing reference locking with Freeze Panes. Freeze Panes keeps worksheet rows or columns visible while you scroll; it does not affect formulas. To hold a formula reference, use dollar signs in the reference.

Tips

  • Use an absolute reference for a shared input such as a tax rate, discount rate, commission percentage, or exchange rate stored in one cell. Give that input a label so it is easy to identify and update.

  • Plan for the direction of copying. If a formula will be filled down, a fixed row may be enough; if it will be filled across, a fixed column may be enough. If it will be copied in both directions and must always point to one cell, lock both.

  • Use F4 as a cycle, not a one-time “add dollar signs” button. Check which of the four reference types is showing before you press Enter, especially when editing formulas with several references.

  • Keep changeable assumptions in dedicated cells rather than embedding constants across many formulas. If a rate changes, updating one input cell is usually clearer and less error-prone than editing every formula.

  • After filling a large range, spot-check formulas near the beginning, middle, and end. A reference that looks right in the first row can still shift incorrectly if the formula was copied in an unexpected direction.

Troubleshooting

  • The reference changes after copying. Edit the original formula and add dollar signs to the part that should stay put. For a single fixed cell, use the fully absolute form, such as $F$1. Then refill the destination cells from the corrected formula.

  • F4 inserts a symbol or changes a system setting. On a laptop, try Fn+F4. Check your keyboard’s function-key setting if needed. You can avoid the shortcut entirely by editing the reference directly—for example, change F1 to $F$1—and then confirm the result in the formula bar.

  • The formula points to the wrong cell after filling. Check whether you need a mixed reference. A fixed column can be useful when filling across, while a fixed row can be useful when filling down. If both dimensions must remain unchanged, lock both. Correct the source formula, then copy it again.

  • The result changes even though the reference is absolute. Confirm whether the value in the anchored cell changed. An absolute reference recalculates when its source changes, and the other references in the formula may also move. To keep a literal number in the calculation, type the constant into the formula; to preserve a past result rather than a live calculation, copy the result and use Paste Special > Values.

  • You see a formula instead of its calculated result. Check whether the cell is formatted as Text or whether Show Formulas is on. Set the cell format to General, edit the formula, and press Enter. On Windows, Ctrl+` toggles Show Formulas; on Mac, the equivalent shortcut may vary by keyboard, so use Formulas > Show Formulas if available. Make sure the entry begins with an equals sign.

Frequently Asked Questions

What does it mean to freeze a number in an Excel formula?

Usually, it means preventing a cell reference from changing when you copy a formula. Add dollar signs to lock the row, column, or both. Sometimes people mean keeping a numeric constant fixed; typing the constant directly into the formula does that when copying, but it does not make the formula uneditable.

How do I lock a cell reference in Excel without F4?

Type dollar signs into the reference while editing the formula. For example, change F1 to $F$1 to lock its column and row. Use $F1 to lock only the column or F$1 to lock only the row.

Does $F$1 stop the value in F1 from changing?

No. It keeps the formula pointing to cell F1 when copied. If F1’s contents change, Excel recalculates formulas that refer to it. To preserve a result as a fixed value, copy the result and paste values, or use a deliberate numeric constant in the formula.

What is the difference between $F$1 and F$1?

$F$1 locks both the column and row. F$1 locks only row 1, so the column letter can change when the formula is copied sideways. Use F$1 when a formula should keep using the same header row across several columns.

Can I freeze a number in a formula when copying it down?

Yes. Use an absolute reference for a shared input, such as $F$1, or use a mixed reference if only the row or column needs to stay fixed. A literal number in the formula also stays the same as the formula is copied, but a reference is easier to update later.

Is freezing a formula reference the same as Freeze Panes?

No. Dollar signs control how cell references behave when formulas are copied. Freeze Panes is a worksheet view feature that keeps selected rows or columns visible while you scroll.

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.