How to Remove Formulas in Excel but Keep Data

In Excel, formulas are powerful tools that make calculations and reporting easier. But there are many cases where you want to keep the calculation results of a formula while discarding the formula itself—for example, when sharing reports, archiving static data, or preventing accidental changes. If you simply delete the formula, the calculated value disappears as well, which can lead to data loss and errors.

This tutorial provides a step-by-step guide on how to remove formulas from Excel cells while keeping the calculated data intact. We’ll cover manual methods in Excel, useful keyboard shortcuts, and also show you how to automate the process with Python. In addition, we highlight common pitfalls and best practices to ensure your data stays reliable.

Main Methods to Remove Formulas in Excel

• Copy and Paste as Values
• Remove Formulas Using Keyboard Shortcuts
• Automating Formula Removal with Python

Copy Cells and Paste as Values in Excel

The simplest and most widely used way to remove formulas in Excel while keeping the results is through Copy → Paste Special → Values. This approach is especially suitable for quick edits in small tables or single worksheets.

Steps:

1. Select the cells containing formulas.
2. Copy the cells by right-clicking and selecting Copy.
3. Right-click the selection → Paste Special → Values → OK.

The image below shows the paste special options menu in Excel which allows you to choose to paste values instead of formulas.

In fact, Paste Special offers three different value-related options, and in this scenario, any of them can be used. Below is an example screenshot of the result:

Tips:

• This method replaces formulas with their calculated values but keeps formatting intact.
• Ideal for small data ranges or single sheets.
• If the formula originally referenced external sources, the pasted value becomes static and won’t update.

Remove Formulas Using Excel Keyboard Shortcuts

While Paste Special → Values is a useful way to remove formulas while keeping values, repeatedly using the mouse can be tedious. For users who prefer keyboard navigation, Excel offers keyboard shortcuts that achieve the same result more quickly.

Steps:

1. Select the target cells (to select all cells, use Ctrl + A).
2. Press Ctrl + C to copy.
3. Use Ctrl + Alt + V, then press V, followed by Enter.

This shortcut essentially performs the same action as Copy → Paste Special → Values, but in a faster, keyboard-driven way.

Advantages:

• Faster workflow, especially for frequent tasks
• Supported in most Excel versions (2010–365)

Limitations:

• Not efficient for very large datasets or across multiple files
• Still requires manual effort

Recommended Reading: If you’re also interested in removing data validation rules while keeping the values intact, check out our guide on how to remove data validation in Excel but keep data.

Common Mistakes When Removing Formulas (and Best Practices)

Removing formulas may seem simple, but there are risks. Keep the following in mind:

• Avoid deleting formulas directly—this clears both the formula and its result.
• Once a file is saved, undo cannot restore formulas.
• Removing formulas that depend on external links will freeze values permanently.
• Some formulas may be hidden through protection or formatting, making them easy to overlook.

Best practices: always work on a copy of your file, double-check values after the change, and keep a backup for critical business sheets.

For repetitive tasks or large datasets, manual methods can become inefficient. That’s where automation comes in.

Automating Excel Formula Removal with Python

Manual methods are sufficient for small tasks, but what if you need to process hundreds of cells, apply the same operation across multiple files, or process worksheets without Excel? This is where automation comes in. With Python, you can write a script to handle formula removal consistently and efficiently.

One practical choice for Python automation is to use Spire.XLS for Python, which provides built-in support for checking whether a cell contains a formula and retrieving its calculated value. This makes the process far simpler compared to parsing formulas manually, especially when dealing with complex formulas or large datasets.

Python Library Installation:

pip install spirexls

Example: Remove formulas in a worksheet using Python

The following example loads an Excel file, checks every cell in the first worksheet, and replaces formulas with their evaluated results while leaving all other cells untouched:

from spire.xls import Workbook

# Load the Excel file
wb = Workbook()
wb.LoadFromFile("Sample.xlsx")
sheet = wb.Worksheets.get_Item(0)

# Replace formulas with their calculated values
for row in range(sheet.Rows.Count):
    for col in range(sheet.Columns.Count):
        cell = sheet.Range.get_Item(row + 1, col + 1)
        if cell.HasFormula:
            cell.Value = cell.FormulaValue

# Save the updated file
wb.SaveToFile("output/remove_formulas.xlsx")

Notes:

• The example demonstrates processing one worksheet. You can extend the logic to loop through all worksheets if needed.
• The API offers properties like CellRange.HasFormula and CellRange.FormulaValue, making it easy to safely convert formulas into static values.
• Always test the script on a backup copy to avoid overwriting important data.

This image shows the original Excel worksheet and the updated worksheet after running the Python script:

By using Python automation, you can handle bulk operations efficiently and integrate formula removal into larger data-processing workflows.

If you want to explore more tips on automating Excel file processing with Python, check out the Spire.XLS for Python official tutorial page.

How Different Formula Types Behave When Removed

It’s important to know how different formulas behave when converted to values:

• Simple formulas (e.g., =SUM(A1:A10)) → Converted into their numeric results.
• Lookup formulas (e.g., =VLOOKUP(...)) → The current lookup value is retained, but won’t update if the source changes.
• Dynamic array formulas (e.g., =SORT(A1:A10) in Excel 365) → Converted into static arrays.
• Date and financial formulas → The displayed result stays, but ensure formatting is preserved.

This knowledge helps prevent unexpected errors when cleaning up spreadsheets.

When working with complex formulas, you may also need to automate tasks like reading or writing formulas. See the Python tutorial on adding and reading Excel formulas for more details.

FAQs on Removing Formulas in Excel

Q: Can I remove formulas but keep cell formatting?

A: Yes. Paste Special → Values preserves formatting. In Python automation, copying styles may require extra steps.

Q: Can I undo removing formulas?

A: Only if the file hasn’t been saved yet. That’s why backups are essential.

Q: Will removing formulas affect dependent cells?

A: Yes. Any cells relying on the formula results will no longer update dynamically.

Q: Can I process multiple worksheets at once?

A: Yes. With Python automation, you can easily extend the script to loop through all worksheets in a workbook.

Conclusion

In summary, knowing how to remove formulas in Excel but keep data is essential for both casual users and professionals. Manual methods like Copy-Paste and keyboard shortcuts are perfect for small datasets or occasional tasks. For repetitive or large-scale operations, Python automation with libraries such as Spire.XLS for Python provides an efficient and reliable solution.

By understanding the implications of different formula types, planning ahead, and following best practices, you can ensure that your spreadsheets remain accurate, consistent, and easy to share—without the risks of accidental formula changes.

See Also

If you’d like to learn more about working with Excel formulas and protecting data, check out these related tutorials.

• Shrink Text or Cells to Fit Cell Content
• How to Save Excel Charts as Images
• Split Excel Sheets into Multiple Files
• How to Write Excel Files with Python