Freeze a Formula in Excel: Keep Cell References Fixed

To freeze a cell reference while copying a formula in Excel, edit the formula, select the reference, and press F4 to add dollar signs; to freeze a formula’s current result instead, copy the cell and use Home > Paste > Paste Values. These are different tasks: dollar signs keep a reference from moving, while Paste Values replaces the formula with its calculated value.

This guide is for Microsoft Excel for Microsoft 365 on Windows. First decide which result you want: a formula that keeps using the same input cell, or a number that no longer changes when its inputs change. The steps also note the main differences on Mac and Excel for the web.

What You’ll Need

An Excel workbook with a formula or a formula you are ready to enter.
For locking a reference, know which cell should stay fixed when the formula is copied.
For freezing a result, make a copy of the workbook or keep an untouched copy of the formula cells. Replacing formulas with values cannot be reversed after you close the workbook.

Step-by-Step

Choose what you mean by freeze. Use an absolute reference when the formula should continue calculating but always point to the same input cell, such as a tax rate, exchange rate, or fixed target. Use Paste Values when you want to keep the current displayed result and stop it recalculating. If you meant keeping headings visible while scrolling, use View > Freeze Panes instead; that is a worksheet display feature and does not change formulas.
Set up a small example if you are locking a reference. Put a price in B2 and a rate in E1. For this example, assume B2 contains 120 and E1 contains 8%. Click C2 and enter the following formula, then press Enter. With the price in B2 and the rate in E1, the formula returns 9.6 because 120 multiplied by 8% is 9.6: =B2*$E$1. The dollar signs make E1 absolute, so that reference will not shift when you fill the formula elsewhere.
Edit the formula you want to adjust. Click the formula cell and press F2, or click in the formula bar. You can also double-click the cell. Make sure you are editing the formula itself, not just selecting the cell. Locate the reference you want to hold in place, such as E1 in the example.
Apply the dollar signs to the reference. Click within the E1 reference in the formula bar or formula, then press F4. Excel cycles through E1, $E$1, E$1, and $E1. Stop at $E$1 if both the column and row must stay fixed. Press Enter to finish editing. If the reference is already fully fixed, F4 cycles it to another form, so check the result rather than pressing the key repeatedly without looking.
Copy the formula to other cells. Select the formula cell and drag its fill handle, the small square at the lower-right corner, across or down the range. You can also copy and paste it. The relative reference B2 changes as the formula moves, while $E$1 stays fixed. For example, a formula filled one row down uses the next row’s price but continues to use E1. Check at least one copied cell to confirm the intended reference did not shift.
Use a partly fixed reference only when needed. In a reference such as E1, F4 changes both the column and row at once, then cycles to forms that lock only one part. Choose E$1 when copying down should keep row 1 fixed but copying sideways may change the column. Choose $E1 when the column should stay E but the row may change. These mixed references are useful in tables where a formula is filled in two directions.
Freeze a calculated result as a value. Select the formula cell or range and press Ctrl+C. Keep the same range selected, then choose Home > Paste > Paste Values. In many Microsoft 365 builds, the Paste Values button is shown as a clipboard with 123; you can open the Paste menu to identify it. The cells now contain the results, not the original formulas. If you need to preserve the formulas elsewhere, paste the values into a separate range instead of over the source.
Verify which kind of freeze you made. For a locked reference, select the cell and inspect the formula bar for dollar signs in the right place; then change the referenced input or fill the formula to confirm it behaves as intended. For a fixed value, select the cell and check that the formula bar shows a value rather than a formula beginning with an equals sign. If the result was formatted as a date, percentage, or currency, confirm the displayed format is still suitable.

On a Mac, the F4 key may require the Fn key depending on your keyboard settings; Command+T is also used in Excel for Mac to cycle reference types while editing a formula. On a laptop, try Fn+F4 if F4 controls a hardware feature. In Excel for the web, function-key behavior can depend on the browser and keyboard. If the shortcut does not change the reference, type the dollar signs directly, or use the Paste Values command from the Home tab.

Common Mistakes

