To stop a formula result changing, copy the formula cell or range and paste it back as values: select the cells, press Ctrl+C, then choose Home > Paste > Values. If instead you want a cell reference to stay fixed when you copy a formula, add dollar signs to the reference. These are two different ways to “freeze” something in Excel; the steps below explain when to use each.
Converting a formula to a value replaces the formula with its current result, so it will no longer recalculate. Locking a reference keeps the formula but makes that reference stay pointed at the same cell when you fill or copy the formula. Choose the method that matches what you need before editing a workbook, and consider saving a copy if you may want to restore the formulas later.
What You’ll Need
- A workbook open in Excel for Microsoft 365 on Windows, with the formulas or references you want to change.
- Permission to edit the cells. If the worksheet is protected, you may need the sheet password or help from its owner.
- Optionally, a backup copy of the workbook if you are replacing formulas with fixed results. That change is easy to make but may be difficult to reverse after closing the workbook.
Step-by-Step
- Decide what you mean by “freeze.” If you need a one-time snapshot—for example, a report that must keep the totals as they are today—convert the formula results to values. If you need to copy a formula across rows or columns without moving one of its inputs, lock the cell reference with dollar signs. You can use both techniques in one workbook, but they solve different problems.
- Identify the formula cells before replacing anything. Click a cell and look at the formula bar. If the entry begins with an equals sign and calculates a result, it is a formula. Check the selected range carefully: it may contain headings, manually entered figures, or blank cells as well as formulas. If only some cells should become fixed, select just those cells rather than converting a whole column or table.
- Make a backup if you may need the formulas again. Use File > Save As to create a separate copy, or duplicate the worksheet before changing it. A pasted value has no link to the original calculation, so Excel cannot later recalculate it from the old formula. Keeping a copy is especially useful when you are preparing a month-end report or handing a workbook to someone who should see results but not edit the calculations.
- Copy the formula cells. Select the cell or range containing the formulas and press Ctrl+C on Windows. A moving dashed outline usually indicates that Excel has copied the selection. Do not click a different cell if your intention is to replace the formulas in place; leave the original range selected so you can paste the results over the same cells.
- Paste the current results as values. On the ribbon, choose Home > Paste > Values. The Values option is usually shown with a clipboard icon and a number. You can also right-click the selected range and choose the Paste Values option if it appears in the context menu. The formula cells now contain their calculated results rather than their formulas. In Excel for the web, use the Home tab’s paste options or the right-click Paste values option if available; labels and availability can vary slightly by browser and interface.
- Use Paste Special if you need a specific paste option. On Windows, with the copied range still selected, press Ctrl+Alt+V to open Paste Special, choose Values, and press Enter. This avoids choosing a normal paste that would carry formulas across. If you need the displayed number formats as well as the results, choose Values & Number Formats instead when that option is available. A plain Values paste generally uses the destination cells’ existing formatting, so a date or percentage may look different if the destination format is different.
- Verify that the formulas are gone. Click one of the changed cells and inspect the formula bar. It should show a value, such as a number or date, rather than an expression beginning with an equals sign. If you see the original formula, undo once with Ctrl+Z and repeat the paste using Values. Check a few cells across the range, not just the first one, especially if formulas were mixed with typed entries.
- Lock a cell reference when you want a formula to copy correctly. Suppose B2 contains a measurement and F1 contains a conversion factor. With that layout, enter
=B2*$F$1in C2. If B2 is 3 and F1 is 100, the result in C2 is 300. The dollar signs make both the column F and row 1 absolute, so copying the formula down changes B2 to B3, B4, and so on, but keeps the factor at F1. - Apply the reference lock before filling the formula. While editing a formula, click or select the cell reference you want to lock, then press F4 on Windows to cycle through relative, absolute, and mixed-reference forms. You can also type the dollar signs yourself. In the example,
=B2*$F$1locks both parts of F1. A reference such as=B2*F$1locks only row 1, while=B2*$F1locks only column F. On a Mac, F4 may require the Fn key depending on the keyboard settings; typing the dollar signs manually works there too. - Test copied formulas before relying on them. Fill the formula down a few rows using the fill handle or copy and paste, then inspect the results and formula bar. Confirm that references intended to move have moved and references intended to stay fixed have not. Locking a reference does not turn its value into a permanent number: if the contents of F1 change, formulas using F1 will recalculate with the new factor.
Common Mistakes
Confusing a fixed result with a fixed reference. Paste Values removes the formula and preserves only the current result. Dollar signs preserve a reference pattern while leaving the calculation live. If a result must update when its inputs change, do not replace its formula with a value. If a formula copied down keeps pointing to the wrong input cell, use an absolute or mixed reference instead.
Using ordinary paste instead of Paste Values. Ctrl+V normally pastes formulas, not just their displayed results. If you paste the copied range over itself using ordinary paste, the formulas remain formulas and will continue recalculating. Choose the Values option explicitly and check the formula bar afterward.
Overwriting the wrong cells. When you paste values onto the original range, every selected formula is replaced. Selecting an entire sheet column by mistake can also affect entries you did not intend to change. Confirm the selection’s outline and range address before pasting. If the wrong cells change, press Ctrl+Z immediately, before making other edits.
Expecting Paste Values to preserve the source appearance. A value-only paste is intended to paste results, not copy every aspect of the original cells. A number that was displayed as a date, percentage, or rounded amount can look different if the destination has a different number format. Choose Values & Number Formats when you need the result and its number format, or format the destination separately.
Locking too much or too little of a reference. $F$1 locks both the column and row. F$1 locks only the row, and $F1 locks only the column. A misplaced dollar sign can cause copied formulas to reference an unintended cell. Check the formula bar after filling, especially when copying both across and down.
Assuming a locked reference freezes the source cell. Absolute references control how a formula’s address changes when copied; they do not protect the source cell from edits. If F1 changes, a formula referring to $F$1 uses the new contents. To keep a result fixed against later input changes, paste the result as a value instead.
Tips
- Keep a live and a fixed version when useful. Duplicate the worksheet or copy the formula results to a separate report area before converting the original cells. Label the snapshot with a date so readers know when its values were captured.
- Use the formula bar as your check. The displayed cell value alone cannot tell you whether it comes from a formula or a hard-coded value. Selecting the cell and checking the formula bar is a quick way to distinguish them.
- Use Paste Values for a whole selected range. It is usually faster and more consistent than editing formulas one by one. For a small number of cells, you can also copy a result and paste values into a different location if you want to preserve the formula in its original cell.
- Use mixed references when copying in two directions. A row-locked reference can be useful when a fixed input is in a header row; a column-locked reference can be useful when a fixed input is in a side column. Decide which part should move before dragging a formula across or down.
- Remember that values may still be editable. Pasting a value makes the result independent of the old formula, but it does not protect the cell from someone typing over it. If the goal is to prevent edits, that is a separate worksheet-protection task.
- Keyboard shortcut differences matter. Ctrl+C and Ctrl+Alt+V are Windows shortcuts. On a Mac, use Command+C to copy and use the Excel ribbon or context menu to choose Paste Values; function-key behavior can depend on your keyboard settings. Excel for the web also has its own browser and clipboard behavior, so the ribbon option is a reliable alternative to relying on a desktop shortcut.
Troubleshooting
The formula is still recalculating after I pasted. You may have used regular Paste rather than Paste Values, or pasted into a different range and left the original formula in place. Select the intended cells, copy again, choose Home > Paste > Values, and inspect the formula bar. If a formula remains, undo and repeat carefully.
The displayed result changed after pasting. First check whether the cell’s number format changed or whether the original formula was still present. A value-only paste may use the destination format, which can make the same underlying number appear as a general number instead of a date or percentage. Apply the appropriate number format from Home > Number, or repeat using Values & Number Formats if you need the source number format too.
I cannot paste into the cells. Check whether the sheet is protected, whether the workbook is in a restricted editing mode, and whether the copied and destination ranges have compatible shapes. If Excel displays a protection warning, ask the workbook owner to unlock the cells or provide permission. Do not work around protection by moving data into unrelated cells; that can disrupt formulas and references.
The copied formula points to the wrong cell. Review the reference in the formula bar. If an input should stay in the same place, add dollar signs to the row, column, or both. If the formula was already filled down, correct the first formula and fill it again, then verify several results. Absolute references do not repair a formula that originally points to the wrong cell.
I replaced formulas but need them back. If you have not closed the workbook or made many subsequent changes, press Ctrl+Z until the formulas return. Otherwise, reopen the backup, use version history if the file is stored in OneDrive or SharePoint, or restore the formula from another copy of the workbook. Pasted values do not contain enough information for Excel to reconstruct the original calculation.
Frequently Asked Questions
Does freezing a value stop Excel from recalculating it?
Yes, if you replace a formula with its value using Paste Values. The cell becomes a constant and no longer recalculates from the former inputs. Locking a reference with dollar signs does not stop recalculation.
Can I freeze only some formula results?
Yes. Select only the cells you want to convert, copy them, and paste values onto that same selection. Leave other formulas out of the selection, and verify the changed cells afterward.
Will Paste Values keep the cell’s formatting?
A plain value-only paste does not necessarily carry the source formatting; the destination’s existing format can affect how the result appears. Choose Values & Number Formats if you need the number format copied as well, or format the cells after pasting.
What do the dollar signs in an Excel formula do?
They lock part of a cell reference when the formula is copied. In $F$1, both the column and row stay fixed; in F$1, only the row stays fixed; in $F1, only the column stays fixed. The referenced cell can still change, and formulas that use it can recalculate.
Can I freeze a formula result without deleting the original formula?
Yes. Copy the formula cells to a different range or worksheet, then paste values there. The original formulas remain live, while the copied area becomes a snapshot of their current results.
Can I undo Paste Values?
Usually, press Ctrl+Z promptly on Windows, or Command+Z on a Mac. If you have closed the workbook or made further changes, undo may no longer be available; use a saved copy or version history to recover the formulas.
Related
- Goal Seek in Excel: How to Find and Use It Step by Step
- Find the Financial Year From a Date in Excel Easily
- How to Find Errors in Excel: A Practical Workbook Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR with AVERAGE in Excel: Formulas and ExamplesTo use IFERROR with AVERAGE in Excel, wrap the average calculation in IFERROR: with the values to average…
- Sort an Entire Excel Spreadsheet by One ColumnTo sort an entire Excel spreadsheet by one column, click a cell in your data, choose Data >…
- Excel Filter Duplicates Without Deleting Data SafelyYes. In Excel for Microsoft 365 on Windows, add a helper column that identifies repeated values, then choose…
- Microsoft Excel Calendar Formula: Build a Monthly ViewIn Microsoft Excel for Microsoft 365, enter a year in B1 and a month number in C1, then…
- Power Query Rounding: How to Round Numbers CorrectlyIn Power Query, select a numeric column and use Transform > Rounding > Round to round it in…
- Concatenate in Excel: Formulas, Delimiters, and TipsTo concatenate in Excel means to join text or values into one text string. In Microsoft Excel for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.