OmniDocHow to Remove Formulas and Keep Their Values in Excel
Convert Excel formula cells to static values, stop links from updating, and save a formula-free copy, without losing the numbers you need.
If you need to send a spreadsheet to someone who should not see your calculations, or you want to freeze a set of results before the source data changes, you need to remove the formulas and keep their values. People often search for this as "excel how to remove formula and keep value," and the answer is simple once you know which operation to use. This guide is for anyone working with a budget, a report, or a shared workbook who wants plain numbers in place of live calculations.
The key distinction is between converting formulas to values and clearing formatting. Clearing formatting only changes fonts, colors, and number styles; the formulas keep running. Converting replaces each formula with the result it currently shows, so the cell holds a fixed number or text. This article covers that conversion, how to stop external links from updating when you open a file, and how to save a copy that contains no formulas.
All of these steps happen in Excel. OmniDoc does not change formulas in your spreadsheet, so you do not need it for this task.
How do I remove formulas but keep the values in Excel? The short answer
Copy the formula cells, then paste them back over themselves as values only. Each cell keeps the number it shows now and loses its formula. Do this on a copy of the file so you can still reach the original.
| Your situation | What to do | Why |
|---|---|---|
| You want a fixed version to share | Save a copy, then paste values only over the formula cells | The shared file has numbers only, and your original keeps working formulas |
| The workbook pulls data from other files | Break the external links, then paste values if needed | Linked cells become static, so the file stops asking to update |
| You need the numbers in a new sheet | Copy the range and paste values only into a blank sheet | The original sheet stays unchanged for reference |
| You only want a frozen snapshot of one column | Select just that column's formula cells and paste values | Other formulas in the workbook keep calculating |
Before you start
Check these items before you change anything. Converting formulas cannot be undone once you save over the original.
- Save a copy under a new name. Once a formula becomes a value, the calculation is gone from that file. A copy lets you go back if you find a mistake.
- Confirm which cells hold formulas. Click a cell and look at the formula bar. A formula starts with an equals sign and shows the calculation, while a plain value shows only the number or text.
- Check for links to other workbooks. A formula that refers to another file will show a path to that file. Those cells need the link broken, not just a paste, if you want the file to stop updating.
- Decide whether you need to keep the calculations. If you might change an input later, keep a formula-based original and make the value copy separately.
- Note any totals or summaries. Subtotals and grand totals are usually formulas. Verify them after conversion, since they will be frozen at their current values.
Converting formulas to values in Excel, step by step
- Save a copy of the workbook. Use the save-as option and give the copy a different name, such as "budget-values." This keeps your live formulas in the original file.
- Select the cells that contain formulas. You can select a single column, a block of cells, or an entire sheet. Only the formula cells change, so mixed ranges are safe as long as you paste back to the same range.
- Copy the selection. Use the copy command for your platform. Excel places the selected cells on the clipboard, including their formulas.
- Open the paste options and choose values only. Excel offers a paste special option with a values choice. Picking it pastes the results without the formulas.
- Paste over the same cells. Put the cursor on the top-left cell of the range and paste. Each formula is replaced by the number it showed. Cell formatting such as borders and fonts stays where it was.
- Break any external links. Open the workbook's link management dialog and break each link that points to another file. Breaking a link also converts the linked cells to their current values, so the file no longer refreshes them.
- Save the converted file. Save under the copy's name. If you later need the original calculations, open the first file you saved.
Check the result
Spot-check the converted file before you send it. A few minutes here prevents a mistake from being shared.
| Check | How to see it | If it's wrong |
|---|---|---|
| Formulas are gone | Click several cells that used to hold formulas and read the formula bar | Repeat the paste-values step on the missed range |
| Numbers match the original | Compare a few totals and key cells with the original file | If a value changed, the source data may have moved; restore from the original and repeat |
| Totals are correct | Add up a column by hand or check it against the source | Recalculate in the original, then paste values again |
| No external links remain | Open the link management dialog and confirm the list is empty | Break any remaining link and save again |
| Formatting is intact | Scroll through the sheet and look at number formats, fonts, and borders | Reapply the format to the affected cells; pasting values does not remove cell styles |
If your text values or dates look different after pasting, check the number format on those cells. Dates may show as serial numbers when the format is lost, so set the date format again.
Opening a workbook without updating links
Sometimes you do not want to convert anything. You just need to open a spreadsheet that is connected to other files without it pulling in new data. When Excel shows a security message about links, choose the option that keeps the existing values rather than updating them. The workbook will show the values from the last time it was saved.
Be aware that these values may be out of date. If the source file has changed since the last save, your numbers will not reflect those changes. If you need current numbers, open the source files first and then open the dependent workbook, or convert the values and keep a dated note of when you froze them.
If you want the file to stop prompting for links permanently, break the links as described in step 6 above. That changes the file itself, so do it on a copy.
🎯 Which approach fits you?
Your goal decides which path to take. Use this to choose the right one quickly.
- You are sharing the numbers with someone else. Save a copy, convert every formula to a value, and break all links. The recipient gets only static data.
- You need to keep calculating in your own workbook. Leave the original as it is. Convert only a copy when you need a snapshot, such as a month-end figure.
- The workbook keeps asking to update links. Open the link dialog on a copy and break the links there. Doing this on the original removes the connection for good.
- You only want to hide the formulas on a few cells. Converting those cells to values does this, but any other formula in the workbook can still reveal how a total was calculated, so check the whole file.
❓ FAQ
1. Will pasting values remove my cell formatting?
Pasting values only replaces the contents of the cells. The existing formatting on those cells usually stays. If you paste into a different range, the formatting of the destination cells applies instead, so check the new location.
2. Can I undo the conversion after saving?
Not in the saved file. Once you overwrite a formula with a value and save, the formula is gone from that file. Keep the copy you made in the first step so you can always return to the live version.
3. Why does Excel keep asking me to update links?
The workbook still has formulas that refer to another file. Breaking those links on a copy stops the prompt. The message will keep appearing until the link is removed or the source file is found at the same path.
4. Does this work on a whole sheet at once?
Yes. Select the entire sheet, copy it, and paste values only over the same range. Be careful with sheets that have other content you want to keep, such as charts or notes, because a full-sheet paste can change them.
📝 Summary
To remove formulas and keep their values in Excel, save a copy, paste values only over the formula cells, and break any external links. Check totals and links before you share or save the final file, and keep the original workbook so you can return to the live calculations.