Confusing fixed references with fixed results. Adding dollar signs does not save a snapshot of a calculation. A formula with $E$1 still recalculates if E1 or another input changes. Paste Values is the method for keeping the current result.
Locking the wrong part of a reference. $E$1 locks both column and row. If you only need to lock row 1, E$1 is different; if you only need column E, use $E1. The wrong pattern can make a copied formula point to unexpected cells.
Pressing F4 when the cursor is not on a cell reference. Excel may not change the reference if the insertion point is elsewhere in the formula. Click directly inside the reference in the formula bar, or select the reference text while editing.
Pasting values over the only copy of important formulas. Once overwritten and saved, the original formulas may be difficult or impossible to recover. Save a copy first, or paste values in a new location and compare the results before replacing the source.
Using ordinary Paste instead of Paste Values. A normal paste copies the formula, so it may still update or shift its references. Use the explicit Paste Values command and inspect the formula bar afterward.
Assuming dollar signs protect a cell from editing. They only control how a reference behaves when a formula is copied. To restrict edits, worksheet protection is a separate feature, and it should be configured deliberately.

Tips

Use F4 while building the formula. You do not have to wait until the formula is complete. Type the formula up to the reference, select or point to that reference, press F4 to cycle its style, then finish entering the formula.
Keep calculation inputs in clearly labeled cells. A fixed reference is easier to audit when its input cell has a label such as Rate or Target. Consider applying a distinct fill color to input cells, and avoid inserting or moving cells without checking formulas that depend on them.
Test with a short range first. Fill a formula into two or three cells and inspect the references before copying it through a large worksheet. This quickly reveals whether you need a relative, absolute, or mixed reference.
Paste values into a backup range when results need to be retained. This preserves a record of the calculated output while leaving the live formulas intact. Add a date or a brief note nearby if the snapshot needs context.
Remember that displayed rounding may not equal the stored value. A cell may show 10.00 while holding additional decimal places. Paste Values keeps the calculated number, not necessarily only the rounded digits visible on screen. If you require rounded values, apply an appropriate rounding formula before converting to values.

Troubleshooting

F4 changes the laptop volume or brightness: Your keyboard may reserve the function keys for hardware controls. Try Fn+F4, check the keyboard’s function-lock setting, or enter the dollar signs manually in the formula. In Excel for Mac, use Command+T while editing the reference if that shortcut is available in your setup.

The reference keeps changing when I fill the formula: Select the source formula and inspect each reference. A reference without dollar signs is relative and is expected to move. Press F4 while the cursor is on the relevant reference until the required dollar-sign pattern appears. Then refill the destination cells and check their formulas.

The formula result still changes after I locked the reference: That is normal. Locking $E$1 holds the reference address steady; it does not lock the value stored in E1 or the other inputs. If you need an unchanging snapshot, copy the result and use Paste Values. Check whether other referenced cells, named ranges, or volatile calculations also affect the result.

Paste Values is unavailable or does not replace the formula: Confirm that the copied cells are still selected and that you opened the Paste menu on the Home tab. Copy the source again if the copy selection has been lost. If a worksheet is protected, permissions may prevent editing; check with the workbook owner before changing protection. In Excel for the web, use the Home tab’s Paste menu if a keyboard shortcut behaves differently in your browser.

The value looks different after pasting: The destination may have a different number format, or the original formula may have returned text, a date, or a rounded display. Check the formula bar and the Number Format controls on the Home tab. Paste Values generally transfers the result rather than the original cell’s formatting, so apply the needed number format separately.

I overwrote a formula by mistake: If the workbook is still open, use Ctrl+Z immediately on Windows or Command+Z on Mac. If the change has already been saved and closed, restore a prior workbook copy or use the available version history if the file is stored in OneDrive or SharePoint. Avoid making further edits until you know whether a recoverable version exists.

Frequently Asked Questions

What does it mean to freeze a formula in Excel?
It can mean locking a cell reference with dollar signs so it stays put when copied, or replacing a formula with its current value. Choose based on whether you want future recalculation.

How do I freeze a cell reference without F4?
Edit the formula and type dollar signs around the row or column you want to lock. For example, $E$1 locks both, E$1 locks the row, and $E1 locks the column.

Does F4 freeze the formula result?
No. F4 cycles the reference style in a formula while you edit it. To keep a result as a fixed value, copy the cell and use Home > Paste > Paste Values.

Can I lock only a row or only a column?
Yes. Use a mixed reference: E$1 keeps row 1 fixed, while $E1 keeps column E fixed. This is useful when copying formulas across a grid rather than in only one direction.

Will a formula with dollar signs update when its inputs change?
Yes. The formula recalculates when its inputs change; the dollar signs only stop that particular reference from adjusting as the formula is copied or filled.

Can I undo Paste Values?
Usually you can undo it with Ctrl+Z on Windows or Command+Z on Mac as long as the action is still in the undo history. Save a backup before replacing many formulas, because closing the workbook or performing later actions may remove the practical opportunity to undo.

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.