To delete a formula but keep its calculated result in Excel, select the formula cells, copy them, then paste values back over the same cells. In Excel for Microsoft 365 on Windows, use Home > Paste > Values. The formulas are replaced with fixed values, so the results stay visible but no longer update when referenced cells change. Save a copy first if you may need the formulas later.
What You’ll Need
- An Excel workbook with the formulas you want to replace.
- Permission to edit the worksheet. If it is protected, you may need the password or an unprotected copy.
- Optional: a backup copy, especially if the formulas are difficult to recreate or the workbook is important.
Step-by-Step
- Check what you are about to change. Open the worksheet and identify the cells containing formulas. Click a few cells and look at the formula bar above the grid: a formula normally begins with an equals sign, while a fixed value appears as a number, date, or text entry. Confirm that the displayed results are the results you want to keep. This matters because pasting values freezes the current calculation, not a future result.
- Select the formula range. Click the first cell, then drag to the last cell, or click the first cell and hold Shift while clicking the last. You can also click the first cell, hold Shift, and use the arrow keys to extend the selection. For a whole column of formulas, select only the cells that should be converted rather than the entire worksheet column, unless you deliberately want to replace everything in it.
- Copy the selected cells. Press Ctrl+C on Windows. A moving dashed border usually appears around the copied range. Copying is necessary because Paste Values takes the copied results and places them in the selected destination. Do not press Delete or Backspace: those commands clear cell contents and will not preserve the calculated result.
- Paste values over the same cells. Keep the copied range selected, then choose Home > Paste > Values. In the Paste dropdown, choose the Values option, commonly shown with a clipboard marked 123. The formulas in the copied cells are replaced by their current results. Existing manually entered numbers or text in the selected range remain the same values.
- Use Paste Special if you prefer the dialog. With the range copied, press Ctrl+Alt+V to open Paste Special, choose Values, and select OK. In the standard Windows dialog, you can also press V to select Values and then press Enter. This method is useful if you want to see the available paste options before committing. Avoid choosing Formulas, which would keep formulas rather than convert them.
- Check the result in the formula bar. Click a converted cell. The formula bar should show a fixed result rather than a formula beginning with an equals sign. Change one of the original input cells, if appropriate, and confirm the converted result does not recalculate. You can undo immediately with Ctrl+Z if the wrong cells were changed, as long as you have not performed other actions that make undo impractical.
- Save the workbook. Once you have checked the result, save the file. If the workbook needs to be shared or retained as a working model, consider saving a separate copy before conversion. That preserves the original formulas in case you need to update the calculations later.
For a small number of cells, you can repeat the process for each range. If the formula cells are separated by gaps, convert one continuous block at a time. Copying and pasting into a noncontiguous selection can be confusing, and it is easier to verify each block separately.
Common Mistakes
- Pressing Delete instead of Paste Values. Delete clears the formula and usually leaves the cell empty. It does not turn the formula result into a fixed value. Copy the cells first, then use the Values paste option.
- Choosing the wrong paste option. Paste, Formulas, and Values are different. A normal paste can bring formulas into a different location, while the Formulas option keeps calculations. Choose Values when the goal is to retain results without calculations.
- Replacing formulas before checking their results. A formula may currently show an error, an unexpected number, or a result based on incomplete data. Paste Values will preserve that current result, including an error. Inspect the range first and fix any calculation issues before freezing it.
- Including extra cells in the selection. A broad selection can replace formulas, labels, or other entries you meant to keep. Check the highlighted range in the Name Box or grid before pasting. When unsure, work on a smaller block and verify it.
- Expecting formulas to update afterward. Once converted, a value has no link to the cells that produced it. If the source data changes later, you must recalculate or enter a new value by another method; the former formula will not return automatically.
Tips
- Keep a formula copy when the workbook is a model. Duplicate the worksheet or save a separate workbook before converting. A second copy is safer than relying on Undo, particularly if you plan to make other edits afterward.
- Use Paste Values on the source range when appropriate. Copying and pasting values onto the same cells is the usual approach and generally leaves the destination formatting in place. If you paste into a different area, the destination formatting remains, so check number formats such as currency, percentages, and dates.
- Remember that a displayed number may be rounded. The grid may show fewer decimal places than the stored result. Pasting values retains the calculated numeric result, not merely the rounded characters you see on screen. If exact display matters, check the cell’s number format after conversion.
- Treat dates as numbers with date formatting. Excel stores dates as serial numbers and displays them using a date format. If a pasted result looks like a large number, the value may still be correct but the destination format may have changed. Apply a suitable date format through Home > Number Format.
- For a formula returning a blank-looking result, inspect it first. A formula may display nothing even though the cell contains a formula. Converting it removes the calculation, and the result may continue to look blank. Test a few representative cells before converting a large range.
Troubleshooting
The formula is still there. Check that you used the Values choice in the Paste dropdown rather than the ordinary Paste button or the Formulas option. Copy the range again, click the destination range, and choose Home > Paste > Values. Then click a cell and inspect the formula bar.
The result became blank or looks different. First check the formula bar and the cell’s number format. A date or decimal can look different if its format changed, while a formula that returned an empty string can remain visually blank. If the result is not what you intended and the action is still the latest change, press Ctrl+Z and inspect the original formula and its inputs before trying again.
You see an error such as #VALUE! or #N/A. Paste Values does not repair formula errors; it freezes the current result, which may itself be an error. Undo if possible, correct the source data or formula, verify that the calculation now returns the intended result, and then paste values again.
Excel will not let you overwrite part of the range. This can happen with a legacy array formula or a dynamic-array formula that spills results into nearby cells. Do not try to replace only one cell in a multi-cell array or spill range. Identify the entire output range first. If Excel still blocks replacement, copy the complete output to an unused area and paste values there, then decide whether to remove or replace the original formula range as a whole. Keep a backup, because array and spill behavior depends on how the workbook was built.
The sheet is protected or the cells are locked. You may be able to select cells but not edit them. Use Review > Unprotect Sheet if you have authorization and the password, or ask the workbook owner for an editable version. Do not assume that copying values bypasses worksheet protection.
A table formula appears again. Excel tables can use calculated columns that fill a consistent formula down the column. If Excel restores a formula while you are editing a table, verify the whole intended column and try converting the data rows as one range. If the behavior continues, make a copy and ask the workbook owner to review the table’s calculated-column setup before changing workbook-wide AutoCorrect options.
You are using Excel for Mac or the web. The same principle applies, but the Windows keyboard shortcuts may differ. On Mac, use Command+C to copy, then select the Values option in the Home tab’s Paste menu; menu labels and shortcuts can vary by release. In Excel for the web, use the Paste Values option from the Home tab after copying. If a shortcut opens a different command, use the ribbon menu instead.
Frequently Asked Questions
Can I remove a formula but keep its result in the same cell?
Yes. Copy the cell or range, then paste values over the same selection. The formula is replaced by its current calculated result.
Will the value change if the original input cells change?
No. A pasted value is fixed and no longer linked to the formula’s inputs. To update it, you would need to calculate or enter a replacement value yourself.
Does Paste Values keep the cell formatting?
When you paste values over the same cells, the existing formatting is generally retained. If you paste into another location, that destination’s formatting is used. Check dates, percentages, and decimals if the appearance changes.
Can I convert only some formulas in a worksheet?
Yes. Select just the cells or contiguous ranges you want to convert, then use Paste Values. Cells outside the selection are not changed. For separated ranges, converting them one at a time is easiest to verify.
Can I recover a formula after pasting values?
Use Ctrl+Z immediately if the conversion is your latest action. After saving, closing, or making further edits, Undo may not be available. If you did not keep a backup, the original formula may need to be recreated.
Does this work with formulas that return errors?
It will replace the formula with its current result, including an error value. Fix the formula or its inputs first if you want to preserve a useful result instead of the error.
Related
- Copy Just the Fill Color in Excel Without Other Formatting
- Copy Only Filtered Data in Excel: Step-by-Step
- How to Copy Into Multiple Cells in Excel Without Errors
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Delete Table but Keep Data: The Right StepsTo delete an Excel table but keep its data, convert the table to a normal range: click a…
- How to Filter in Excel and Find the Exact Rows You NeedTo filter a list in Excel, select a cell in your data, choose Data > Filter, then open…
- COUNTIF in Excel: Examples and How to Use It Step by StepTo count cells that match one condition in Excel, use =COUNTIF(A2:A11,"Apple"). With fruit names in A2:A11, this returns…
- Excel Color Filter: How to Filter Rows by Cell ColorTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- How to Delete Cells in Excel Without Losing FormulasTo remove entries without removing formulas in Excel, select only the cells containing the entries and choose Home…
- Extract the First Word in Excel With a FormulaTo extract the first word from text in Excel, put the source text in A2 and enter =LEFT(TRIM(A2),FIND("…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.