To remove formulas in Excel but keep the data they currently display, select the formula cells, copy them, then paste values over the same cells. In Excel for Microsoft 365 on Windows, use Home > Paste > Paste Special > Values. This replaces each formula with its current result without clearing the cells. Save a copy first if you may need the formulas later, because replacing them this way is not undone by simply closing the workbook without saving.
What You’ll Need
- The Excel workbook containing the formulas, open in the desktop version of Microsoft Excel.
- A clear idea of which cells to convert. If the workbook is important, make a copy using File > Save As before changing it.
- Permission to edit the worksheet. If it is protected, you may need the sheet password or the workbook owner’s help.
Step-by-Step
- Identify the formulas you want to replace. Click a cell and look at the formula bar above the worksheet. If it begins with an equals sign, the cell contains a formula. Check a few cells in the target area, especially if it contains a mixture of formulas and manually entered values. Decide whether to convert one cell, a contiguous range, or several separate ranges. Avoid selecting an entire row or column unless you truly intend to affect all of its cells.
- Save a backup if the formulas might be useful later. Choose File > Save As, give the copy a different name, and keep the original workbook. This is especially useful when formulas refer to other sheets, contain business rules, or would take time to rebuild. You can also copy the target range to an unused area or a separate sheet as a temporary record, but a separate workbook copy is safer if the worksheet has complicated links or layout.
- Select the target cells. Click and drag across a range, or click the first cell, hold Shift, and click the last cell. To select non-adjacent ranges in Windows, hold Ctrl while selecting each range. On Mac, use Command to select non-adjacent areas. For a whole column of formulas, select only the relevant cells beneath the header rather than the column heading, unless you also want to affect other content in that column.
- Copy the selection. Press Ctrl+C on Windows or Command+C on Mac. A moving dashed outline usually appears around the copied cells. Do not press Delete or Backspace: those keys clear cell contents rather than replacing formulas with their displayed results.
- Open Paste Special and choose values. On Windows, go to Home > Paste, open the Paste dropdown, and select Values or Paste Special > Values, depending on the ribbon layout. You can also press Ctrl+Alt+V to open the Paste Special dialog, choose Values, and select OK. In the dialog, the Values option is commonly represented by a clipboard or number icon. On Mac, use the Home tab’s Paste dropdown and choose the values-only option; the exact icon or label can differ by Excel release. In Excel for the web, use the Home tab’s Paste dropdown and select Paste Values if available.
- Paste over the original cells. When you use the ribbon, Excel normally pastes onto the selected range. If the selection has moved, click the top-left cell of the intended destination first. Pasting values over the same cells replaces formulas with the results currently shown, while leaving the cells’ existing formatting in place. Do not choose regular Paste if you want to remove formulas; regular Paste copies the formulas too.
- Verify the change before moving on. Click several converted cells and inspect the formula bar. A former formula such as a calculation should now show its result rather than an expression beginning with an equals sign. Check that totals, dates, text, and number formats still look right. If the formula returned an error, such as #N/A or #DIV/0!, the pasted value will generally be that error, not a repaired answer.
- Finish the operation and save. Press Esc to clear the moving copy outline, then save the workbook with Ctrl+S on Windows or Command+S on Mac. If you converted only part of a range, inspect nearby cells to ensure the correct formulas remain and that no unintended cells were changed. Keep the backup until you are sure the values are correct.
For one cell: select and copy it, then use Paste Special with Values while that cell remains the destination. For a large range, use the same procedure once for the entire intended selection. Excel replaces the formulas with their calculated results; it does not preserve the formula logic in the converted cells.
Common Mistakes
Pressing Delete instead of pasting values: Delete removes the cell contents, including both a formula and its displayed result. It does not freeze the result. If you have not saved since the mistake, press Ctrl+Z on Windows or Command+Z on Mac immediately. Then copy the cells and use Paste Values.
Using ordinary Paste: A normal paste usually reproduces the formulas, so the destination may still change when source cells change. Choose the values-only command, not the standard clipboard paste icon. Afterward, check the formula bar; the displayed number alone does not tell you whether the cell still contains a formula.
Selecting too much or too little: If you include labels, inputs, or neighboring formulas by mistake, values-only paste can replace formulas you meant to keep. Conversely, converting only a few cells in a calculated column may leave the rest of the column dynamic. Confirm the selection outline before pasting and verify both the converted area and its boundaries afterward.
Expecting the result to keep updating: A pasted value is a snapshot of the formula’s current result. If the inputs change later, the converted cell will not recalculate. Keep the original formula in a backup or leave it in place if ongoing updates are required.
Tips
- Check calculation timing first. If Excel is set to manual calculation, a formula’s displayed result may be out of date. On the Formulas tab, check Calculation Options. If appropriate, choose Automatic or recalculate with F9 before converting. In a large workbook, recalculate only after considering whether that will update other formulas too.
- Use Paste Values when formatting matters. Values-only paste is designed to replace cell contents without copying the source’s formatting. This is useful for retaining number formats, fill colors, borders, and font styling in the destination. Still inspect special formatting such as conditional formatting, which may continue to change the appearance based on other cell values.
- Convert only after checking dependencies. Other formulas may refer to the cells you are freezing. Those dependent formulas can continue to calculate using the fixed values, but the results will no longer respond to the inputs that previously changed the converted formulas. Review important totals, charts, and reports after the change.
- For a separate copy, use a new destination. If you want to preserve the formula version on the original sheet, copy the range to another sheet and paste values there. Use Home > Paste > Values after selecting the new destination. Give the copied sheet a clear name so readers know it contains fixed results.
- Remember that dates and percentages are stored as values too. A date formula’s displayed date becomes a date serial value with date formatting, and a percentage formula’s result becomes a number displayed with percentage formatting. If the appearance changes, check the cell’s number format rather than assuming the conversion failed.
Troubleshooting
The cell still shows a formula in the formula bar. The paste may have been ordinary Paste, or the copied selection may not have been the destination. Undo if needed, select the correct cells again, copy, and explicitly choose Values. In a Paste Special dialog, confirm that the Values option is selected before accepting.
The pasted result is an error or looks blank. Paste Values preserves what the formula currently returns; it does not resolve errors or restore missing source data. Inspect the original formula and its referenced cells before conversion if you need a usable result. A formula that displays an empty string can become a blank-looking cell, so test a small sample first if distinguishing an empty result from an actually empty cell matters to your workflow.
The values are out of date. Recalculate the workbook before copying. Check Formulas > Calculation Options and, if suitable, set calculation to Automatic. Press F9 on Windows to recalculate. On Mac, use the Formulas tab’s calculation controls or the recalculation shortcut shown for your Excel version. Once pasted as values, the cells will not update when the workbook recalculates.
Excel will not let you change the cells. A protected worksheet can block pasting. Check Review > Unprotect Sheet; if Excel requests a password, ask the workbook owner rather than trying to bypass protection. If the workbook is shared or opened read-only, save an editable copy or request edit access.
A table column seems to refill formulas. Excel tables can use calculated columns that propagate formulas through a column. If a table immediately restores formulas after you paste values, first make a backup and check that the entire intended data portion of the column is selected. If you need fixed values and the behavior persists, you can use Table Design > Convert to Range, then repeat the values-only paste. Converting a table to a range removes table features such as structured references and automatic filtering behavior, so do this only if that tradeoff is acceptable.
Formatting changed unexpectedly. Make sure you chose Values rather than a broader Paste Special option such as All. If only the number display is wrong, select the affected cells and use Home > Number Format to choose the appropriate format, such as Date, Percentage, or Number. Avoid reapplying formatting blindly if the range has custom styles.
Frequently Asked Questions
Does Paste Values keep the formula’s displayed result?
Yes. It replaces the formula with the result available at the time of pasting. The result may be a number, date, text, blank-looking output, or an error value.
Will the converted data update if I change the inputs?
No. A pasted value is fixed and no longer recalculates from the former formula. Keep a copy of the formulas if you may need updated results later.
Can I remove formulas from a whole worksheet at once?
You can select a broad range and paste values, but doing so may also affect cells you did not intend to change. For most worksheets, identify and convert the relevant range or ranges, then verify the result. Make a separate copy before converting an entire sheet.
Does Paste Values remove cell formatting?
Normally, no. Values-only paste changes the cell contents while retaining the destination’s formatting. The result’s appearance can still be affected by existing number formats or conditional formatting.
Can I undo the change?
Usually, press Ctrl+Z on Windows or Command+Z on Mac immediately after pasting. Undo history may not be available after closing the workbook or performing other actions, so a saved backup is the reliable way to retain the original formulas.
Can I do this in Excel for the web or on Mac?
Yes. Excel for the web and Excel for Mac have values-only paste options, though their ribbon labels and shortcut behavior can differ from Windows. Use the Home tab’s Paste dropdown and choose the explicit Values option rather than relying on a Windows-only shortcut.
Related
- How to Paste in Excel Without Changing Cell Format
- Paste in Excel With Filtered Rows: Safe Methods
- How to Paste in Excel While Skipping Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find a Percentage in Excel: Formulas and ExamplesTo find what percentage one value is of another, divide the part by the whole and format the…
- Excel Formula for Hours Worked: Calculate Time and PayFor a shift that starts in A2 and ends in B2, enter =MOD(B2-A2,1)*24 to return hours as a…
- VLOOKUP in Excel: Step-by-Step GuideTo use VLOOKUP in Excel, put the value you want to find in the first column of a…
- COUNTIF for X in a Cell: Exact or Partial MatchTo count cells whose entire value is x, use =COUNTIF(A2:A8,"x"); to count cells that contain x anywhere, use…
- Excel INDEX Function Examples: Useful Formulas and UsesExcel’s INDEX function returns a value—or a reference to a value—from a range based on its row and…
- Excel Formula to Check Yes or No in Another ListYes. To show whether a value in one cell appears anywhere in another list, use COUNTIF inside IF.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.